Workbook looping to many worksheets

Hello Excel Experts and Users,

This code from Bernie Deitrick works fine for the example I provided in a
previous post.
It works because the sheets in test workbook B are named sheet1, sheet2,
sheet3 etc. The four columns in each row of data in Range("B2:E11") are
nicely transposed to the various sheets in book B.

In reality the names of the sheets in the real workbook B are names like
Adams, Allen, Brown, Cuyahoga, etc. A list of these names are in column A
of workbook A, Sheet1 A2:A11. (there are 88 names but I am using 10 in my
testing)

How can I pass these names to the PasteSpecial & Transpose code line similar
to how iSht increments the row number, except I get the next row name each
time.

Instead of ...Workbooks("B.xls").Worksheets("Sheet" & iSht). _
How can I ...Workbooks("B.xls").Worksheets("increment names 1 row at a
time"). _

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

Application.ScreenUpdating = False

iSht = 1

For Each myRow In Workbooks("A.xls"). _
Worksheets("Sheet1").Range("B2:E11").Rows
myRow.Copy

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

iSht = iSht + 1

Next myRow
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub


Thanks for any help.
Regards,
Howard

maybe...

Option explicit
Sub Reconstruct_To_Source()

dim myRng as range
dim myCell as range

Application.ScreenUpdating = False

set myrng = workbooks("a.xls").worksheets("sheet1").range("a2:A11")

For Each mycell in myrng.cells
'columns B:E of the same row as mycell.
mycell.offset(0,1).resize(1,4).copy
workbooks("b.xls").worksheets(mycell.value) _
.Range("IV5").End(xlToLeft).Offset(0, 1).PasteSpecial _
xlPasteValues, Transpose:=True
next mycell

Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub

"L. Howard Kittle" wrote:
>
> Hello Excel Experts and Users,
>
> This code from Bernie Deitrick works fine for the example I provided in a
> previous post.
> It works because the sheets in test workbook B are named sheet1, sheet2,
> sheet3 etc. The four columns in each row of data in Range("B2:E11") are
> nicely transposed to the various sheets in book B.
>
> In reality the names of the sheets in the real workbook B are names like
> Adams, Allen, Brown, Cuyahoga, etc. A list of these names are in column A
> of workbook A, Sheet1 A2:A11. (there are 88 names but I am using 10 in my
> testing)
>
> How can I pass these names to the PasteSpecial & Transpose code line similar
> to how iSht increments the row number, except I get the next row name each
> time.
>
> Instead of ...Workbooks("B.xls").Worksheets("Sheet" & iSht). _
> How can I ...Workbooks("B.xls").Worksheets("increment names 1 row at a
> time"). _
>
> Sub Reconstruct_To_Source()
> Dim myRow As Range
> Dim iSht As Integer
>
> Application.ScreenUpdating = False
>
> iSht = 1
>
> For Each myRow In Workbooks("A.xls"). _
> Worksheets("Sheet1").Range("B2:E11").Rows
> myRow.Copy
>
> Workbooks("B.xls").Worksheets("Sheet" & iSht). _
> Range("IV5").End(xlToLeft).Offset(0, 1).PasteSpecial _
> xlPasteValues, Transpose:=True
>
> iSht = iSht + 1
>
> Next myRow
> Application.ScreenUpdating = True
> Application.CutCopyMode = False
> End Sub
>
> Thanks for any help.
> Regards,
> Howard

--

Dave Peterson

ps. I didn't test it, but it did compile ok.

"L. Howard Kittle" wrote:
>
> Hello Excel Experts and Users,
>
> This code from Bernie Deitrick works fine for the example I provided in a
> previous post.
> It works because the sheets in test workbook B are named sheet1, sheet2,
> sheet3 etc. The four columns in each row of data in Range("B2:E11") are
> nicely transposed to the various sheets in book B.
>
> In reality the names of the sheets in the real workbook B are names like
> Adams, Allen, Brown, Cuyahoga, etc. A list of these names are in column A
> of workbook A, Sheet1 A2:A11. (there are 88 names but I am using 10 in my
> testing)
>
> How can I pass these names to the PasteSpecial & Transpose code line similar
> to how iSht increments the row number, except I get the next row name each
> time.
>
> Instead of ...Workbooks("B.xls").Worksheets("Sheet" & iSht). _
> How can I ...Workbooks("B.xls").Worksheets("increment names 1 row at a
> time"). _
>
> Sub Reconstruct_To_Source()
> Dim myRow As Range
> Dim iSht As Integer
>
> Application.ScreenUpdating = False
>
> iSht = 1
>
> For Each myRow In Workbooks("A.xls"). _
> Worksheets("Sheet1").Range("B2:E11").Rows
> myRow.Copy
>
> Workbooks("B.xls").Worksheets("Sheet" & iSht). _
> Range("IV5").End(xlToLeft).Offset(0, 1).PasteSpecial _
> xlPasteValues, Transpose:=True
>
> iSht = iSht + 1
>
> Next myRow
> Application.ScreenUpdating = True
> Application.CutCopyMode = False
> End Sub
>
> Thanks for any help.
> Regards,
> Howard

--

Dave Peterson

Hi Dave,

Works perfectly! Thanks a ton for the help.

Regards,
Howard

"L. Howard Kittle" <[email protected]> wrote in message
news:[email protected]...
> Hello Excel Experts and Users,
>
> This code from Bernie Deitrick works fine for the example I provided in a
> previous post.
> It works because the sheets in test workbook B are named sheet1, sheet2,
> sheet3 etc. The four columns in each row of data in Range("B2:E11") are
> nicely transposed to the various sheets in book B.
>
> In reality the names of the sheets in the real workbook B are names like
> Adams, Allen, Brown, Cuyahoga, etc. A list of these names are in column A
> of workbook A, Sheet1 A2:A11. (there are 88 names but I am using 10 in my
> testing)
>
> How can I pass these names to the PasteSpecial & Transpose code line
> similar to how iSht increments the row number, except I get the next row
> name each time.
>
> Instead of ...Workbooks("B.xls").Worksheets("Sheet" & iSht). _
> How can I ...Workbooks("B.xls").Worksheets("increment names 1 row at a
> time"). _
>
> Sub Reconstruct_To_Source()
> Dim myRow As Range
> Dim iSht As Integer
>
> Application.ScreenUpdating = False
>
> iSht = 1
>
> For Each myRow In Workbooks("A.xls"). _
> Worksheets("Sheet1").Range("B2:E11").Rows
> myRow.Copy
>
> Workbooks("B.xls").Worksheets("Sheet" & iSht). _
> Range("IV5").End(xlToLeft).Offset(0, 1).PasteSpecial _
> xlPasteValues, Transpose:=True
>
> iSht = iSht + 1
>
> Next myRow
> Application.ScreenUpdating = True
> Application.CutCopyMode = False
> End Sub
>
>
> Thanks for any help.
> Regards,
> Howard
>