Hey Guys!
Objetive: Compile data from multiple sheets in just one.
I have a workbook with multiple sheets where I'm using a code to be able to compile all the information, which are informations (calculeted by formulas) and organized on a single line in each tab that are my source.
(see images). However, in the code I'm using, my data is read as formulas and ends up entering the compiled spreadsheet with error ... So I need help changing my code to paste Values instead of just normal format pasting in the main spreadshhet.
Can you help me?
This is the code that I am using and where I got from (danwagner's blog, title: how-to-combine-data-from-multiple-sheets-into-a-single-sheet)
Public Sub CombineDataFromAllSheets()
Dim wksSrc As Worksheet, wksDst As Worksheet
Dim rngSrc As Range, rngDst As Range
Dim lngLastCol As Long, lngSrcLastRow As Long, lngDstLastRow As Long
'Notes: "Src" is short for "Source", "Dst" is short for "Destination"
'Set references up-front
Set wksDst = ThisWorkbook.Worksheets("Import")
lngDstLastRow = LastOccupiedRowNum(wksDst) '<~ defined below (and in Toolbelt)!
lngLastCol = LastOccupiedColNum(wksDst) '<~ defined below (and in Toolbelt)!
'Set the initial destination range
Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)
'Loop through all sheets
For Each wksSrc In ThisWorkbook.Worksheets
'Make sure we skip the "Import" destination sheet!
If wksSrc.Name <> "Import" And wksSrc.Name <> "Agenda" Then
'Identify the last occupied row on this sheet
lngSrcLastRow = LastOccupiedRowNum(wksSrc)
'Store the source data then copy it to the destination range
With wksSrc
Set rngSrc = .Range("C44", "P44")
rngSrc.Copy Destination:=rngDst
End With
'Redefine the destination range now that new data has been added
lngDstLastRow = LastOccupiedRowNum(wksDst)
Set rngDst = wksDst.Cells(lngDstLastRow + 1, 1)
End If
Next wksSrc
End Sub
Thanks a lot!
Bookmarks