Excel OR VBA. Transfer of data from multiple sheets to a single sheet.

[andymalan]

Excel OR VBA Transfer of data from multiple sheets to a single sheet.

I have a pdf doc “DrawingData.pdf” that has (in this case), 5 pages, named pages 1 through 5. (there will be occasions when there will sometimes be more pages, and sometimes less).
I convert this pdf to an excel workbook named “DrawingData.xlsx”, and there are 5 sheets named page 1, page 2, 3, 4, 5.
I also have a macro-enabled work book named “configuredata”, and I want to transfer all the data from “DrawingData.xlsx”, to sheet 1 of “configuredata.xlsm”, so that the data can be manipulated.( I’ll be doing that using macros I have cobbled together)
The problem is that the data on each page is not always the same length, (as in number of rows). 2 saving factors however, is that the last row of each page always has the words “end of report” in column B, and the first row has "Quote Nr" in A1.
What I have thus far is that in work book “configuredata” I created 10 sheets; sheets 1 through 10, with each cell in each sheet having the formula (relative to the sheets) =IF('[DrawingData.xlsx]Page 1'!$A$1="Quote Nr",'[DrawingData.xlsx]Page 1'!A1,"").
That brings in all the data onto each sheet (page 1 of DrawingData gets sucked into sheet1 of configuredata) etc.
All’s well until sheet 6 of configuredata, which gives an error #REF!, because there is no page 6, or 7 or 8-10 and each of the cells in each of the sheets gives the same error.
I’m sure that this will be a simple thing for the boffins to solve, but to me it’s a steep mountain to climb.
I will be so grateful for any helpful information. Many thanks in advance.

 

[ffionnah]

Hia,
This is the formula I always use when combining multiple sheets.

The only thing you need to do with this is create a new tab and rename is "Master" and make sure your headers are the same on all tabs. I always copy the headers into the "Master" sheet as well. In your new "Master" tab, just run the macro.

There may be a better and easier code someone else can provide, but this is my go to as it will adjust to your headers/data.

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("Master")
    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 "Master" destination sheet!
        If wksSrc.Name <> "Master" 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(.Cells(2, 1), .Cells(lngSrcLastRow, lngLastCol))
                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
 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'INPUT       : Sheet, the worksheet we'll search to find the last row
'OUTPUT      : Long, the last occupied row
'SPECIAL CASE: if Sheet is empty, return 1
Public Function LastOccupiedRowNum(Sheet As Worksheet) As Long
    Dim lng As Long
    If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
        With Sheet
            lng = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Row
        End With
    Else
        lng = 1
    End If
    LastOccupiedRowNum = lng
End Function
 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'INPUT       : Sheet, the worksheet we'll search to find the last column
'OUTPUT      : Long, the last occupied column
'SPECIAL CASE: if Sheet is empty, return 1
Public Function LastOccupiedColNum(Sheet As Worksheet) As Long
    Dim lng As Long
    If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
        With Sheet
            lng = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              Lookat:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByColumns, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Column
        End With
    Else
        lng = 1
    End If
    LastOccupiedColNum = lng
End Function

 

[andymalan]

Hi ffionah, thank you for taking the time to help me. I think i may not have explained myself properly
I ran the code, but only col A was populated with data. please help me with getting all the data(A1:H1) from each page in "DrawingData" to master sheet.
drawingdata page 1 goes to master sheet A1
drawingdata page 2 goes to master sheet next available row
drawingdata page 3 goes to master sheet next available row etc
kind regards Andy.