Combining multiple Excel Worksheets into one worksheet

bbecker

New Member
Joined
Aug 10, 2011
Messages
1
I'm trying to combine multiple Worksheets of the same format into one worksheet. I have created a vb script that should work. I'm having trouble setting the range since each sheet has a different amount of rows.


With mybook.Worksheets(1)
Set sourceRange = .Range("A1:H10")
End With
Thanks in advance.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi bbecker,

This macro plaes all worksheets into one worksheet, evean the hidden ones. Got it from this message board.

Hope it helps.

Sub consolidate()
On Error Resume Next
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets("ConsolidatedData").Delete
Sheets.Add Before:=Sheets(1)
Sheets(1).Name = "ConsolidatedData"
Dim ii As Long
For ii = 2 To Sheets.Count
Sheets(ii).Range("A1:bA10000").Copy 'change the range to copy
With Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
.PasteSpecial xlValues
End With
Next ii
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Range("a1").Activate
End Sub
 
Upvote 0
Hi bbecker,

Welcome to the forum!!

Try this which dynamically sets the last row on r=each tab being consolidated:

Code:
Sub Macro1()
    
    Dim strConsTabName As String
    Dim varConsSheetLen As Variant
    Dim lngLastRow As Long, _
        lngPasteRow As Long
    
    strConsTabName = "Consolidation" 'Consolidation tab name.  Change to suit.
    
    Application.ScreenUpdating = False
    
    'If there's no tab called by the named passed to the 'strConsTabName' variable, then...
    On Error Resume Next
        varConsSheetLen = Len(Sheets(strConsTabName).Name)
        If IsEmpty(varConsSheetLen) = True Then
            '...create it at the at the start (furtherest left) of the workbook.
            Worksheets.Add Before:=Sheets(1)
            Sheets(1).Name = strConsTabName
        'Else...
        Else
            '...delete it and recreate it (simply clearing the data may be another option).
            With Application
                .DisplayAlerts = False
                Sheets(strConsTabName).Delete
                .DisplayAlerts = True
                Worksheets.Add Before:=Sheets(1)
                Sheets(1).Name = strConsTabName
            End With
        End If
    On Error GoTo 0
    
    For Each Worksheet In ThisWorkbook.Sheets
        
        If Worksheet.Name <> strConsTabName Then
            'Find where the last row resides from Col's A to H
            lngLastRow = Worksheet.Range("A:H").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            'Set the 'lngPasteRow' variable.
            If WorksheetFunction.CountA(Sheets(strConsTabName).Cells) = 0 Then
                lngPasteRow = 1
            Else
                lngPasteRow = Sheets(strConsTabName).Range("A:H").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
            End If
         
            Worksheet.Range("A1:H" & lngLastRow).Copy _
                Destination:=Sheets(strConsTabName).Range("A" & lngPasteRow)
        
        End If
        
    Next Worksheet
    
    Application.ScreenUpdating = True
            
End Sub

HTH

Robert
 
Upvote 0
Hi Robert, I have understood your macros but I do not know how to use it and adapt it to mine.



I my case I have 7 sheets corresponding to 7 consecutive days. i will like to use a userform to input week start date of next week in one of the sheets (or a new page) and this automatically renames all the sheets with dates as 7 consecutive days beginning from weekstart date inputed in the textbox.


I have different sheets but I want the consolidation only for sheets labelled with date and the range is fixed, but the consolidation sheet should have the same heading as each of the sheets it is consolidating



I already have a code to delete all previous entries in the worksheets that I want to rename before they are renamed.

Also I wil like the workbook to be first saved,
afterthe rename save a copy with name difference only in week number

I look forward to your support

Thanks
 
Upvote 0

Forum statistics

Threads
1,224,599
Messages
6,179,828
Members
452,946
Latest member
JoseDavid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top