Macro - Next blank row

N

NPell

I have A summary Sheet, and a few tabs, and i want a macro to collate
all this information.
So far i have;

--------------------------
Dim rng As Range

Set rng = Sheets("Summary
Sheet").Range("A65536").End(xlUp).Offset(1, 0)

Sheets("SME Quarterly").Select
Rows("15:43").Select
Selection.Copy Destination:=rng
Application.CutCopyMode = False

Sheets("Sheet1").Select
Rows("15:43").Select
Selection.Copy Destination:=rng
Application.CutCopyMode = False

Sheets("Sheet2").Select
Rows("15:43").Select
Selection.Copy Destination:=rng
Application.CutCopyMode = False

Sheets("Sheet3").Select
Rows("15:43").Select
Selection.Copy Destination:=rng
Application.CutCopyMode = False
End Sub
 
M

Mike H

Maybe this way.

Note sheets aren't selected, for this operation there' no need

Sub copy()
Sheets("SME Quarterly").Rows("15:43").copy
lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial
Application.CutCopyMode = False

Sheets("Sheet1").Rows("15:43").copy
lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial
Application.CutCopyMode = False

Sheets("Sheet2").Rows("15:43").copy
lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial
Application.CutCopyMode = False

Sheets("Sheet3").Rows("15:43").copy
lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial
Application.CutCopyMode = False
End Sub

Mike
 
N

NPell

Maybe this way.

Note sheets aren't selected, for this operation there' no need

Sub copy()
    Sheets("SME Quarterly").Rows("15:43").copy
    lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row
    Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial
    Application.CutCopyMode = False

    Sheets("Sheet1").Rows("15:43").copy
    lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row
    Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial
    Application.CutCopyMode = False

    Sheets("Sheet2").Rows("15:43").copy
    lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row
    Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial
    Application.CutCopyMode = False

    Sheets("Sheet3").Rows("15:43").copy
    lastrow = Sheets("Summary Sheet").Cells(Rows.Count, "A").End(xlUp).Row
    Sheets("Summary Sheet").Range("A" & lastrow + 1).PasteSpecial
    Application.CutCopyMode = False
End Sub

Mike











- Show quoted text -

Thanks Mike! It works!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top