Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
This script initializes the destination sheet then combines data from certain sheets (and not others)
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
@Kris21tin

This comment has been minimized.

Copy link

commented Apr 8, 2018

Hi there,

I used this code which worked well, however, sheets with vba codes (that I do not want the data combined) gets included. Do you know what's happening there? Thank you!

Regards,
Tin

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.