VBA: Copying contents one sheet to another's file sheet

[kess]

Good day to all!

i am using Excel 2007, Windows XP.

trying to write a macros, that determines the range with data in file1 sheet1, copies it and adds it to determined position in sheet1 in file2.

i end up receiving run-time error 1004 on the line i marked with red. any help will be greatly appreciated, as im clueless after trying to make it work trough copy/destination and value methods. below is the latest version of my disaster

Thank you.

Sub COPYCELL()

Dim wbk As Workbook
Dim bgn As String
Dim rght As String
Dim wbk2 As Workbook
    
strFirstFile = "C:\playground\files playground\file1.xlsx"
strSecondFile = "C:\playground\files playground\file2.xlsx"
    
Set wbk = Workbooks.Open(strFirstFile)
wbk.Sheets("Sheet1").Range("A65000").End(xlUp).Select
bgn = ActiveCell.Row
wbk.Sheets("Sheet1").Range("IV1").End(xlToLeft).Select
rght = ActiveCell.Column

[COLOR=Red]Workbooks(strFirstFile).Sheets("Sheet1").Range(Cells(1, 1), Cells(bgn, rght)).Copy  [/COLOR] 

Set wbk2 = Workbooks.Open(strSecondFile)

With wbk2.Sheets("Sheet1")
    .Range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End With

End Sub

 

[Jonmo1]

When using the syntax

Range(Cells(w,x), Cells(y,z))

And you specify the book/sheet on the Range, you MUST also specify the SAME book/sheet on each of the Cells.

So it would be

Workbooks(..).Sheets(..).Range(Workbooks(..).Sheets(..).Cells(w,x), Workbooks(..).Sheets(..).Cells(y,z))

And you can simplify it using With

Try

With Workbooks(strFirstFile).Sheets("Sheet1")
    .Range(.Cells(1,1), .Cells(bgn, rght)).Copy
End With

Notice the periods preceeding the Range AND Cells.
Those indicate a reference to the object (book/sheet) referenced in the With Statement.


Hope that helps.

 

[kess]

Thank you for responding.

Fixed the syntax, now get run-time error 9: subscript out of range

 

[Jonmo1]

Fortunately, that error is pretty straight forward.
It means the object referenced does not exist

so either the workbook doesn't exist
Workbooks(strFirstFile)

OR, Sheets("Sheet1") does not exist within that workbook.


For troubleshooting purposes, to see if it's the book or the sheet..
Replace Sheets("Sheet1") with Sheets(1)

With Workbooks(strFirstFile).Sheets(1)
    .Range(.Cells(1,1), .Cells(bgn, rght)).Copy
End With

If it still errors, then it's the book.
Check the value of the variable strFirstFile at the time of the error.

If it doesn't error, then it's the sheet, check for exact spelling and extra spaces

 

[kess]

It works now!

Thanks a lot for your help