Forum Home
Press F1
 
Thread ID: 100189 2009-05-29 09:10:00 Excel Macro help acp1909 (14950) Press F1
Post ID Timestamp Content User
777954 2009-05-29 09:10:00 Hi

I know next to nothing about macros. Here's what I want to do:
I have 1 workbook with multiple sheets named 'Report list', 'Consolidated', 'master', and the rest are named ' Stats_date' 'Stats_Name_Age', 'Stats_Colour_Type' etc ...I will keep adding new worksheets with name 'Stats...'
Each Stats sheet is a copy of the master and has exactly same format i.e. required data in B1: B50
I want to copy data in range B1:B50 of each Stats sheet in sheet named 'Consolidated', i.e. 'Stats_date' Range B1:B50 must be copied in Range B1:B50 of 'Consolidated' sheet, 'Stats_Name_Age' Range B1:B50 must be copied in Range C1:C50 of 'Consolidated' sheet,'Stats_Colour_Type' Range B1:B50 must be copied in Range D1:D50 of 'Consolidated' sheet and so on...I could end up with 50 Stats worksheets to be copied into "Consolidated sheet.

I have tried to use macros available on this forum and adapt them but I keep getting errors. You guys are my last hope!! Here's the code:

Sub Consolidation()
Dim ws As Worksheet, wsConso As Worksheet, SourceCol As Integer, Source As Range
Set wsConso = Worksheets("Consolidated")
For Each ws In Worksheets
If ws.Name <> "Master" And ws.Name <> "Consolidated" And ws.Name <> "Report List" Then
Source = ws.Range("B1:B50")
If wsConso.Range("b1").Value = "" Then
SourceCol = 2
ElseIf wsConso.Range("IV1").Value <> "" Then
MsgBox "There are no more columns available in the sheet " & wsConso.Name, vbCritical, "No More Data Can Be Copied"
Exit Sub
Else
SourceCol = wsConso.Range("IV1").End(xlToLeft).Column + 1
End If
Source.Copy wsConso.Cells(1, SourceCol)
End If
MsgBox "Data copied successfully!", vbInformation, "Process Complete"
Exit Sub
Next ws
End Sub

I hope you can find the solution!
Thanks 		acp1909 (14950)
777955 2009-05-29 09:37:00 Have you tried the consolidated function from different work sheets? Tutorial here (www.bettersolutions.com), and if there are several groups the same within each table, then pivot tables may help to group them to calculate the total/grand stats and can convert them to charts. kahawai chaser (3545)
777956 2009-05-29 11:29:00 Thanks for your suggestion.
The data that I have on the stats_worksheets are a mix of text and numbers. I do not want to add up or make averages of the worksheets. I just want to copy all the data on one worksheet.
I could do it by copying each worksheet and pasting on the sheet I called 'Consolidation' but this would be too time consuming...Any other way of doing this? 		acp1909 (14950)
777957 2009-05-29 15:47:00 One way is to search online for an Excel add in utility that can group/merge rows from worksheets... kahawai chaser (3545)
1