Help combining worksheets in excel 2007
Solved/Closed
Pauliolio
-
May 8, 2010 at 03:27 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 19, 2013 at 08:53 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 19, 2013 at 08:53 PM
Related:
- Help combining worksheets in excel 2007
- Save as pdf office 2007 - Download - Other
- Transfer data from one excel worksheet to another automatically - Guide
- Number to words in excel - Guide
- How to take screenshot in excel - Guide
- Dd-mm-yyyy format in excel - Guide
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 8, 2010 at 03:59 AM
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
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
Pluggie
Posts
11
Registration date
Monday March 11, 2013
Status
Member
Last seen
August 14, 2013
Mar 15, 2013 at 08:41 AM
Mar 15, 2013 at 08:41 AM
Worked like a charm!
Thanks a lot!!!
Thanks a lot!!!
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 15, 2013 at 10:16 AM
Mar 15, 2013 at 10:16 AM
you are welcome Pluggie
Pluggie
Posts
11
Registration date
Monday March 11, 2013
Status
Member
Last seen
August 14, 2013
Mar 18, 2013 at 05:37 AM
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?
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?
Pluggie
Posts
11
Registration date
Monday March 11, 2013
Status
Member
Last seen
August 14, 2013
Mar 18, 2013 at 11:34 AM
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 19, 2013 at 08:53 PM
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
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
May 8, 2010 at 04:30 AM
May 11, 2011 at 05:56 AM
Mar 11, 2013 at 05:49 AM
Can you edit the code above to accommodate for that?
Thanks in advance
Mar 11, 2013 at 09:05 PM
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.
Mar 15, 2013 at 04:37 AM
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 SubNow 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?