Help combining worksheets in excel 2007 [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
We use excel 2007 to store information about our products and have a different worksheet for each range.

Is there any way of combining all these worksheets into one long page? All the columns are excactly the same as are the titles in the top row.

Any hep is greatly appreciated

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
761
Assumptions
1. A sheet titled Consolidated can be created and deleted by macro
2. Headers are on row 1 on all sheets and is common for all
3. All sheets are to be copied

Sub CombineSheets()
Dim lConRow As Long
Dim Sheet As Variant
Dim sConsolidatedSheet As String
Dim lSheetRow As Long
Dim sLastCol As String

    sConsolidatedSheet = "Consolidated"

    On Error Resume Next
    
        Sheets(sConsolidatedSheet).Delete
    
    On Error GoTo 0
    
    Sheets.Add
    ActiveSheet.Name = sConsolidatedSheet
    
    
    For Each Sheet In Sheets
    
        If Sheet.Name = sConsolidatedSheet Then GoTo Next_Sheet
        
        If sLastCol = "" Then
            sLastCol = Sheet.Cells(1, Columns.Count).End(xlToLeft).Address
            Sheets(sConsolidatedSheet).Range("1:1") = Sheet.Range("1:1").Value
            lConRow = 1
        End If
        
        lSheetRow = 0
        
        On Error Resume Next
            lSheetRow = Sheet.Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        On Error GoTo 0
        
        If (lSheetRow > 1) Then
            Sheets(sConsolidatedSheet).Range(lConRow + 1 & ":" & lSheetRow + lConRow - 1) = Sheet.Range("2:" & lSheetRow).Value
            lConRow = Sheets(sConsolidatedSheet).Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                           
        End If
        
Next_Sheet:
    
    Next
End Sub
5
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

thanks it worked
Posts
13
Registration date
Monday March 11, 2013
Status
Member
Last seen
August 14, 2013

What if I want to consolidate sheets from a different Workbook?
Can you edit the code above to accommodate for that?

Thanks in advance
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
761
@Pluggie :"What if I want to consolidate sheets from a different Workbook?
Can you edit the code above to accommodate for that?


You just have to loop thru the workbooks as it is looping for the sheets.
Posts
13
Registration date
Monday March 11, 2013
Status
Member
Last seen
August 14, 2013

Hmm...

I actually want to modify the macro I was using.
It was designed to copy and replace the contents of a sheet with that from a sheet in a different workbook.
Here is that code.
Sub Copy_source_sheet_from_source()
'
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Cells.Select
    Selection.ClearContents
    Workbooks.Open Filename:="sourcefile.xlsx"
    Cells.Select
    Selection.Copy
    Windows("destinationfile.xlsm").Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Cells.EntireColumn.AutoFit
    
    'close source sheet
    Windows("sourcefile.xlsx").Close
    Windows("destinationfile.xlsm").Activate
    Range("A1").Select
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub


Now how do I incorporate the code you wrote into this one, so that it not only copies the first sheet of the source, but copies the contents of all sheets and consolidates them into the destination sheet?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
761
try this way

1. copy the routine at https://ccm.net/forum/affich-342549-help-combining-worksheets-in-excel-2007#1

2.edit your macro as highlighted

Sub Copy_source_sheet_from_source()
Dim sConsolidatedSheet As String
    sConsolidatedSheet = "Consolidated"
'
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Cells.Select
    Selection.ClearContents
    Workbooks.Open Filename:="sourcefile.xlsx"
    
    Call CombineSheets()
    Sheets(sConsolidatedSheet).Select

    Cells.Select
    Selection.Copy
    Windows("destinationfile.xlsm").Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Cells.EntireColumn.AutoFit
    
    'close source sheet
    Windows("sourcefile.xlsx").Close False 
    Windows("destinationfile.xlsm").Activate
    Range("A1").Select
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub
Posts
13
Registration date
Monday March 11, 2013
Status
Member
Last seen
August 14, 2013

Worked like a charm!

Thanks a lot!!!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
761
you are welcome Pluggie
Posts
13
Registration date
Monday March 11, 2013
Status
Member
Last seen
August 14, 2013

Hey Rizvisa 1,

This macro takes a bit of time to execute (files are on networkdrive).
But the source-file is not constantly updated.
I could add a question to the macro asking the user if he wants to update file or not, but that creates the risk of missing out on the lastest update(s).

In stead, could I have the macro first check to see if a newer version of the sourcefile exists and make that check determine if a new update is required?

I checked some online sources and found a possible lead with "variable = FileDateTime(File location)", but the catch is, that the sourcefile's name is always the same. So somehow the macro should "store" the FileDateTime of the sourcefile during the previous update and compare it to the current FileDateTime. I imagine that we could store it in a separate (hidden) worksheet on the destinationfile?

Anyway, maybe you could help me outline the best way to approach this?
Posts
13
Registration date
Monday March 11, 2013
Status
Member
Last seen
August 14, 2013

Written a working if statement at the top of my main macro.

Dim StoreDate As Date 
Dim CompareDate As Date 
sConsolidatedSheet = "Consolidated" 
StoreDate = Sheets("Hidden").Range("B2").Value 
CompareDate = FileDateTime("Sourcefile.xlsx") 

    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 

If CompareDate = StoreDate Then 
    Exit Sub 
Else: Sheets("Hidden").Activate 
    Range("B2").Select 
    Selection.Value = CompareDate 
End If
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
761
You got it!!
As for being slow, one option would be to copy the sheet on your desktop, then let the macro work to combine then do copy

1. macro run
1.1 copy file from network to pc
1.2 run Copy_source_sheet_from_source

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!