Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i set up a user prompt at the start of an excel workbook ? | Excel Worksheet Functions | |||
Prompt for start row | Excel Programming | |||
start up prompt for updating links | Excel Discussion (Misc queries) | |||
start learning Excel/VBA, where to start from? Any online video lectures? | Excel Programming | |||
OT :Start your own online business today !start making dollars | Excel Discussion (Misc queries) |