I'm really struggling with this one (pretty new to VBA).
I have a workbook that pulls financial data for a range of dates and places each company's information into a separate worksheet. I'm trying to figure out a way to copy the resulting data from those tabs into one master tab named "Results." The format for each of the source worksheets are all the same.
I have a script that is successfully pulling the data from all of the worksheets into one worksheet, but it is stacking them all one on top of the other in the same columns (i.e. A1:G), when what I really need is for each to be side-by-side in successive columns for comparison and analysis.
The next problem is that not all of the worksheets have the full range of dates, so I need to figure out a way to have the destination of the pasted data to correspond with the correct date for that row (either already entered in Column A of the "Results" worksheet or, preferably, based on the worksheet with the most complete set of dates).
I could do all of this with formulas, but given the number of sheets I'm working with it slows things down way too much.
Here's the code I've been working with:
Sub move_data()
Dim Sht As Worksheet
Dim Rng As Range
For Each Sht In Sheets
If Sht.Name = "Results" Or Sht.Name = "Parameters" Then
Else
Set Rng = Sht.Range("A1:G" & Sht.Range("G" & Rows.Count).End(xlUp).Row)
Rng.Copy Destination:=Sheets("Results").Range("A" & Rows.Count).End(xlUp).Offset(0, 0)
End If
Next Sht
With Sheets("Results").Range("A1:G" & Range("G" & Rows.Count).End(xlToRight).Row)
End With
End Sub
If there's a better way of doing it, I have no problem scrapping this code for another.
Would really appreciate any help.
Bookmarks