Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Range & Row
Hi All
I have the following code - which does the job OK for reading the 1st row and range on sheet "Print1" and puts all the data in the correct place on sheet "Card" how would I code the button Add1 after doing the first row to move and read the ranges on the next row and insert the data from that row to overwrite what is already in the sheet"Card" Private Sub Add1_Click() Worksheets("Card").Range("A5:I5").Value = UserForm1.Cmd1 Worksheets("Card").Range("D9").Value = UserForm1.Cmd2 Worksheets("Card").Range("E13").Value = Worksheets("Print1").Range("L7") Worksheets("Card").Range("B7").Value = Worksheets("Print1").Range("A7") Worksheets("Card").Range("G9").Value = Worksheets("Print1").Range("D21") Worksheets("Card").Range("D3").Value = Worksheets("Print1").Range("K7") Worksheets("Card").Range("E7").Value = Worksheets("Print1").Range("D22") Worksheets("Card").Range("E11").Value = Worksheets("Print1").Range("E7") Worksheets("Card").Range("F11").Value = Worksheets("Print1").Range("F7") End Sub The range on the sheet"Card" would always remain the same --- I have about 60 rows to move down. -- Many thanks hazel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Range & Row
your code doesn't address a single row in either Print1 or Card.
So it is hard to understand what you consider the next row to be. A8? the what about the items on row 22 and 21? do you want the button to look at a succeeding location each time it is pressed and move that data into Card? If so, what value on Card would be the unique identifier which could be used to identify the last written row by looking at Print1. How far down should it go. Where should it stop. -- Regards, Tom Ogilvy "Hazel" wrote in message ... Hi All I have the following code - which does the job OK for reading the 1st row and range on sheet "Print1" and puts all the data in the correct place on sheet "Card" how would I code the button Add1 after doing the first row to move and read the ranges on the next row and insert the data from that row to overwrite what is already in the sheet"Card" Private Sub Add1_Click() Worksheets("Card").Range("A5:I5").Value = UserForm1.Cmd1 Worksheets("Card").Range("D9").Value = UserForm1.Cmd2 Worksheets("Card").Range("E13").Value = Worksheets("Print1").Range("L7") Worksheets("Card").Range("B7").Value = Worksheets("Print1").Range("A7") Worksheets("Card").Range("G9").Value = Worksheets("Print1").Range("D21") Worksheets("Card").Range("D3").Value = Worksheets("Print1").Range("K7") Worksheets("Card").Range("E7").Value = Worksheets("Print1").Range("D22") Worksheets("Card").Range("E11").Value = Worksheets("Print1").Range("E7") Worksheets("Card").Range("F11").Value = Worksheets("Print1").Range("F7") End Sub The range on the sheet"Card" would always remain the same --- I have about 60 rows to move down. -- Many thanks hazel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Range & Row
Hi Tom
Thanks for quick response -- I'm new to all this, however if this helps the start Row on sheet "Print1" is 7 and goes down to Row 67 the columns covered by the data are "A:L" the sheet "Card" is actually a Pass Card to enter the building so the data from all the Rows 7:67 always goes in the same Cells on the sheet "Card". The Rows on sheet "Card" start on Row 3 and end on Row 14. Does this explain it better? -- Many thanks hazel "Tom Ogilvy" wrote: your code doesn't address a single row in either Print1 or Card. So it is hard to understand what you consider the next row to be. A8? the what about the items on row 22 and 21? do you want the button to look at a succeeding location each time it is pressed and move that data into Card? If so, what value on Card would be the unique identifier which could be used to identify the last written row by looking at Print1. How far down should it go. Where should it stop. -- Regards, Tom Ogilvy "Hazel" wrote in message ... Hi All I have the following code - which does the job OK for reading the 1st row and range on sheet "Print1" and puts all the data in the correct place on sheet "Card" how would I code the button Add1 after doing the first row to move and read the ranges on the next row and insert the data from that row to overwrite what is already in the sheet"Card" Private Sub Add1_Click() Worksheets("Card").Range("A5:I5").Value = UserForm1.Cmd1 Worksheets("Card").Range("D9").Value = UserForm1.Cmd2 Worksheets("Card").Range("E13").Value = Worksheets("Print1").Range("L7") Worksheets("Card").Range("B7").Value = Worksheets("Print1").Range("A7") Worksheets("Card").Range("G9").Value = Worksheets("Print1").Range("D21") Worksheets("Card").Range("D3").Value = Worksheets("Print1").Range("K7") Worksheets("Card").Range("E7").Value = Worksheets("Print1").Range("D22") Worksheets("Card").Range("E11").Value = Worksheets("Print1").Range("E7") Worksheets("Card").Range("F11").Value = Worksheets("Print1").Range("F7") End Sub The range on the sheet"Card" would always remain the same --- I have about 60 rows to move down. -- Many thanks hazel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can change range to select active rows instead of :=Range("S10 | Excel Discussion (Misc queries) | |||
How do I change a range name back to the underlying data range? | Excel Worksheet Functions | |||
How can I dynamically change the range for Range("A1:M500")? | Excel Programming | |||
Change cell colour for a cell or range within a predefined range | Excel Programming | |||
how to change range in VBA? | Excel Programming |