Help combining worksheets in excel 2007
Solved/Closed
Pauliolio
-
8 May 2010 à 03:27
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 19 Mar 2013 à 20:53
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 19 Mar 2013 à 20:53
Related:
- Help combining worksheets in excel 2007
- Save as pdf office 2007 - Download - Other
- Excel mod apk for pc - Download - Spreadsheets
- Transfer data from one excel worksheet to another automatically - Guide
- Combine notification in viber means - Guide
- Kernel for excel repair - Download - Backup and recovery
2 responses
rizvisa1
Posts
4478
Registration date
Thursday 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
8 May 2010 à 03:59
8 May 2010 à 03:59
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 11 March 2013
Status
Member
Last seen
14 August 2013
15 Mar 2013 à 08:41
15 Mar 2013 à 08:41
Worked like a charm!
Thanks a lot!!!
Thanks a lot!!!
rizvisa1
Posts
4478
Registration date
Thursday 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
15 Mar 2013 à 10:16
15 Mar 2013 à 10:16
you are welcome Pluggie
Pluggie
Posts
11
Registration date
Monday 11 March 2013
Status
Member
Last seen
14 August 2013
18 Mar 2013 à 05:37
18 Mar 2013 à 05:37
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 11 March 2013
Status
Member
Last seen
14 August 2013
18 Mar 2013 à 11:34
18 Mar 2013 à 11:34
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 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
19 Mar 2013 à 20:53
19 Mar 2013 à 20:53
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
8 May 2010 à 04:30
11 May 2011 à 05:56
11 Mar 2013 à 05:49
Can you edit the code above to accommodate for that?
Thanks in advance
11 Mar 2013 à 21:05
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.
15 Mar 2013 à 04:37
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?