copying a variable range from a closed workbook-CodePudding

As the title says, I'm trying to copy a variable range from a closed workbook. I'm getting a "Object doesn't support this property or method" error. Here is my code:

Sub PadslocReadData()

    Application.ScreenUpdating = False
    
    Dim src As Workbook
    Dim LastSrcRow As Integer
    Dim curWorkbook As Workbook
    
    Set curWorkbook = ThisWorkbook
    Set src = Workbooks.Open("\\filename.xls", False, False)
    LastSrcRow = src.Cells(Rows.count, "A").End(xlUp).row
    curWorkbook.Worksheets("sls").Range("A1:G" & LastSrcRow).Formula = 
    src.Worksheets("Sheet1").Range("A1:G" & LastSrcRow).Formula

    src1.Close False

End Sub

Any idea where I'm going wrong? Thanks

Edit: per the comments I changed a line of code but now i'm getting a new error "Object Required" (424) error.

Sub PadslocReadData()

Application.ScreenUpdating = False

Dim source As Workbook
Dim LastSrcRow As Long
Dim curWorkbook As Workbook
    
Set curWorkbook = ThisWorkbook
Set source = Workbooks.Open("\\filename.xls", False, False)
LastSrcRow = source.Sheets("Sheet1").Cells(Rows.count, 
"A").End(xlUp).row
curWorkbook.Worksheets("sls").Range("A1:G" & LastSrcRow).Formula = 
source.Worksheets("Sheet1").Range("A1:G" & LastSrcRow).Formula

src1.Close False

End Sub

CodePudding user response：

Could you try this code. Change the file path and sheet name as per your requirement

Sub PadslocReadData()

Dim wbtarget, wbsource As Workbook
Dim wstarget, wssource   As Worksheet
Dim lastrow As Long



Set wbtarget = ThisWorkbook
Set wstarget = wbtarget.Worksheets("Sheet1")

wstarget.Cells.Clear

'Filepath
Filepath = "C:\test.xlsx"

Set wbsource = Workbooks.Open(Filepath, UpdateLinks:=0)
Set wssource = wbsource.Worksheets("sheet1")

lastrow = wssource.Cells(wssource.Rows.Count, "A").End(xlUp).Row

wstarget.Range("A1:G" & lastrow).Formula = wssource.Range("A1:G" & lastrow).Formula

wbsource.Close savechanges:=False

End Sub

CodePudding user response：

Alternatively and very valuable to learn in vba is to use array's. The idea is to minimize the number of interactions with the sheet. So you basically, load in memory (an array), do whatever you want to do, dumb the data back to the sheet at the end:

    Option Explicit 'always add this, it will triger an error if you forgot to dim a var
    Sub PadslocReadData2()
        Dim srcWb As Workbook, FileName As String 'declare your vars
        FileName = "\12.xlsx" 'make path dynamic, just update filename
        Set srcWb = Workbooks.Open(FileName:=Application.ActiveWorkbook.Path & FileName)
        
        Dim arr, LastSrcRow As Long
        With srcWb.Sheets("Sheet1") 'use with to avoid retyping
            LastSrcRow = .Cells(Rows.Count, "A").End(xlUp).Row 'if your cells are adjacent you can use .currentregion to avoid this step
            arr = .Range(.Cells(1, 1), .Cells(LastSrcRow, 7)).Formula 'Add source to array
        End With
        With ThisWorkbook.Sheets("sls")
            .Range(.Cells(1, 1), .Cells(UBound(arr), UBound(arr, 2))).Formula = arr 'dump to target sheet
        End With
    End Sub