0

I wan't to copy N60 values and paste it in O17 cell and subsequently all other data from different tables into all worksheet of the same workbook in specific columns

Data Format Master Table

enter image description here

 Sub Data_Click()

Dim wkSht As Worksheet
Dim cell As Range
Dim lastrow As Long, blankRow As Long, offsetRow As Long

lastrow = Sheets("Combine").UsedRange.Rows.Count

For Each cell In Sheets("Combine").Range("A2", "A" & lastrow).Cells

    For Each wkSht In ThisWorkbook.Worksheets
    
        If cell.Value = wkSht.Name Then
        
            If cell.End(xlDown).Row > lastrow Then
                blankRow = lastrow
            Else
                blankRow = cell.End(xlDown).Row - 1
            End If
            
            offsetRow = Application.WorksheetFunction.RoundDown(Sheet1.Cells(blankRow, 12).Value, 0)
            Sheets("Combine").Range(cell.Offset(0, 13), cell.Offset(offsetRow, 13)).Copy wkSht.Range("B17")
            Sheets("Combine").Range(cell.Offset(0, 18), cell.Offset(offsetRow, 18)).Copy wkSht.Range("017")
        End If
    
    Next wkSht

Next cell


End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Samiul30
  • 23
  • 2
  • 1
    Have a look at https://stackoverflow.com/q/30575923/4961700 – Solar Mike Nov 17 '21 at 10:19
  • For the first worksheet `SW BH 01`, using cell references (addresses), share what exactly will be copied where to, e.g. `A4:X22` to `B17:Y35`? Why are you not copying in one go per worksheet? Are there any merged cells? Is the data always 19 records (rows)? – VBasic2008 Nov 17 '21 at 11:03

0 Answers0