VBA Use dynamic variables to Center Across Selection-CodePudding

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.

CodePudding user response：

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