Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making rows variable
Hi, Any help would be greatly appreciated as I am just getting grips with how all of this works. I have pieced together most of what I need through browsing other threads and Ron De Bruin help but I just can't get the last. I need the following code to work for instances where the rows increase by +1. So next time it loops through it picks up row(3:3) etc. Nothing I have tried so far works. Do Dim White As Range Dim Source As Range Dim Destination As Range Set White = (Sheets("Sales Invoice").Columns("L:L")) Set Source = (Sheets("Purchase").rows("2:2")) Source.Select Source.Copy ActiveCell.EntireRow.Offset(1, 0) Loop Until IsEmpty(White) Thanks. -- VBA_Learner ------------------------------------------------------------------------ VBA_Learner's Profile: http://www.excelforum.com/member.php...o&userid=25138 View this thread: http://www.excelforum.com/showthread...hreadid=386340 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making rows variable
I can't glean from your explanation or code what you're trying to do. Are you
trying to transfer data from consecutive columns in Sheets("Sales Invoice") starting with column "L" to consecutive rows in Sheets("Purchase") starting with row 2 - i.e. transpose the data from vertical orientation (columns) to horizontal orientation (rows) ??? Perhaps give us a simplified example describing how the data would appear at the start and how it would change after each iteration of the loop for a few iterations. Regards, Greg "VBA_Learner" wrote: Hi, Any help would be greatly appreciated as I am just getting grips with how all of this works. I have pieced together most of what I need through browsing other threads and Ron De Bruin help but I just can't get the last. I need the following code to work for instances where the rows increase by +1. So next time it loops through it picks up row(3:3) etc. Nothing I have tried so far works. Do Dim White As Range Dim Source As Range Dim Destination As Range Set White = (Sheets("Sales Invoice").Columns("L:L")) Set Source = (Sheets("Purchase").rows("2:2")) Source.Select Source.Copy ActiveCell.EntireRow.Offset(1, 0) Loop Until IsEmpty(White) Thanks. -- VBA_Learner ------------------------------------------------------------------------ VBA_Learner's Profile: http://www.excelforum.com/member.php...o&userid=25138 View this thread: http://www.excelforum.com/showthread...hreadid=386340 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making rows variable
Is this the sort of thing that you mean?
Dim White As Range Dim Source As Range Dim Destination As Range Dim iRow As Long Do iRow = iRow + 1 Set White = Worksheets("Sales Invoice").cells(iRow,"L") Set Source = Worksheets("Purchase").Rows(iRow) Source.Select Source.Copy ActiveCell.EntireRow.Offset(1, 0) Loop Until IsEmpty(White.Value) -- HTH RP (remove nothere from the email address if mailing direct) "VBA_Learner" wrote in message ... Hi, Any help would be greatly appreciated as I am just getting grips with how all of this works. I have pieced together most of what I need through browsing other threads and Ron De Bruin help but I just can't get the last. I need the following code to work for instances where the rows increase by +1. So next time it loops through it picks up row(3:3) etc. Nothing I have tried so far works. Do Dim White As Range Dim Source As Range Dim Destination As Range Set White = (Sheets("Sales Invoice").Columns("L:L")) Set Source = (Sheets("Purchase").rows("2:2")) Source.Select Source.Copy ActiveCell.EntireRow.Offset(1, 0) Loop Until IsEmpty(White) Thanks. -- VBA_Learner ------------------------------------------------------------------------ VBA_Learner's Profile: http://www.excelforum.com/member.php...o&userid=25138 View this thread: http://www.excelforum.com/showthread...hreadid=386340 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making rows variable
Greg, Just to clarify I am trying to copy an entire row which is on the sheets("purchase") down for as many times until there is a break in column L on the sheets("sales invoice"). Column L on the sheets("Sales Invoice") does not contain the data that I want to copy, that is on sheets("purchase"). The row that I am copying does have linked references to the "sales invoice" sheet, so I am using the column L as a guide. As once it is reached the break in Column L I need it to do something else. Thanks. -- VBA_Learner ------------------------------------------------------------------------ VBA_Learner's Profile: http://www.excelforum.com/member.php...o&userid=25138 View this thread: http://www.excelforum.com/showthread...hreadid=386340 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making rows variable
Bob, That looks like it might help but where do you tell it what Irow initially represents? Thanks. -- VBA_Learner ------------------------------------------------------------------------ VBA_Learner's Profile: http://www.excelforum.com/member.php...o&userid=25138 View this thread: http://www.excelforum.com/showthread...hreadid=386340 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making rows variable
Just set it before the Do line
iRow = 7 Do etc. -- HTH RP (remove nothere from the email address if mailing direct) "VBA_Learner" wrote in message ... Bob, That looks like it might help but where do you tell it what Irow initially represents? Thanks. -- VBA_Learner ------------------------------------------------------------------------ VBA_Learner's Profile: http://www.excelforum.com/member.php...o&userid=25138 View this thread: http://www.excelforum.com/showthread...hreadid=386340 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making rows variable
Thanks very much Bob that's great -- VBA_Learner ------------------------------------------------------------------------ VBA_Learner's Profile: http://www.excelforum.com/member.php...o&userid=25138 View this thread: http://www.excelforum.com/showthread...hreadid=386340 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
making a portion of reference to a worksheet variable in a formula | Excel Discussion (Misc queries) | |||
Making part of a formula a variable | Excel Worksheet Functions | |||
Making a Call to an external sheet using a variable... | Excel Worksheet Functions | |||
Making a file and worksheet reference into a variable.... | Excel Worksheet Functions | |||
Excel VBA -making Objectnames variable | Excel Programming |