| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
re : Copying a range from WB01 and pasting to WB02
macros as below :-
Private Sub MODA() Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic Set WB1 = Workbooks("A HSA Daily Yield VPB") Set WB2 = Workbooks("A HSA Daily Scrap VPB") Set SH1 = WB1.Sheets("Date Input") Set SH2 = WB2.Sheets("Date Input") Set SH3 = WB1.Sheets("FI") Set SH4 = WB1.Sheets("FID") Set SH5 = WB2.Sheets("Modular") Set rng1 = SH1.Range("E13") Set rng2 = SH2.Range("D18") SH3.Activate ActiveSheet.Range(rng1 & "116").Copy SH5.Activate ActiveSheet.Range(rng2 & "3").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End Sub With the above, I am able to copy the WB1.range 116 and paste on WB2.range 3, I would to copy from WB1.range 116 to 120 at 1 go, and paste on WB2.range 3, is there any way or shorter script instead of me repeating the copy paste script 5 times to copy 116 to 120. Appreciate your help. Thanks. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
re : Copying a range from WB01 and pasting to WB02
You can put this thing in for loop, probably yo may need 2 for loops and then
increment the rows/column within one of the loop, and other loop to run it 5 times. Hope this makes sense -- "Who will guard the guards?" "ddiicc" wrote: macros as below :- Private Sub MODA() Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic Set WB1 = Workbooks("A HSA Daily Yield VPB") Set WB2 = Workbooks("A HSA Daily Scrap VPB") Set SH1 = WB1.Sheets("Date Input") Set SH2 = WB2.Sheets("Date Input") Set SH3 = WB1.Sheets("FI") Set SH4 = WB1.Sheets("FID") Set SH5 = WB2.Sheets("Modular") Set rng1 = SH1.Range("E13") Set rng2 = SH2.Range("D18") SH3.Activate ActiveSheet.Range(rng1 & "116").Copy SH5.Activate ActiveSheet.Range(rng2 & "3").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End Sub With the above, I am able to copy the WB1.range 116 and paste on WB2.range 3, I would to copy from WB1.range 116 to 120 at 1 go, and paste on WB2.range 3, is there any way or shorter script instead of me repeating the copy paste script 5 times to copy 116 to 120. Appreciate your help. Thanks. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
re : Copying a range from WB01 and pasting to WB02
Hmm, Loop...so how do I go about in writing the script?
I am just a beginner though, so do not know much on it. thanks. "Anand.V.V.N" wrote: You can put this thing in for loop, probably yo may need 2 for loops and then increment the rows/column within one of the loop, and other loop to run it 5 times. Hope this makes sense -- "Who will guard the guards?" "ddiicc" wrote: macros as below :- Private Sub MODA() Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic Set WB1 = Workbooks("A HSA Daily Yield VPB") Set WB2 = Workbooks("A HSA Daily Scrap VPB") Set SH1 = WB1.Sheets("Date Input") Set SH2 = WB2.Sheets("Date Input") Set SH3 = WB1.Sheets("FI") Set SH4 = WB1.Sheets("FID") Set SH5 = WB2.Sheets("Modular") Set rng1 = SH1.Range("E13") Set rng2 = SH2.Range("D18") SH3.Activate ActiveSheet.Range(rng1 & "116").Copy SH5.Activate ActiveSheet.Range(rng2 & "3").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End Sub With the above, I am able to copy the WB1.range 116 and paste on WB2.range 3, I would to copy from WB1.range 116 to 120 at 1 go, and paste on WB2.range 3, is there any way or shorter script instead of me repeating the copy paste script 5 times to copy 116 to 120. Appreciate your help. Thanks. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
re : Copying a range from WB01 and pasting to WB02
A for loop is simple loop,
which would look like this for i=0 to 10 for j=1 to 20 next j next i I will do one thing I'll try the code and try to post it. Anand -- "Who will guard the guards?" "ddiicc" wrote: Hmm, Loop...so how do I go about in writing the script? I am just a beginner though, so do not know much on it. thanks. "Anand.V.V.N" wrote: You can put this thing in for loop, probably yo may need 2 for loops and then increment the rows/column within one of the loop, and other loop to run it 5 times. Hope this makes sense -- "Who will guard the guards?" "ddiicc" wrote: macros as below :- Private Sub MODA() Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic Set WB1 = Workbooks("A HSA Daily Yield VPB") Set WB2 = Workbooks("A HSA Daily Scrap VPB") Set SH1 = WB1.Sheets("Date Input") Set SH2 = WB2.Sheets("Date Input") Set SH3 = WB1.Sheets("FI") Set SH4 = WB1.Sheets("FID") Set SH5 = WB2.Sheets("Modular") Set rng1 = SH1.Range("E13") Set rng2 = SH2.Range("D18") SH3.Activate ActiveSheet.Range(rng1 & "116").Copy SH5.Activate ActiveSheet.Range(rng2 & "3").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End Sub With the above, I am able to copy the WB1.range 116 and paste on WB2.range 3, I would to copy from WB1.range 116 to 120 at 1 go, and paste on WB2.range 3, is there any way or shorter script instead of me repeating the copy paste script 5 times to copy 116 to 120. Appreciate your help. Thanks. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
re : Copying a range from WB01 and pasting to WB02
Thanks alot, greatly appreciated your help .. and waiting promptly for the
codes.. thanks. "Anand.V.V.N" wrote: A for loop is simple loop, which would look like this for i=0 to 10 for j=1 to 20 next j next i I will do one thing I'll try the code and try to post it. Anand -- "Who will guard the guards?" "ddiicc" wrote: Hmm, Loop...so how do I go about in writing the script? I am just a beginner though, so do not know much on it. thanks. "Anand.V.V.N" wrote: You can put this thing in for loop, probably yo may need 2 for loops and then increment the rows/column within one of the loop, and other loop to run it 5 times. Hope this makes sense -- "Who will guard the guards?" "ddiicc" wrote: macros as below :- Private Sub MODA() Application.ScreenUpdating = False Application.Calculation = xlCalculationAutomatic Set WB1 = Workbooks("A HSA Daily Yield VPB") Set WB2 = Workbooks("A HSA Daily Scrap VPB") Set SH1 = WB1.Sheets("Date Input") Set SH2 = WB2.Sheets("Date Input") Set SH3 = WB1.Sheets("FI") Set SH4 = WB1.Sheets("FID") Set SH5 = WB2.Sheets("Modular") Set rng1 = SH1.Range("E13") Set rng2 = SH2.Range("D18") SH3.Activate ActiveSheet.Range(rng1 & "116").Copy SH5.Activate ActiveSheet.Range(rng2 & "3").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End Sub With the above, I am able to copy the WB1.range 116 and paste on WB2.range 3, I would to copy from WB1.range 116 to 120 at 1 go, and paste on WB2.range 3, is there any way or shorter script instead of me repeating the copy paste script 5 times to copy 116 to 120. Appreciate your help. Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copying formulas in range and pasting in a selection | Excel Worksheet Functions | |||
| Copying and pasting from a range | Excel Worksheet Functions | |||
| copying and pasting range problem | Excel Programming | |||
| Problem copying range and pasting to multiple sheets | Excel Programming | |||
| Finding a range for Copying & Pasting | Excel Programming | |||
Linear Mode
