Routine to Compile data in one sheet

[speakers_86]

I've got many many sheets that have data. I am trying to get all the data on one page. Can anyone tell me how to move to a given sheet, move to a new row, and paste? I'll perform the copy myself, as the starting location can vary, but automating the paste would be awesome. I'm skilled with vba, but I've never messed with Excel before, just Access.

Thanks.

 

[Brianwarnock]

I'm puzzled , if you are going to do the copying manually what is the difficulty in doing the paste?

If you make the compiled sheet the first it will always be easy to jump to it, and finding the end is not difficult but if you start with the last sheet and come forward that might be easier as you insert everything into a1.

Brian

 

[speakers_86]

Just because there are so many sheets. When you record a macro, you can assign that macro a hotkey. What I pictured was copying the rows, and using the hotkey to paste it to the end of the compilation sheet. There's probably an easier way, but I've never used vba in Excel, and (at the moment anyway) this is not a permanent solution.

 

[Brianwarnock]

Open up the vba editor , select this workbook and paste in the following code. change sheet1 to the name of your worksheet, assign your short cut and away you go.

NB my system is like me , old, so tested on 2002 but can't see any problems

Brian

 Sub xcopy()
  
Dim lastrow As Long

lastrow = Sheets("Sheet1").UsedRange.Rows.Count
Worksheets("Sheet1").Cells(lastrow + 1, 1).PasteSpecial
Application.CutCopyMode = False

 End Sub