syke27
03-09-2011, 10:38 AM
Hi all,
This is an alternate post to my last. I separated per the rules of not asking multiple questions!! Below is a macro that solves my quest to merge data from mult sheets to one but it has a couple flaws. As posted in my previous post, here are the requirements in case anyone has an alternate macro that could work vs. helping me fix the below! THANKS IN ADVANCE FOR ANY HELP!!!!! :-)
Requirements:
1. Merging Data from multiple sheets to one. I don't want to select these sheets as active. I'd just like the macro to know which sheets to pull from. There will be 7 sheets to start merging to 1 "Master".
2. I'd like to maintain Master Row A for headers, so the macro should start pasting from the first sheet in Row B.
3. Each sheet's data should paste at the bottom of the previous data on Master. Some of the macros I've tried writing are overwriting each sheet's data leaving me with just the final sheet's data in Master.
4. Don't paste blank rows from the sheets - just data. There will be no blank rows between data so it should just know to stop at the first blank row and move to the next sheet.
This first one works perfectly except:
1. I have to select the active sheets. I just want it to take from Sheets titled A, B, C, D, E, etc.
Macro:
Sub MergeSheets()
' Merges data from all the selected worksheets onto the end of the
' active worksheet.
Const NHR = 1 'Number of header rows to not copy from each MWS
Dim MWS As Worksheet 'Worksheet to be merged
Dim AWS As Worksheet 'Worksheet to which the data are transferred
Dim FAR As Long 'First available row on AWS
Dim LR As Long 'Last row on the MWS sheets
Set AWS = ActiveSheet
For Each MWS In ActiveWindow.SelectedSheets
If Not MWS Is AWS Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next MWS
End Sub
This is an alternate post to my last. I separated per the rules of not asking multiple questions!! Below is a macro that solves my quest to merge data from mult sheets to one but it has a couple flaws. As posted in my previous post, here are the requirements in case anyone has an alternate macro that could work vs. helping me fix the below! THANKS IN ADVANCE FOR ANY HELP!!!!! :-)
Requirements:
1. Merging Data from multiple sheets to one. I don't want to select these sheets as active. I'd just like the macro to know which sheets to pull from. There will be 7 sheets to start merging to 1 "Master".
2. I'd like to maintain Master Row A for headers, so the macro should start pasting from the first sheet in Row B.
3. Each sheet's data should paste at the bottom of the previous data on Master. Some of the macros I've tried writing are overwriting each sheet's data leaving me with just the final sheet's data in Master.
4. Don't paste blank rows from the sheets - just data. There will be no blank rows between data so it should just know to stop at the first blank row and move to the next sheet.
This first one works perfectly except:
1. I have to select the active sheets. I just want it to take from Sheets titled A, B, C, D, E, etc.
Macro:
Sub MergeSheets()
' Merges data from all the selected worksheets onto the end of the
' active worksheet.
Const NHR = 1 'Number of header rows to not copy from each MWS
Dim MWS As Worksheet 'Worksheet to be merged
Dim AWS As Worksheet 'Worksheet to which the data are transferred
Dim FAR As Long 'First available row on AWS
Dim LR As Long 'Last row on the MWS sheets
Set AWS = ActiveSheet
For Each MWS In ActiveWindow.SelectedSheets
If Not MWS Is AWS Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next MWS
End Sub