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
- Number to words in excel - Guide
- Gif in excel - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- Marksheet 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.
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?