inserting a row on sheet 2...
Posted by Adam a.k.a. Yellow WIzard on August 22, 2001 5:42 AM
Ok what im after doing is taking a selection of cells from sheet 1 and pasting them into sheet 2 - easy though this is the problem is i am dealing with new data every week and i need to insert a new blank row to sheet 2 every time i copy from sheet 1 to sheet 2.
so if it were to be literal it would be:
copy selection on sheet 1
goto sheet 2 insert blank line
scroll down to where i need to paste data
increment by 1 line and paste in
is the incrementation mainly need otherwise the data that gets shifted down will become overwritten by the pasting into a given cell range.
hope im clear
thanks in advance
regards
adam
Posted by Nobby on August 22, 2001 6:33 AM
no youre not clear.
It might help if you were to try a bit of punctuation.
Also :
-Where do you want to insert the blank line on sheet 2 ?
-Where do you need to paste the data? On the next available cell?
If yes, the next available cell in what column?
I could take a guess at what the following means, but perhaps you could clarify - "is the incrementation mainly need otherwise the data that gets shifted down will become overwritten by the pasting into a given cell range."
Posted by Adam a.k.a. Yellow WIzard on August 22, 2001 7:55 AM
Hi sorry i was unclear.
ok the selection from sheet 1 is A1 to K41
from here i need to paste it into sheet 2 at position A83, but each time it is to be pasted, i need to insert a row at row: A12
so when i now paste into sheet to all rows have moved down by 1 position.
what i need is a command to say: insert a row
then: paste selection into sheet to at position A83 + 1
next time it would be +2 and so on
ok hope that was a little better :-)
email me if you need to yellow_wizard@hotmail.com
adam
Posted by Nobby on August 22, 2001 5:25 PM
Try this :-
Dim rw As Integer
rw = Worksheets("Sheet2").Range("A65536").End(xlUp).Row - 40
Worksheets("Sheet1").Range("A1:K41").Copy Worksheets("Sheet2").Cells(rw, 1)
Worksheets("Sheet2").Rows("12").Insert
The above assumes that there is never any data below the data posted to sheet2 and that A41 on sheet1 always contains data.
Post again if these assumptions are incorrect.
Hi sorry i was unclear. ok the selection from sheet 1 is A1 to K41
Posted by Ivan F Moala on August 22, 2001 9:09 PM
If your data cells is going beyond 32768 then
Declare rw as Double
Ivan
Posted by Jack on August 22, 2001 9:49 PM
Very true, but since Yellow Wizard said that he wants to start at row 83 and once a week thereafter increase the paste row by one, it would be approximately 627 years before he would arrive at row 32768 !!
If your data cells is going beyond 32768 then Declare rw as Double Ivan : Try this :- : Dim rw As Integer : rw = Worksheets("Sheet2").Range("A65536").End(xlUp).Row - 40 : Worksheets("Sheet1").Range("A1:K41").Copy Worksheets("Sheet2").Cells(rw, 1) : Worksheets("Sheet2").Rows("12").Insert : The above assumes that there is never any data below the data posted to sheet2 and that A41 on sheet1 always contains data. : Post again if these assumptions are incorrect.
Posted by Adam a.k.a. Yellow Wizard on August 23, 2001 2:09 AM
LOL indeed although i hope to have long left the company before that :0)
Ad Very true, but since Yellow Wizard said that he wants to start at row 83 and once a week thereafter increase the paste row by one, it would be approximately 627 years before he would arrive at row 32768 !! : : If your data cells is going beyond 32768 then : Declare rw as Double : : Ivan :
Posted by Ivan F Moala on August 23, 2001 3:00 AM
Should really read further ;-), you are correct
Thanks
Ivan Very true, but since Yellow Wizard said that he wants to start at row 83 and once a week thereafter increase the paste row by one, it would be approximately 627 years before he would arrive at row 32768 !! : : If your data cells is going beyond 32768 then : Declare rw as Double : : Ivan :
