How to Combine Data from Certain Sheets but Not Others into a Single Sheet

I need to copy everything from certain sheets but not others, allowing duplicate data…

Shutting down certain Sheets like Craig T. Nelson

What if you need to combine data from some sheets in your Workbook, but not all of them?

Suppose you have a Worksheet for each day of the month that you need to combine, but you need to skip all the other Worksheets — like this:

Combine the days of the month, skip everything else

Easy peasy! We’re going to use the same technique implemented here, with more guards in the If statement to make sure those other Worksheets are not copied. Here goes:

	Option Explicit
	Public Sub CombineCertainSheets()
	Dim wks As Worksheet, wksDst As Worksheet
	Dim strName As String
	Dim lngSrcLastRow As Long, lngDstLastRow As Long, _
	lngLastCol As Long
	Dim rngSrc As Range, rngDst As Range, rngToClear As Range
	'Set references up-front
	Set wksDst = ThisWorkbook.Worksheets("ALPHA")
	lngLastCol = LastOccupiedColNumInRow(wksDst, 1)
	'Identify the last-occupied row on the ALPHA sheet, clearing
	'any pre-existing values that are not headers
	lngDstLastRow = LastOccupiedRowNumInCol(wksDst, 1)
	If lngDstLastRow > 1 Then
	With wksDst
	.Range(.Cells(2, 1), .Cells(lngDstLastRow, lngLastCol)).ClearContents
	End With
	End If
	'Now that the destination has been initialized, set the
	'first destination range to row 2, column A
	Set rngDst = wksDst.Cells(2, 1)
	'Loop through all of the worksheets in this workbook, skipping
	'the names we know we DO NOT want to combine into ALPHA
	For Each wks In ThisWorkbook.Worksheets
	'If this sheet name is NOT one that we want to ignore,
	'append the data to the ALPHA sheet
	strName = UCase(wks.Name)
	If strName <> "1ST ORIG" And _
	strName <> "BUSIEST DAY" And _
	strName <> "MASTER" And _
	strName <> "ALPHA" And _
	strName <> "ARINC" And _
	strName <> "ARINC ARRIVAL" Then
	'Yay! If we have gotten here, then this is one of the
	'numbered sheets that we want to combine!
	'Identify the last occupied row on this sheet
	'by looking in column A
	lngSrcLastRow = LastOccupiedRowNumInCol(wks, 1)
	'Store the source data then copy the values and number formats,
	'followed by the formulas
	With wks
	Set rngSrc = .Range(.Cells(7, 1), .Cells(lngSrcLastRow, lngLastCol))
	rngSrc.Copy
	rngDst.PasteSpecial xlPasteValuesAndNumberFormats
	rngDst.PasteSpecial xlPasteFormulas
	End With
	'Redefine the destination range now that new data has been added
	lngDstLastRow = LastOccupiedRowNumInCol(wksDst, 1)
	Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)
	End If
	Next wks
	'Let the user know our macro is complete!
	MsgBox "Sheets combined!"
	End Sub
	'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	'INPUT : Sheet, the worksheet we'll work on. ColNum, the number of the
	' : column we'd like to find the last occupied row in
	'OUTPUT : Long, the last occupied row in the column
	'SPECIAL CASE: if ColNum is <= 0, return 0 (error condition)
	Public Function LastOccupiedRowNumInCol(Sheet As Worksheet, _
	ColNum As Long) As Long
	Dim lng
	If ColNum > 0 Then
	With Sheet
	lng = .Cells(.Rows.Count, ColNum).End(xlUp).Row
	End With
	Else
	lng = 0
	End If
	LastOccupiedRowNumInCol = lng
	End Function
	'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	'INPUT : Sheet, the worksheet we'll work on. RowNum, the number of the
	' : row we'd like to find the last-occupied column in
	'OUTPUT : Long, the last occupied column in the row
	'SPECIAL CASE: if RowNum <= 0, return 0 (error condition)
	Public Function LastOccupiedColNumInRow(Sheet As Worksheet, _
	RowNum As Long) As Long
	Dim lng As Long
	If RowNum > 0 Then
	With Sheet
	lng = .Cells(RowNum, .Columns.Count).End(xlToLeft).Column
	End With
	Else
	lng = 0
	End If
	LastOccupiedColNumInRow = lng
	End Function

view raw combine_certain_sheets.vb hosted with by GitHub

Here’s a link to the code above so you can review it side-by-side with the walk through below. Right-click, “Open in new window”.

Per usual, we are going to review this code using the 4-step VBA process:

Step 1 – Setup
Step 2 – Exploration
Step 3 – Execution
Step 4 – Cleanup

We get through Step 1 – Setup on lines 11-25.

First, on lines 11-12, we assign wksDst (the destination Worksheet) and lngLastCol (the last-occupied column for our data).

Line 12 takes advantage of LastOccupiedColNumInRow, a super-handy function from the VBA Toolbelt. You’re using the VBA Toolbelt, right?

Then, from line 16 to line 21, we initialize “ALPHA”, the destination sheet. If the last-occupied row on the destination sheet is greater than 1 (i.e. the header row), then we know some data already exists. In that case, we use the Range.ClearContents method to get rid of that data in preparation for adding the individual days’ data.

Finally, on line 25, we assign the initial destination range (rngDst) — and with that, our setup is complete!

Next up is Step 2 – Exploration, which runs from lines 29 to 46.

The For Each…Next loop that begins on line 29 allows us to iterate through each Worksheet, which is stored in wks. This means that on each cycle of the loop, wks will be set to a different Worksheet in the file.

First, we use the Worksheet.Name method to store the upper case name of wks in strName, which makes for a very easy-to-read next step.

We know that we need to skip any Worksheet that is not “1ST”, “2ND”, “3RD”, etc., so we check each name using a multi-line If statement in lines 34-39.

Assuming that wks is NOT one of the Worksheets to be ignored, Step 2 – Exploration wraps up on line 46. Here, the last-occupied row on the Worksheet is identified and stored as lngSrcLastRow.

Wahoo — exploration complete!

We then move into Step 3 – Execution, which runs from lines 50-59.

Using lngSrcLastRow, the last variable we assigned in the exploration step, we assign the data range to rngSrc on line 51.

In this particular example, we only want the values and formulas from each data Worksheet. With that as our guiding princple, lines 52-54 take care of the job.

1. Line 52: Copy the source data using the Range.Copy method
2. Line 53: Paste to the destination using the Range.PasteSpecial method, first sending over the values and number formats ONLY
3. Line 54: Paste to the destination using the same method of #2 above, this time sending over the formulas ONLY

Nice!

Now that this particular Worksheet’s data has been copied to the destination, we need to update the destination range to match the new bottom of the data block.

This occurs on lines 58-59, where we use LastOccupiedRowNumInCol again (seriously, get the VBA Toolbelt), then set rngDst to reflect this new last occupied row.

Jackpot — that’s it for execution!

There isn’t much Step 4 – Cleanup to do in this case, so we simply let the user know all the data has been combined with a MsgBox on line 66.

And with that, we have successfully combined certain Worksheets while skipping others! In case you prefer video, here’s a 7-minute screencast walking through these steps:

Are you combining some and skipping other Worksheets with the best of them? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.