Q regarding copy range within code

[auto.pilot]

I have the following, which simply copies the range B6:Q100 from several files in a folder, then pastes data into the next row in my active workbook. Instead of B6:Q100, I would like to copy the entire populated range from each Sheet1 in each workbook in the Source Folder. Not sure if it matters, but the new copy range is much larger, typically A1:BP9000, but could be wider or taller depending on the data set.

Thanks in advance for any and all replies.

jim

Option Explicit
Const SOURCE_FOLDER = "\\myserver\mycompany\Templates\TemplateOne"

Sub AggregateDataFromFiles()
    Dim fs As Object
    Dim objFolder As Object
    Dim objFolderName As String
    objFolderName = SOURCE_FOLDER
    Dim filePath As String
    Dim objFile As Object
 
    Dim targetWb As Workbook
    Dim lastrow As Long
    lastrow = [A65536].End(3).Row + 5
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set objFolder = fs.GetFolder(objFolderName)
    For Each objFile In objFolder.Files
        filePath = objFolderName & "\" & objFile.Name
        Set targetWb = GetObject(filePath)
        targetWb.Worksheets(1).Range("B6:Q100").Copy Destination:=ActiveSheet.Range("A" & lastrow)
        lastrow = [A65536].End(3).Row + 1
        targetWb.Close (False)
    Next
End Sub

 

[Andrew Poulsom]

Have you tried using the Worksheets's UsedRange property?

 

[VoG]

Try

targetWb.Worksheets(1).UsedRange.Copy Destination:=ActiveSheet.Range("A" & lastrow)

 

[auto.pilot]

Excellent - and much faster than simply picking a larger-than-necessary range.

Thank you both.

jim