Hi,
I'm trying to combine data that I have on 62 worksheets in my workbook. I've got the Macro working that pulls in my data from cells A4:P:66, and only puss in the info if they have data, but I can't figure out how to add the worksheet name to the end of the data on my "Import" worksheet, so in column Q, that way it has an indicator for me, so I know what worksheet the data actually came from. Any help would be much appreciated. Here is the macro I currently have:
Public Sub LaborFTE()
Dim wksSrc As Worksheet, wksDst As Worksheet
Dim rngSrc As Range, rngDst As Range
Dim lngLastCol As Long, lngSrcLastRow As Long, lngDstLastRow As Long
Set wksDst = ThisWorkbook.Worksheets("Import")
lngDstLastRow = LastOccupiedRowNum(wksDst) '
lngLastCol = LastOccupiedColNum(wksDst) '
Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)
For Each wksSrc In ThisWorkbook.Worksheets
If wksSrc.Name <> "Import" Then
lngSrcLastRow = LastOccupiedRowNum(wksSrc)
With wksSrc
Set rngSrc = .Range(.Cells(4, 1), .Cells(166, 16))
rngSrc.Copy Destination:=rngDst
End With
lngDstLastRow = LastOccupiedRowNum(wksDst)
Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)
End If
Next wksSrc
End Sub
I'm trying to combine data that I have on 62 worksheets in my workbook. I've got the Macro working that pulls in my data from cells A4:P:66, and only puss in the info if they have data, but I can't figure out how to add the worksheet name to the end of the data on my "Import" worksheet, so in column Q, that way it has an indicator for me, so I know what worksheet the data actually came from. Any help would be much appreciated. Here is the macro I currently have:
Public Sub LaborFTE()
Dim wksSrc As Worksheet, wksDst As Worksheet
Dim rngSrc As Range, rngDst As Range
Dim lngLastCol As Long, lngSrcLastRow As Long, lngDstLastRow As Long
Set wksDst = ThisWorkbook.Worksheets("Import")
lngDstLastRow = LastOccupiedRowNum(wksDst) '
lngLastCol = LastOccupiedColNum(wksDst) '
Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)
For Each wksSrc In ThisWorkbook.Worksheets
If wksSrc.Name <> "Import" Then
lngSrcLastRow = LastOccupiedRowNum(wksSrc)
With wksSrc
Set rngSrc = .Range(.Cells(4, 1), .Cells(166, 16))
rngSrc.Copy Destination:=rngDst
End With
lngDstLastRow = LastOccupiedRowNum(wksDst)
Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)
End If
Next wksSrc
End Sub