Prompt for Start Row

[JenL]

I have the below code set up which is working well.

What I need to do is prompt the user for the starting row for the copy/paste
range. For instance, this week the copy range may be C71309 and next week
the range may be C78309. The only thing that will change is the beginning
row. How do I do that?

THANKS!

Sub CopyCashFlowLAO()

Dim RngToCopy As Range
Dim DestCell As Range


With Workbooks("LAO Cash Flow Input
Template-ARG.xls").Worksheets("Argentina ARS")
Set RngToCopy = .Range("C71309")
End With

With ActiveWorkbook.Worksheets("Argentina ARS")
Set DestCell = .Range("c71")
End With

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

With Workbooks("LAO Cash Flow Input
Template-ARG.xls").Worksheets("Argentina ARS")
Set RngToCopy = .Range("G71:G309")
End With

With ActiveWorkbook.Worksheets("Argentina ARS")
Set DestCell = .Range("G71")
End With

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

With Workbooks("LAO Cash Flow Input
Template-ARG.xls").Worksheets("Argentina ARS")
Set RngToCopy = .Range("I71:I309")
End With

With ActiveWorkbook.Worksheets("Argentina ARS")
Set DestCell = .Range("I71")
End With

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

End Sub

 

[Gary''s Student]

use:

n = Application.InputBox(prompt:="enter starting row", Type:=1)
Set RngToCopy = .Range("C" & n & "309")

in place of your Set statement

 

[JenL]

Thanks a bunch Gary!!! It worked great!!

use:

n = Application.InputBox(prompt:="enter starting row", Type:=1)
Set RngToCopy = .Range("C" & n & "309")

in place of your Set statement