I usually find my answer here but I've been stuck on this one for several days now.
I'm working on 2 sheets, one with new data, and one with historical data.
On each sheet I have 'blocks' of columns, those blocks are always 4 columns wide, with a blank column in-between. And have different row length.
For example, there are data on column A to D, then a blank column, then data in column F to I, etc. as below:
|item 1||item 2|
|title 1||title 2||title 3||title 4||title 1||title 2||title 3||title4|
Goal: Need to check if the item number (ex: Cell A1 for the first block) is equal to the item number on the destination sheet (same cell A1).
Need to copy data from row 3 (first row with actual data) until the last row, for the first block of column, onto the 2nd sheet after the last row containing data. And create a loop for each block of columns until the last column of the source sheet.
Issue: The blocks of columns have different row length. Same thing on the destination sheet, each block have different row length. Which is why I need to use a dynamic range, for each block.
My code is here below, I managed to have it working for the first block of column by using the column letters, and try to modify it to use a dynamic range, but it's deleting all the data from row 3 on the destination sheet, and copying '#N/A' from "E3" until the very last column of the excel sheet (it does take into account the number of rows of the first block of column on the source sheet).
Sub copydata() Dim shta As Worksheet Dim sht1 As Worksheet Set shta = Sheets("source") Set sht1 = Sheets("target") Dim c As Long Dim LastCol As Long 'last column sheet source lrws As Long 'last row of source column frwt As Long 'first available row in target column Dim rngs As Range Dim rngt As Range Const frsrc As Long = 3 'first row on source column to copy from For c = 1 To c + 3 If shta.Cells(1, c).Value = sht1.Cells(1, c).Value Then 'ensure data item are the same lrws = shta.Cells(Rows.Count, c).End(xlUp).Row frwt = sht1.Cells(Rows.Count, c).End(xlUp).Row + 1 LastCol = shta.Cells(frsrc, Columns.Count).End(xlToLeft).Column Set rngs = shta.Range(c & frsrc).Resize(lrws, 4) 'source range of first block of columns Set rngtg = sht1.Range(c & frwt & ":" & c & frwt).Resize(rngs.Rows.Count) 'target range for first block of column rntg.Value = rngs.Value c = c + 4 'move to the next block of column Else: MsgBox "Need to check table formatting" End If Next c End Sub
There might be a better/easier way of doing it, than moving a block of columns at a time, but that's the only way I could think about.
I'm fairly new to VBA, and would really appreciate any comments/feedback you may have.
Thank you very much in advance!