Combining different worksheets into a single worksheet.

jimmy2016 · #1 10-12-2016, 05:56 PM

Dear helpers,

I am new to VBA and have the problem:
To combine worksheets into a single worksheet we always use copy and paste but if there are many worksheets, say 30, it will spend a lot of time in selecting and copying.
I want to create a button in sheet1 and onclick it, those tables in the worksheets(named A01,..,A30) will be copied to sheet1, say from sheet1!A15.

1. The worksheets will be of the same number of columns:
A1(name), B11(date), C11(number), D11(price)
but different rows of data.

2. In the workbook, there are other worksheets and the worksheets I want to cope with are A01,...A30 only.

3. The are other data in worksheets A01,...,A30 and the tables I want to copy are, say, A01!A11:P19, A02!A11:P13,....
Can the VBA code count the number of rows in the table in each worksheet?

Thanks for your help.

charlesdh · #2 10-13-2016, 12:47 PM

Hi,

Can you post a workbook. This will help.
Supply a example as to what you want too see.

Pecoflyer · #3 10-14-2016, 12:27 AM

Why not try this add-in from Ron de Bruin ?

jimmy2016 · #4 10-14-2016, 02:01 AM

Combine sheets A01 .... A04 into Sheet1

NoSparks · #5 10-15-2016, 09:05 AM

Something along the lines of this should do it.
Hopefully you can follow the commenting.

Code:

Sub CombineJimmysSheets()
    
Dim i As Integer, sht As Worksheet, RngLR As Long
    
Application.ScreenUpdating = False
'loop thru numbers
For i = 1 To 30
    'loop thru sheets
    For Each sht In ThisWorkbook.Sheets
        'if sheet name found
        If sht.Name = "A" & Format(i, "00") Then
            With sht
                'determine last row of range
                RngLR = .Range("A4").End(xlDown).Row
                'copy the range and paste to sheet 1
                .Range("A5:P" & RngLR).Copy _
                Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
            End With
            'don't loop any further looking for sheet already found
            Exit For
        End If
    'check next sheet
    Next sht
'check for next number
Next i
Application.ScreenUpdating = True
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Combine values from different worksheets into a single worksheet.	jimmy2016	Excel	2	10-06-2016 09:15 AM
How do I populate Worksheets using information entirely from another Worksheet in the same Workbook?	BrieDanielle	Excel	1	06-18-2016 10:50 AM
Combine or merge multiple worksheets into one worksheet	timomaha	Excel	1	07-21-2014 01:02 PM
Multiple Dynamic Worksheets Consolidated into One Static Worksheet	mars1886	Excel Programming	3	02-09-2014 12:50 AM
Cells from other worksheets apear on current worksheet	Equilar	Excel	3	05-03-2010 01:40 AM