Macro for copy and paste

[vidplaylist]

Every month i copy the formula to next column on many sheets. Each sheets has different number of rows. I tried a macro to copy and paste the formula on next column but every sheet has different numbers of total rows as a result it copy the formula on only number of columns which are part of recorded macro.

Can someone suggest a vba code that i can input the number of rows when i run macro on each sheet.

Sub formula1mrexcel()

' recorded macro
' copy formula to next column each month

Range("F3:F50").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-42
Range("G3").Select
ActiveSheet.Paste
Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub



Thanks

 

[VoG]

Try

Sub formula1mrexcel()

' recorded macro
' copy formula to next column each month
Dim LR As Long, LC As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
Range(Cells(3, LC), Cells(LR, LC)).Copy Destination:=Cells(3, LC + 1)
Cells(3, LC).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub