Copy range until last occupied cell [VBA]

[Martin_H]

Hi,

I used this macro to copy data from each sheet in the active workbook, from the range A14:BB258 to the MASTER sheet, to the first available row.

As you can see I am using a fixed range (A14:BB258).

I would like to modify this macro to copy exactly same range (A14:BB258), but with little change.
The macro will work until the last occupied cell in the column G.
Therefore, it does not always have to go to the bottom of this range (A14: BB258).

Sub SUMMARIZE_DATA()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "MASTER" Then
ws.Range("A14:BB258").Copy
Sheets("MASTER").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteAll
End If
Next
Application.CutCopyMode = False
End Sub

Thank you for your help.

 

[Sequoyah]

Hi Martin_H
just add a variable to find the lastrow in column G

Sub SUMMARIZE_DATA()

Dim ws As Worksheet
Dim LastRow As Long

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "MASTER" Then
LastRow = ws.Range("G" & Rows.Count).End(xlUp).Row
ws.Range("A14:BB" & LastRow).Copy
Sheets("MASTER").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteAll
End If
Next
Application.CutCopyMode = False
End Sub

 

[Martin_H]

Awesome Sequoyah

I appreciate it very much. Thank you.