select data

[klatlap]

I need a macro that would copy cells BA2:QG? now i don't know what the last row will be as it changes.

I have used this code before but the empty cells are copied as well.

Sub Copy()
Sheets("Data").Select
    Range("BA2:QG25").Select
    Selection.Copy
    Sheets("Output").Range("A" & Sheets("Data").Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=True, Transpose:=False

 

[Michael M]

Maybe try

Sub Copy()
dim lr as Long
lr = cells(rows.count."QG").end(xlup).row
Sheets("Data").Range("BA2:QG" & lr).Copy
    Sheets("Output").Range("A" & Sheets("Data").Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=True, Transpose:=False

 

[klatlap]

Must be error in code as soon as i paste it i get an error on the 3rd line

 

[Michael M]

ooops....that's what happens when you type in the dark

Sub Copy()
dim lr as Long
lr = cells(rows.count,"QG").end(xlup).row
Sheets("Data").Range("BA2:QG" & lr).Copy
    Sheets("Output").Range("A" & Sheets("Data").Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=True, Transpose:=False

 

[Peter_SSs]

Since multiple sheets are involved I would strongly recommend adding

lr = Sheets("Data").Cells(Rows.Count, "QG").End(xlUp).Row

Also, should work OK, but seems strange to use "Data" to count the rows in the last line of code when you are working on "Output". (No sheet was specified when counting the rows to determine lr)