Move rows to last row

Hi, im looking for a sub to move data from Sheet1 to "Output"

I want to grab all rows (count can change) from A:I and paste them in A:I on "Output", i need them to be pasted to the next available row in ColA (In this case Row80)

Thanks
Seamus
test.zip
Seamus2626Asked:
Who is Participating?
 
StephenJRCommented:
This then?
Sub x()

ThisWorkbook.Sheets("Sheet1").Range("A1").CurrentRegion.Copy _
        Workbooks("Treats file for Merit.xls").Sheets("Output").Range("A" & Rows.Count).End(xlUp)(2)
        
End Sub

Open in new window

0
 
StephenJRCommented:
Seamus - like this?
Sub x()

Sheets("Sheet1").Range("A1").CurrentRegion.Copy _
        Sheets("Output").Range("A" & Rows.Count).End(xlUp)(2)
        
End Sub

Open in new window

0
 
Seamus2626Author Commented:
Sorry i never mentioned it was moving Spreadsheet too, can you adjust that so it moves to a spreadsheet called

The sheet name is the same "Output"

("Treats file for Merit.xls")

Thanks
Seamus
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jppintoCommented:
I've attached a working solution for what you want to do. Please click on the "Copy Data" button to see it working.

jppinto
Sub CopyData()

Dim wsOriginal As Worksheet
Dim wsOutuput As Worksheet
Dim lstRowOriginal As Long
Dim lstRowOutput As Long

With Application
    .DisplayAlerts = False
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set wsOriginal = Sheets("Sheet1")
Set wsOutput = Sheets("Output")

lstRowOriginal = wsOriginal.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
lstRowOutput = wsOutput.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

wsOriginal.Range("A1:I" & lstRowOriginal).Select
Selection.Copy
wsOutput.Range("A" & lstRowOutput).PasteSpecial
Application.CutCopyMode = False

With Application
    .DisplayAlerts = True
    .EnableEvents = True
    .ScreenUpdating = True
End With

End Sub

Open in new window

test.xls
0
 
Seamus2626Author Commented:
hey Stephen my workbook is called test.xls and im getting

"subscript out of range"

ThsWorkbook.Sheets("Sheet1") will do any spreadsheet i presume?

Thanks
Seamus
0
 
StephenJRCommented:
Do you have a sheet called "Sheet1" in the source file (the one with the code)? If not, you need to change the name.
0
 
Seamus2626Author Commented:
Perfect, my mistake Stephen,

Thank you too Jppinto

Cheers,
Seamus
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.