VBA - Define Range of Cells to Copy

[muzikman69]

I have a macro which will consolidate a series of Excel files. However the macro has the range to consolidate hard-coded (eg. A1:Z500). Does anyone know how to get the code to just select the cell range for all cells with content?



Current Code:

With mybook.Worksheets(1)
Set sourceRange = .Range("A1:Z500")
End With



Desired Code:

With mybook.Worksheets(1)
Set sourceRange = .Range(______________)
End With


With the range being all cells in the worksheet with content. I have some code below which will select all the content on the sheet, I have just not been able to integrate that into the portion of the code above. Thoughts?


Range("A1").Select
Range(Selection, Selection.SpecialCells(xlLastCell)).Select

 

[VoG]

Perhaps

With mybook.Worksheets(1)
    Set SourceRange = .UsedRange
End With

 

[muzikman69]

That is perfect, thank you so much.

Cheers,
Graeme,