CODEWITHSUNDEEP
vbaexcelcsv
DigitalSea
DigitalSea

Reputation: 191

copy and paste from csv file to excel file

I modified a piece of code that opens a .csv file, copies a selected range and pastes it in a selected cell in an excel worksheet.

I am having trouble selecting the next range in the .csv file to paste in the excel file. It only works for the first range (E2:E25).

I want it to select the next range (B2:B25) from the .csv file, copy/paste, but it only selects from the excel file. How do I fix that? Thanks.

Option Explicit                               

Sub copy2()
Dim FilesToOpen
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim newSheet As Worksheet
FilesToOpen = Application.GetOpenFilename(Title:="Text Files to Open")
    Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen, Format:=4)
    wkbTemp.Sheets(1).Cells.copy
    Range("E2:E25").Select
    Selection.copy
    Windows("Petty Cash Form (test).xls").Activate
    Range("H10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


    Range("B2:B25").Select
    Selection.copy
    Windows("Petty Cash Form (test).xls").Activate
    Range("B10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Application.CutCopyMode = False
    wkbTemp.Close

End Sub

Upvotes: 0

Views: 16609

Answers (2)

dom176
dom176

Reputation: 187

This is somewhat of a rudimentary answer but I would suggest following the VBA hierarchy in order to select specific ranges. 

workbook.worksheet.range

I think if you defined the worksheets then used

wrktemp.Sheets("sheet name").Range("E2:E25")

I believe this will help in pursuit of your problem

Upvotes: 0

Alexander Bell
Alexander Bell

Reputation: 7918

In your VBA macro add the line: wkbTemp.Sheets(1).Activate (see 'Activate Source Worksheet):

Option Explicit

Sub copy2()
Dim FilesToOpen
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim newSheet As Worksheet
FilesToOpen = Application.GetOpenFilename(Title:="Text Files to Open")
    Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen, Format:=4)
    wkbTemp.Sheets(1).Cells.copy
    Range("E2:E25").Select
    Selection.copy
    Windows("Petty Cash Form (test).xls").Activate
    Range("H10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

  'Activate Source Worksheet
  wkbTemp.Sheets(1).Activate    

    Range("B2:B25").Select
    Selection.copy
    Windows("Petty Cash Form (test).xls").Activate
    Range("B10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Application.CutCopyMode = False
    wkbTemp.Close

End Sub

Hope this may help.

Upvotes: 1

Related Questions