VBA Use dynamic variables to Center Across Selection

Question

I would appreciate some guidance on how to center across selection by referencing cell values in worksheet "Data" whilst copy/pasting another value from the same worksheet.

Column A	Column B	Column C
January-22	F1	I1
February-22	J1	M1
March-22	N1	R1
April-22	S1	V1

I would like to copy the values from column A, paste them to the cell referenced in column B (in Worksheet "Overview"), and center across the selection referenced in columns B and C

Sub months()
Dim i As Integer
Dim Cval As Variant
Dim Rng1 As Range

i = 1
Cval = Worksheets("Data").Range("B" & i).Value
Set Rng1 = Worksheets("Overview").Range(Cval)

With Worksheets("Data").Range("A" & i).Copy
Worksheets("Overview").Activate
Rng1.Select
ActiveSheet.Paste
Range("F1:I1").Select
Application.CutCopyMode = False
End With

With Selection
    .HorizontalAlignment = xlCenterAcrossSelection
    .MergeCells = False
End With
End Sub

Thank you in advance for anybody that can help.

I would advise [How To Avoid Using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) as an excellent starting point, allowing you to change lines like `Range("F1").Select` to reference the values in Column B, etc. (Also, the documentation [for `For .. Next` loops](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/fornext-statement)) — Chronocidal, Oct 03 '21 at 20:01
Thanks. The reference selection is the part I am more comfortable with (see revised code above), the part where I am stuck is referencing the center across selection range — scott_od, Oct 03 '21 at 20:57

score 1 · Accepted Answer · answered Oct 04 '21 at 01:16

As Chronocidal implies, your code could be improved in many ways. But, based on your response to that, I'm reading you're very much at the beginning stage of learning vb and prefer to stay with what know and build out on that for now.

Given this, I've done minimal change to your code, and focused on just getting it to do just the thing you asked. I did remove an unused 'With' structure. And I fixed a bug. Both are noted in the code comments.

Sub months()
    Dim i As Integer
    Dim Cval As Variant, Cval2 ' Added 2nd variant var (note: Variants don't require 'as Type'
    Dim Rng1 As Range

    i = 1
    Cval = Worksheets("Data").Range("B" & i).Value
    Cval2 = Worksheets("Data").Range("C" & i).Value ' Added getting the 2nd address part
    
    Set Rng1 = Worksheets("Overview").Range(Cval)
    
    Worksheets("Data").Range("A" & i).Copy ' Removed With (as no properties there were referenced)
    Worksheets("Overview").Activate
    Rng1.Select
    ActiveSheet.Paste
    ActiveSheet.Range("F1:I1").Select ' Ranges need a parent context
    
    With ActiveSheet.Range(Cval, Cval2) ' This now does what you want
        .MergeCells = False ' This is unnecessary unless the cells were merged before you started
        .HorizontalAlignment = xlCenterAcrossSelection
    End With

End Sub

thanks for your efforts to explain that was very clear & helpful. One additional question is how can I use `PasteSpecial Paste:=xlPasteValues` instead of `ActiveSheet.Paste` in the above example — scott_od, Oct 04 '21 at 09:28
So: PasteSpecial is useful for various specific outcomes (e.g. transferring only formulas or formats). You can do back-to-backs (e.g. formulas, then formats). Also, it's based on a Range reference. So you can paste, without having to previously 'select'. That all said you should also look at 'RangeX = RangeY' which 'enters' the value at Y from the value at X. No copy, no paste and no change of 'selection'. — Spinner, Oct 04 '21 at 09:50

VBA Use dynamic variables to Center Across Selection

1 Answers1