How can I extract data from database sheet and paste matched range in different sheets

Question

I have multiple sheets in a workbook. I wan't to copy cells containing SPT values for each Boreholes from the "combine" sheet based on sheetname matching within range listed within "A5:A116" or may be more after final data entry to the sheet matching with the borehole ID /cell value within range "A5:A116" and paste it below the SPT column. This has to continue through a loop process for all the sheets. Also a relative referencing from the matched cell to the range to be copied will be required. In the code the range D5:D16 will have to be a floating ranged based on relative referencing from cell value. Also the paste range will start from 1 cell below the SPT value in the matched.name sheet The code I am working on is as follows the relative referencing for range does not seem to work:

 Sub example()

 Dim wkSht As Worksheet

  For Each wkSht In Sheets

    For Each Cell In Sheets("Combine").Range("A5:A116")

        If Cell.Value = wkSht.Name Then

            On Error Resume Next

            Sheets("Combine").Range("D5:D16").Copy Destination:=wkSht.Range("D29:D52")
        End If

    Next Cell

 Next wkSht

End Sub

Data Extraction in different sheets

Kin Siang Kin Siang · Accepted Answer · 2021-06-06T00:59:27

You sequence for the looping is incorrect, and you did not declare the variable for cell, please try the following code modification and it shall work properly:

Sub example()

Dim wkSht As Worksheet
Dim cell As Range

For Each cell In Sheets("Combine").Range("A5:A116").Cells

    For Each wkSht In ThisWorkbook.Worksheets
    
        If cell.Value = wkSht.Name Then
       
            Sheets("Combine").Range(cell.Offset(0, 11), cell.Offset(30, 11)).Copy wkSht.Range("D29")
        End If
    
    Next wkSht

Next cell

End Sub

How can I extract data from database sheet and paste matched range in different sheets

1 Answers