Variable in Range for excel

[James C.]

Can someone help

Hi,

I am trying to make my workbook more dynamic. My code is pretty simple, copy
formula's from one column to the next. Each month the user will run the code.
The problem is that the columns are not static and will move over one column
each month. My solution was to have the user input a value into cell C1 and
C2 for the column to be copied and the column to be copied to.

Here is my code. How do I insert the variable's into the range? I know I
need to remove the "H" in the range and put in Var1/Var2...just not sure how
to do this.

Sub NextMonth()

Dim Var1 As String
Dim Var2 As String

Var1 = Sheets("Summary").Cells(1, 3)
Var2 = Sheets("Summary").Cells(2, 3)

Range("H5:H7").Select
Selection.AutoFill Destination:=Range("H5:I7"), Type:=xlFillDefault
Range("H5:I7").Select

End Sub

 

[Don Guillett]

This finds the last column in row 5 and copies formulas on row 5:7 to the
next column

Sub FINDLASTCOLSAS()
lc = Cells(5, Columns.Count).End(xlToLeft).Column
Cells(5, lc).Resize(3).Copy Cells(5, lc + 1)
End Sub

 

[James C.]

Perfect.. that is even better. Thanks so much!

This finds the last column in row 5 and copies formulas on row 5:7 to the
next column

Sub FINDLASTCOLSAS()
lc = Cells(5, Columns.Count).End(xlToLeft).Column
Cells(5, lc).Resize(3).Copy Cells(5, lc + 1)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)