Workbook looping to many worksheets

[L. Howard Kittle]

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]

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

 

[Dave Peterson]

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

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

 

[L. Howard Kittle]

Hi Dave,

Works perfectly! Thanks a ton for the help.

Regards,
Howard