Hi everyone,
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 | |
data | data | data | data | data | data | data | data | |
data | data | data | data | data | data | data | data | |
data | data | data | data |
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
Display More
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!