Help combining worksheets in excel 2007

Solved/Closed
Pauliolio - May 8, 2010 at 03:27 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 19, 2013 at 08:53 PM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
May 8, 2010 at 03:59 AM
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
Thanks for that, problem solved :)
0
thanks it worked
0
Pluggie Posts 13 Registration date Monday March 11, 2013 Status Member Last seen August 14, 2013
Mar 11, 2013 at 05:49 AM
What if I want to consolidate sheets from a different Workbook?
Can you edit the code above to accommodate for that?

Thanks in advance
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Mar 11, 2013 at 09:05 PM
@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.
0
Pluggie Posts 13 Registration date Monday March 11, 2013 Status Member Last seen August 14, 2013
Mar 15, 2013 at 04:37 AM
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?
0
Pluggie Posts 13 Registration date Monday March 11, 2013 Status Member Last seen August 14, 2013
Mar 15, 2013 at 08:41 AM
Worked like a charm!

Thanks a lot!!!
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Mar 15, 2013 at 10:16 AM
you are welcome Pluggie
0
Pluggie Posts 13 Registration date Monday March 11, 2013 Status Member Last seen August 14, 2013
Mar 18, 2013 at 05:37 AM
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?
0
Pluggie Posts 13 Registration date Monday March 11, 2013 Status Member Last seen August 14, 2013
Mar 18, 2013 at 11:34 AM
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
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Mar 19, 2013 at 08:53 PM
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
0