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
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!