Prompt for Start Row
use:
n = Application.InputBox(prompt:="enter starting row", Type:=1)
Set RngToCopy = .Range("C" & n & ":D309")
in place of your Set statement
--
Gary''s Student - gsnu200832
"JenL" wrote:
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 C71:D309 and next week
the range may be C78:D309. 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("C71:D309")
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
|