Looping Macro

[Neil]

I am trying to write a macro that copies data (starting at column B(starting
at row 3), and going to end right) from one worksheet (Register) to another
(Sheet 1), and then repeats this on the data from the next row down on
worksheet Register until all data has been copied

I've been successful with the actual copying but have come unstuck on the
loop

Any help gratefully received

 

[Don Guillett]

before/after example(s)

 

[Neil]

Hello Don

The data in worksheet 'Register' is document number (column B) and
associated document number(s) in columns C........to however many

I need to extract this data one row at a time and copy it to worksheet
'Sheet1' where I copy paste special transpose

I have cracked the copy paste special transpose part but have come unstuck
getting it to repeat one row at a time

The contents of worksheet 'Sheet1' is only intended to be temporary as it
will be created for a look up operation from a different worksheet as and
when needed

 

[Don Guillett]

Sub copytransposecolstorows()
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row

With Sheets("sheet1") 'destination sheet
lc = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 1 To lc
dlr = .Cells(Rows.Count, "a").End(xlUp).Row + 1
Cells(i, j).Copy .Cells(dlr, "a")
Next j
End With

Next i
End Sub

 

[Neil]

Thanks Don worked a treat
--
Neil

Sub copytransposecolstorows()
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row

With Sheets("sheet1") 'destination sheet
lc = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 1 To lc
dlr = .Cells(Rows.Count, "a").End(xlUp).Row + 1
Cells(i, j).Copy .Cells(dlr, "a")
Next j
End With

Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

Glad to help
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Thanks Don worked a treat