| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax Help: Range variables (plucking data and pasting to 2nd sheet)
I think the code is straightforward in its intent.
I just want to pluck a cell value from sheet 1 to sheet 2 Then a range to sheet 2 then another range to sheet 2 How can this be done? Thanks in advance. Dim srcRowNum As Integer Dim dstRowNum As Integer srcRowNum = 19 dstRowNum = 2 Do Until rownum 600 'ITEM CODE Range(Cells(srcRowNum + 1, 1)).Copy Worksheets(1).Range(Cells(srcRowNum + 1, 1)).Copy Worksheets(2).Range(Cells(dstRowNum, 1)).Paste 'WEEKLY Worksheets(1).Range(Cells(srcRowNum, 4), Cells(srcRowNum, 16)).Copy Worksheets(2).Range(Cells(dstRowNum, 2)).Paste srcRowNum = srcRowNum + 6 'MONTHLY Worksheets(1).Range(Cells(srcRowNum, 4), Cells(srcRowNum, 16)).Copy Worksheets(2).Range(Cells(dstRowNum, 17)).Paste srcRowNum = srcRowNum + 8 dstRowNum = dstRowNum + 1 Loop |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Syntax Help: Range variables (plucking data and pasting to 2nd sheet)
Anyone know?
Finny wrote: I think the code is straightforward in its intent. I just want to pluck a cell value from sheet 1 to sheet 2 Then a range to sheet 2 then another range to sheet 2 How can this be done? Thanks in advance. Dim srcRowNum As Integer Dim dstRowNum As Integer srcRowNum = 19 dstRowNum = 2 Do Until rownum 600 'ITEM CODE Range(Cells(srcRowNum + 1, 1)).Copy Worksheets(1).Range(Cells(srcRowNum + 1, 1)).Copy Worksheets(2).Range(Cells(dstRowNum, 1)).Paste 'WEEKLY Worksheets(1).Range(Cells(srcRowNum, 4), Cells(srcRowNum, 16)).Copy Worksheets(2).Range(Cells(dstRowNum, 2)).Paste srcRowNum = srcRowNum + 6 'MONTHLY Worksheets(1).Range(Cells(srcRowNum, 4), Cells(srcRowNum, 16)).Copy Worksheets(2).Range(Cells(dstRowNum, 17)).Paste srcRowNum = srcRowNum + 8 dstRowNum = dstRowNum + 1 Loop |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Syntax Help: Range variables (plucking data and pasting to 2nd sheet)
I can't see what's wrong with it.
:( Finny wrote: Anyone know? Finny wrote: I think the code is straightforward in its intent. I just want to pluck a cell value from sheet 1 to sheet 2 Then a range to sheet 2 then another range to sheet 2 How can this be done? Thanks in advance. Dim srcRowNum As Integer Dim dstRowNum As Integer srcRowNum = 19 dstRowNum = 2 Do Until rownum 600 'ITEM CODE Range(Cells(srcRowNum + 1, 1)).Copy Worksheets(1).Range(Cells(srcRowNum + 1, 1)).Copy Worksheets(2).Range(Cells(dstRowNum, 1)).Paste 'WEEKLY Worksheets(1).Range(Cells(srcRowNum, 4), Cells(srcRowNum, 16)).Copy Worksheets(2).Range(Cells(dstRowNum, 2)).Paste srcRowNum = srcRowNum + 6 'MONTHLY Worksheets(1).Range(Cells(srcRowNum, 4), Cells(srcRowNum, 16)).Copy Worksheets(2).Range(Cells(dstRowNum, 17)).Paste srcRowNum = srcRowNum + 8 dstRowNum = dstRowNum + 1 Loop |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Syntax Help: Range variables (plucking data and pasting to 2nd sheet)
The error is
Run-time 1004 Application-defined or object-defined error Finny wrote: I can't see what's wrong with it. :( Finny wrote: Anyone know? Finny wrote: I think the code is straightforward in its intent. I just want to pluck a cell value from sheet 1 to sheet 2 Then a range to sheet 2 then another range to sheet 2 How can this be done? Thanks in advance. Dim srcRowNum As Integer Dim dstRowNum As Integer srcRowNum = 19 dstRowNum = 2 Do Until rownum 600 'ITEM CODE Range(Cells(srcRowNum + 1, 1)).Copy Worksheets(1).Range(Cells(srcRowNum + 1, 1)).Copy Worksheets(2).Range(Cells(dstRowNum, 1)).Paste 'WEEKLY Worksheets(1).Range(Cells(srcRowNum, 4), Cells(srcRowNum, 16)).Copy Worksheets(2).Range(Cells(dstRowNum, 2)).Paste srcRowNum = srcRowNum + 6 'MONTHLY Worksheets(1).Range(Cells(srcRowNum, 4), Cells(srcRowNum, 16)).Copy Worksheets(2).Range(Cells(dstRowNum, 17)).Paste srcRowNum = srcRowNum + 8 dstRowNum = dstRowNum + 1 Loop |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Syntax Help: Range variables (plucking data and pasting to 2ndsheet)
You have unqualified ranges in your code.
worksheets(1).range(cells(.... the .range portion is qualified with worksheets(1). But Cells() isn't qualified by anything--and if this code is in a general module, that unqualified range will refer to the activesheet. So at leaste one of these will blow up: worksheets(1).range(cells(... or worksheets(2).range(cells(... Since they both can't be active. In your case, it looks like you don't need .range() in all your statements. Dim srcRowNum As Integer Dim dstRowNum As Integer srcRowNum = 19 dstRowNum = 2 Do Until rownum 600 'ITEM CODE 'you have two .copy statements. The first isn't used! 'Cells(srcRowNum + 1, 1).Copy Worksheets(1).Cells(srcRowNum + 1, 1).Copy Worksheets(2).Cells(dstRowNum, 1).Paste 'but you'll want to qualify the range he 'WEEKLY with worksheets(1) .range(.Cells(srcRowNum, 4), .Cells(srcRowNum, 16)).Copy end with Worksheets(2).Cells(dstRowNum, 2).Paste srcRowNum = srcRowNum + 6 'and again here 'MONTHLY with Worksheets(1) .Range(.Cells(srcRowNum, 4), .Cells(srcRowNum, 16)).Copy end with Worksheets(2).Cells(dstRowNum, 17).Paste srcRowNum = srcRowNum + 8 dstRowNum = dstRowNum + 1 Loop Note the dots in front of the .cells() portion. That means that that range (.cells(srcRownum,4) belongs to the object in the previous With statement--in this case worksheets(1). I did change the logic of your code, though. This line: Worksheets(2).Range(Cells(dstRowNum, 17)).Paste Says to pick up the value from cells(dstRowNum,17) from the activesheet. It would have to look like an address (A17, X99) and then you'd be left with worksheets(2).range("x99").paste But I didn't think that's what you really wanted to do. Finny wrote: I think the code is straightforward in its intent. I just want to pluck a cell value from sheet 1 to sheet 2 Then a range to sheet 2 then another range to sheet 2 How can this be done? Thanks in advance. Dim srcRowNum As Integer Dim dstRowNum As Integer srcRowNum = 19 dstRowNum = 2 Do Until rownum 600 'ITEM CODE Range(Cells(srcRowNum + 1, 1)).Copy Worksheets(1).Range(Cells(srcRowNum + 1, 1)).Copy Worksheets(2).Range(Cells(dstRowNum, 1)).Paste 'WEEKLY Worksheets(1).Range(Cells(srcRowNum, 4), Cells(srcRowNum, 16)).Copy Worksheets(2).Range(Cells(dstRowNum, 2)).Paste srcRowNum = srcRowNum + 6 'MONTHLY Worksheets(1).Range(Cells(srcRowNum, 4), Cells(srcRowNum, 16)).Copy Worksheets(2).Range(Cells(dstRowNum, 17)).Paste srcRowNum = srcRowNum + 8 dstRowNum = dstRowNum + 1 Loop -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Syntax Help: Range variables (plucking data and pasting to 2nd sheet)
Thanks Dave
I understand that error now. Qualifying the cells, got it. That first copy was left in by mistake from when I was testing. I don't quite understand what you mean in terms of pasting. Are you saying that my paste statements will paste the range referenced in that same line to whatever cell is active? and ignore the clipboard? To be clear I'm trying to copy range in sheet 1 to sheet 2 (src to dst) .... I tried pastespecial instead of paste and it worked fine. wierd. Dave Peterson wrote: You have unqualified ranges in your code. worksheets(1).range(cells(.... the .range portion is qualified with worksheets(1). But Cells() isn't qualified by anything--and if this code is in a general module, that unqualified range will refer to the activesheet. So at leaste one of these will blow up: worksheets(1).range(cells(... or worksheets(2).range(cells(... Since they both can't be active. In your case, it looks like you don't need .range() in all your statements. Dim srcRowNum As Integer Dim dstRowNum As Integer srcRowNum = 19 dstRowNum = 2 Do Until rownum 600 'ITEM CODE 'you have two .copy statements. The first isn't used! 'Cells(srcRowNum + 1, 1).Copy Worksheets(1).Cells(srcRowNum + 1, 1).Copy Worksheets(2).Cells(dstRowNum, 1).Paste 'but you'll want to qualify the range he 'WEEKLY with worksheets(1) .range(.Cells(srcRowNum, 4), .Cells(srcRowNum, 16)).Copy end with Worksheets(2).Cells(dstRowNum, 2).Paste srcRowNum = srcRowNum + 6 'and again here 'MONTHLY with Worksheets(1) .Range(.Cells(srcRowNum, 4), .Cells(srcRowNum, 16)).Copy end with Worksheets(2).Cells(dstRowNum, 17).Paste srcRowNum = srcRowNum + 8 dstRowNum = dstRowNum + 1 Loop Note the dots in front of the .cells() portion. That means that that range (.cells(srcRownum,4) belongs to the object in the previous With statement--in this case worksheets(1). I did change the logic of your code, though. This line: Worksheets(2).Range(Cells(dstRowNum, 17)).Paste Says to pick up the value from cells(dstRowNum,17) from the activesheet. It would have to look like an address (A17, X99) and then you'd be left with worksheets(2).range("x99").paste But I didn't think that's what you really wanted to do. Finny wrote: I think the code is straightforward in its intent. I just want to pluck a cell value from sheet 1 to sheet 2 Then a range to sheet 2 then another range to sheet 2 How can this be done? Thanks in advance. Dim srcRowNum As Integer Dim dstRowNum As Integer srcRowNum = 19 dstRowNum = 2 Do Until rownum 600 'ITEM CODE Range(Cells(srcRowNum + 1, 1)).Copy Worksheets(1).Range(Cells(srcRowNum + 1, 1)).Copy Worksheets(2).Range(Cells(dstRowNum, 1)).Paste 'WEEKLY Worksheets(1).Range(Cells(srcRowNum, 4), Cells(srcRowNum, 16)).Copy Worksheets(2).Range(Cells(dstRowNum, 2)).Paste srcRowNum = srcRowNum + 6 'MONTHLY Worksheets(1).Range(Cells(srcRowNum, 4), Cells(srcRowNum, 16)).Copy Worksheets(2).Range(Cells(dstRowNum, 17)).Paste srcRowNum = srcRowNum + 8 dstRowNum = dstRowNum + 1 Loop -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Syntax Help: Range variables (plucking data and pasting to 2ndsheet)
I'm saying that range(cells(1,1)) won't refer to A1 unless the value in
cells(1,1) is the string "A1". This syntax is the same as range(cells(1,1).value). Either use just Range("a1") or cells(1,1)--and you want the cells() version. And I did miss the .paste vs .pastespecial values stuff. Sorry. Finny wrote: Thanks Dave I understand that error now. Qualifying the cells, got it. That first copy was left in by mistake from when I was testing. I don't quite understand what you mean in terms of pasting. Are you saying that my paste statements will paste the range referenced in that same line to whatever cell is active? and ignore the clipboard? To be clear I'm trying to copy range in sheet 1 to sheet 2 (src to dst) ... I tried pastespecial instead of paste and it worked fine. wierd. Dave Peterson wrote: You have unqualified ranges in your code. worksheets(1).range(cells(.... the .range portion is qualified with worksheets(1). But Cells() isn't qualified by anything--and if this code is in a general module, that unqualified range will refer to the activesheet. So at leaste one of these will blow up: worksheets(1).range(cells(... or worksheets(2).range(cells(... Since they both can't be active. In your case, it looks like you don't need .range() in all your statements. Dim srcRowNum As Integer Dim dstRowNum As Integer srcRowNum = 19 dstRowNum = 2 Do Until rownum 600 'ITEM CODE 'you have two .copy statements. The first isn't used! 'Cells(srcRowNum + 1, 1).Copy Worksheets(1).Cells(srcRowNum + 1, 1).Copy Worksheets(2).Cells(dstRowNum, 1).Paste 'but you'll want to qualify the range he 'WEEKLY with worksheets(1) .range(.Cells(srcRowNum, 4), .Cells(srcRowNum, 16)).Copy end with Worksheets(2).Cells(dstRowNum, 2).Paste srcRowNum = srcRowNum + 6 'and again here 'MONTHLY with Worksheets(1) .Range(.Cells(srcRowNum, 4), .Cells(srcRowNum, 16)).Copy end with Worksheets(2).Cells(dstRowNum, 17).Paste srcRowNum = srcRowNum + 8 dstRowNum = dstRowNum + 1 Loop Note the dots in front of the .cells() portion. That means that that range (.cells(srcRownum,4) belongs to the object in the previous With statement--in this case worksheets(1). I did change the logic of your code, though. This line: Worksheets(2).Range(Cells(dstRowNum, 17)).Paste Says to pick up the value from cells(dstRowNum,17) from the activesheet. It would have to look like an address (A17, X99) and then you'd be left with worksheets(2).range("x99").paste But I didn't think that's what you really wanted to do. Finny wrote: I think the code is straightforward in its intent. I just want to pluck a cell value from sheet 1 to sheet 2 Then a range to sheet 2 then another range to sheet 2 How can this be done? Thanks in advance. Dim srcRowNum As Integer Dim dstRowNum As Integer srcRowNum = 19 dstRowNum = 2 Do Until rownum 600 'ITEM CODE Range(Cells(srcRowNum + 1, 1)).Copy Worksheets(1).Range(Cells(srcRowNum + 1, 1)).Copy Worksheets(2).Range(Cells(dstRowNum, 1)).Paste 'WEEKLY Worksheets(1).Range(Cells(srcRowNum, 4), Cells(srcRowNum, 16)).Copy Worksheets(2).Range(Cells(dstRowNum, 2)).Paste srcRowNum = srcRowNum + 6 'MONTHLY Worksheets(1).Range(Cells(srcRowNum, 4), Cells(srcRowNum, 16)).Copy Worksheets(2).Range(Cells(dstRowNum, 17)).Paste srcRowNum = srcRowNum + 8 dstRowNum = dstRowNum + 1 Loop -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help! finding data and pasting it to another sheet! | Excel Programming | |||
| plucking data from one workbook to another | Excel Programming | |||
| VARIABLES IN ROWS - Help on The Right Syntax | Excel Programming | |||
| using variables in the range of source data | Excel Programming | |||
| Range.Select Using Variables - need syntax | Excel Discussion (Misc queries) | |||
![Finny[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
