Range Issue

Hi everyone,

I was wondering if someone could help me.

I have 3 workbooks, I have a sheet that will have a button on it that will run the code, when the button is pushed, data from another workbook will then copy to a 3rd workbook.

Control.xls - Has the button, which will run the vba code.
Source.xls - Has the data to be copied.
Dest.xls - Were the data will be copied too.

This is the code I have so far:

strFirstFile = "C:\Users\Jay Webster\Desktop\Source.xls"
strSecondFile = "C:\Users\Jay Webster\Desktop\Dest.xls"

Workbooks.Open strFirstFile
LastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

If LastColumn > 26 Then
    LastColumn = Chr(Int((LastColumn - 1) / 26) + 64) & Chr(((LastColumn - 1) Mod 26) + 65)
Else
    LastColumn = Chr(LastColumn + 64)
End If

Range("A2", LastColumn & LastRow).Select      THIS IS THE PART THAT FAILS
Selection.Copy

Workbooks.Open strSecondFile
Sheets("Sheet1").Select
ActiveSheet.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

I get the error "Select method of Range class failed". Can someone tell me where I am going wrong?

Thanks

Any questions just ask.

Hard to tell without knowing the values of LastRow and LastColumn.

Try using the Cells method rather than converting column to a character.

Range("A2", Cells(LastRow, LastColumn)).Copy

Also no need to select.

Use objects rather than rely on the active ones.

    Dim wbkSource As Workbook
    Dim wbkDest As Workbook
    Dim shtSource As Worksheet
    Dim shtDest As Worksheet
    
    strFirstFile = "C:\Users\Jay Webster\Desktop\Source.xls"
    strSecondFile = "C:\Users\Jay Webster\Desktop\Dest.xls"
    
    Set wbkSource = Workbooks.Open(strFirstFile)
    Set shtSource = wbkSource.Worksheets("Sheet1")
    
    Set wbkDest = Workbooks.Open(strSecondFile)
    Set shtDest = wbkSource.Worksheets("Sheet1")
    
    With shtSource
        LastColumn = .UsedRange.Column - 1 + .UsedRange.Columns.Count
        LastRow = .UsedRange.Row - 1 + .UsedRange.Rows.Count
        .Range("A2", .Cells(LastRow, LastColumn)).Copy
    End With
    
    shtDest.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

I couldn't check you actual code as I did not have example data files.
I therefore assumed the code you posted was at least compiling if not running as expected.

The problem is the Pastespecial arguments you posted are for a Range object. Where as they code suggested you where using a worksheet object.

You need to make this change

    shtDest.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False