Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping a column in workbook 1 to workbook 2's sheets
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" wrote in message . .. 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping a column in workbook 1 to workbook 2's sheets
Thanks, Bernie. I'll give it a go.
Regards, Howard "L. Howard Kittle" wrote in message . .. 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping a column in workbook 1 to workbook 2's sheets
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" wrote in message . .. 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping a column in workbook 1 to workbook 2's sheets
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).Val ue). _ Range("IV2").End(xlToLeft).Offset(0, 1).PasteSpecial _ xlPasteValues, Transpose:=True iSht = iSht + 1 Next myRow HTH, Bernie MS Excel MVP "L. Howard Kittle" wrote in message . .. 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" wrote in message . .. 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping a column in workbook 1 to workbook 2's sheets
Thanks again Bernie. Appreciate the help!
Regards, Howard "L. Howard Kittle" wrote in message . .. 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping a column in workbook 1 to workbook 2's sheets
Howard,
You're welcome again! Bernie Thanks again Bernie. Appreciate the help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel workbook, same name on diff sheets, need the next column and | Excel Discussion (Misc queries) | |||
CREATE NEW WORKBOOK AND SHEETS BASED ON COLUMN DATA | Excel Worksheet Functions | |||
Looping worksheets in workbook | Excel Programming | |||
Sheets select method fails when workbook is opened by another workbook | Excel Programming | |||
Looping Through Worksheets In A Workbook | Excel Programming |