Variable in Range for excel
 

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

Variable in Range for excel
 

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

"James C." wrote in message
...
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

Variable in Range for excel
 

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

"Don Guillett" wrote:

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

"James C." wrote in message
...
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