Looping a column in workbook 1 to workbook 2's sheets

[L. Howard Kittle]

Hello Excel Experts and Users,

Excel 2002.

Workbook A has a list, A2 to A11, four columns wide.
Want to copy row 2 of A to Workbook B, sheet 1, "to a cell" and transpose
...copy row 3 of A to Workbook B, sheet 2 "to a cell" and
transpose
...copy row 4 of A to Workbook B, sheet 3 "to a cell" and
transpose
...etc until all ten rows have been copied and transposed to B.

Where "to a cell" will be an .end(xltoleft).offset(0,1)

I found this snippet in Google, Tom O. I believe, and tried to adapt it but
it ain't happenin'.

Dim WS as Worksheet
Set WS = Worksheets(1)
Do Until WS.Name = ("Sheet3)
'do stuff to the worksheets
Set WS = WS.next
Loop

I can get the first row of A to the first sheet in B but then fall on my
face in the code.
The code needs to loop thru the 10 rows of book A and thru the 10 sheets of
book B.

Thanks all,
Regards,
Howard

 

[Bernie Deitrick]

Dim myRow As Range
Dim iSht As Integer

iSht = 1
For Each myRow In Workbooks("A.xls"). _
Worksheets("Sheet1").Range("A211").Rows
myRow.Copy
Workbooks("B.xls").Worksheets("Sheet" & iSht). _
Range("IV2").End(xlToLeft).Offset(0, 1).PasteSpecial _
xlPasteValues, Transpose:=True
iSht = iSht + 1
Next myRow



Perhaps, just this instead - depends on your naming convention.

Workbooks("B.xls").Worksheets(iSht). _
Range("IV2").End(xlToLeft).Offset(0, 1).PasteSpecial _
xlPasteValues, Transpose:=True

HTH,
Bernie
MS Excel MVP

 

[L. Howard Kittle]

Thanks, Bernie. I'll give it a go.

Regards,
Howard

 

[L. Howard Kittle]

Hi Bernie,

Your code did exactly what I asked for in my description. Below it is
edited to reflect the actual names of the books and sheets and a correction
of an error in my data range. Works fine on my test bed where I have test
sheets numbered Sheet1 thru Sheet3, but I failed to take into consideration
that in the real destination workbook (A1RECONSTRUCT) the sheets are names
of counties. A list of these county names is A2:A89 in book "A1Source".

Sub Reconstruct_To_Source()
Dim myRow As Range
Dim iSht As Integer

iSht = 1
For Each myRow In Workbooks("A1Source.xls"). _
Worksheets("QURY4585").Range("B2:E4").Rows
myRow.Copy
Workbooks("A1RECONSTRUCT.xls").Worksheets("Sheet" & iSht). _
Range("IV5").End(xlToLeft).Offset(0, 1).PasteSpecial _
xlPasteValues, Transpose:=True
iSht = iSht + 1
Next myRow

End Sub

I tried to incorporate this into the code but no go.

Dim iSht as worksheet
Set iSht = Worksheets("Adams")
Do Until iSht.Name = ("Ashland")
Set iSht = iSht.Next
Loop

Perhaps a Do Until Loop would work if set up properly, or is there a way to
use the county list on the 1st sheet (A1Source) to increment through the
sheets.

Workbooks("A1RECONSTRUCT.xls").Worksheets( A1Source county list one by
one)._
Range("IV5").End(xlToLeft).Offset(0, 1).PasteSpecial _
xlPasteValues, Transpose:=True

Thanks for the help.
Regards,
Howard

 

[Bernie Deitrick]

Assuming that your sheet names and data rows are in the correct order:

Dim myRow As Range
Dim rSht As Range
Dim iSht As Integer

Set rSht =
Worksbooks("A1Source.xls").Worksheets("SheetName").Range("A2:A89")

iSht = 1
For Each myRow In Workbooks("A.xls"). _
Worksheets("Sheet1").Range("A289").Rows
myRow.Copy
Workbooks("B.xls").Worksheets(rSht.Cells(iSht).Value). _
Range("IV2").End(xlToLeft).Offset(0, 1).PasteSpecial _
xlPasteValues, Transpose:=True
iSht = iSht + 1
Next myRow

HTH,
Bernie
MS Excel MVP

 

[L. Howard Kittle]

Thanks again Bernie. Appreciate the help!

Regards,
Howard

 

[Bernie Deitrick]

Howard,

You're welcome again!

Bernie