VBA to copy block of cells - keeping correct cell reference

[silentcarl]

Hi all,

Super novice here trying to get something done for my wife and a non-profit she is part of. I hope someone can help save me some time as I have been at this for hours.

I have a set of raw data on one page that is organized by row. (a2, a3, a4...)

I have a separate page with name tags that uses the data and spreads it across 6 rows using formulas. (A3:D8) I need this copied down to A9, then A15, etc.

I have a (nearly) working program that copies the block down, but the automatic cell reference is moving by as many cells as I am copying down. IE cell A2 becomes A8 in the formula because it is copied down the sheet 6 spaces. So I am only getting every 6th data set.

Is there a way to modify what I have to get this, or do I need to approach a different way?

Sub UpdatePrint()
Dim UsedRows As Integer

UsedRows = Worksheets("RawData").UsedRange.Rows.Count

Dim i As Long
For i = 3 To UsedRows Step 6

    Rows(i).RowHeight = 21
    Range("a3:D8").Copy
    With Cells(i, 1)
    .PasteSpecial xlPasteFormulas
    .PasteSpecial xlPasteFormats
    End With
    
    With Range("b3:d3")

    .Merge
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter

End With
    
    Rows(i + 1 & ":" & i + 5).RowHeight = 15
    
Next i


End Sub

Thanks in advance for any help!

 

[Joe4]

I think it might be much more helpful to us to show us an image of your original data, and your expected output from that data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[silentcarl]

Sub UpdatePrint()
Dim UsedRows As Integer

UsedRows = Worksheets("RawData").UsedRange.Rows.Count

Dim i As Long
For i = 3 To UsedRows Step 6
    
    Range("a3:D8").Copy
    With Cells(i, 1)
    .PasteSpecial xlPasteFormulas
    .PasteSpecial xlPasteFormats
    End With
    
Next i

End Sub

I got rid of all the extra "stuff" to simplify.


This works, and copies the range down every 6 rows.

However, the referenced cell in the formula changes by 6 as expected. I need it to only increase by 1. (since raw data is all on the same row).


I have to get rid of personal data before I could upload any real data.

=CONCATENATE(RawData!A2," ",RawData!B2)

^is the formula in B3 that then becomes:

=CONCATENATE(RawData!A8," ",RawData!B8)

Thanks!

 

[Joe4]

Then you will not want to copy the existing formula, but actually construct the formula in VBA.
(Unless you rewrite your formula to behave differently, i.e. use something like OFFSET instead of hard-coded cell references).

 

[silentcarl]

Is it acceptable to ask the following question now or do I need to start a new thread?

=CONCATENATE(RawData!A2," ",RawData!B2)

How then do I write this formula into my macro where it will change in each instance by (a2+n,B2+n) (n is the number of instances in my with loop)?

Thanks!

 

[Joe4]

Here is a little example to show you how you could do that (create the needed formula in VBA instead of copying):

Sub UpdatePrint()

Dim UsedRows As Integer
Dim i As Long
Dim c As Long

'Set initial value for loop counter
c = 3

UsedRows = Worksheets("RawData").UsedRange.Rows.Count

For i = 2 To UsedRows Step 6
    Range("B" & c).Formula = "=CONCATENATE(RawData!A" & i & ","" "",RawData!B" & i & ")"
'   Increment loop counter
    c = c + 1
Next i

End Sub