Copy data from many worksheets to a compiled

Closed
Hobbs - Jul 14, 2009 at 04:34 PM
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 - Jul 17, 2009 at 12:29 AM
Hello,
I want to write a macro for some data in excel. I have multiple worksheets that I need to copy a column of data from each and paste them into rows in another "compiled" workbook. I have gotten the point where I am copying the data from each but am pasting them into the same row as the prior. I need a loop that pasted the next worksheet's data into the next row. Can you help me?

Here's my code...

Sub AllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Range("C45:C55").Select
Application.CutCopyMode = False
Selection.copy
Windows("FM2&6_1res_mysc.xlsm").Activate
Range("A3").Select 'first row of data begins at 3rd row of 2nd column, want a loop that adds to row below'
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Next
End Sub

1 response

Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Jul 17, 2009 at 12:29 AM
Hello Hobbs

Use Range("B4").End(xlDown).Select technique to find the last unused row
1