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

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

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

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

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

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

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