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

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

Dim myRow As Range
Dim iSht As Integer

iSht = 1
For Each myRow In Workbooks("A.xls"). _
Worksheets("Sheet1").Range("A2:D11").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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

Regards,
Howard

"L. Howard Kittle" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

"L. Howard Kittle" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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("A2:D89").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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "L. Howard Kittle" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>

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("A2:D89").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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "L. Howard Kittle" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>

Thanks again Bernie. Appreciate the help!

Regards,
Howard

"L. Howard Kittle" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Howard,

You're welcome again!

Bernie

> Thanks again Bernie. Appreciate the help!