Hello Sir, I have stored the data of multiple worksheets that I want to store on the single worksheet without copy-paste. The Rows & columns are not fixed on each worksheet. I want VBA code to add all records of each worksheet on a single sheet.
Thanks & regards,
Prince
Combine data of multiple worksheets on single sheet
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
-
- Administrator
- Posts: 78378
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combine data of multiple worksheets on single sheet
Will the sheets have one or more header rows? If so, how many?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
Re: Combine data of multiple worksheets on single sheet
Each worksheet has three rows of header. I want only 3rd row of header on (combined) worksheet
-
- Administrator
- Posts: 78378
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combine data of multiple worksheets on single sheet
Here is a macro:
Code: Select all
Sub CombineSheets()
Dim ws As Worksheet
Dim wt As Worksheet
Dim f As Boolean
Dim t As Long
Dim rng As Range
Application.ScreenUpdating = False
On Error Resume Next
Set wt = Worksheets("Summary")
On Error GoTo ErrHandler
If wt Is Nothing Then
Set wt = Worksheets.Add(Before:=Worksheets(1))
wt.Name = "Summary"
Else
wt.Cells.Clear
End If
f = True
For Each ws In Worksheets
Select Case ws.Name
Case "Summary" ' You can add other sheet names, separated by commas
' Skip sheet
Case Else
' Is this the first sheet we copy?
If f Then
' If so, copy the third header row
wt.Range("A1").EntireRow.Value = ws.Range("A3").EntireRow.Value
f = False
End If
t = wt.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
Set rng = ws.UsedRange
Set rng = rng.Offset(3).Resize(rng.Rows.Count - 3)
wt.Range("A" & t).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
End Select
Next ws
ExitHandler:
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1098
- Joined: 21 Jan 2011, 16:51
- Location: Florida
Re: Combine data of multiple worksheets on single sheet
Hans,
Thank you! I was starting on an organizing project for all the external drives I have to manage, and was thinking I'd need something along these lines, too.
Thank you! I was starting on an organizing project for all the external drives I have to manage, and was thinking I'd need something along these lines, too.
PJ in (usually sunny) FL
-
- 2StarLounger
- Posts: 171
- Joined: 02 Mar 2015, 17:00
Re: Combine data of multiple worksheets on single sheet
Thank you sir, it's working as per my requirements.
Thanks a lot sir.
With warm regards
Prince
Thanks a lot sir.
With warm regards
Prince