Prompt for Start Row
 

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

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

Prompt for Start Row
 

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

"Gary''s Student" wrote:

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