Here is my question. I have a workbook that has worksheet names that change depending on the day. I could have as many as 30 different sheets at one time. I am trying to write a macro that will sort the 4 of the worksheets data into one master file in a different location.
Here is the code I have thus far:
Sub Disperse_Data1()
Sheets(" 4828-010O").Select
Range("B1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Open Filename:= _
"C:\Documents and Settings\A369747.000\My Documents\AC Hose Dimensional Data.xls"
Range("A1").Select
ActiveSheet.Paste
Range("C1").Select
Windows("sorted.xls").Activate
Sheets(" 4828-010I").Select
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("AC Hose Dimensional Data.xls").Activate
Range("C1").Select
ActiveSheet.Paste
Windows("sorted.xls").Activate
Sheets(" 4828-010V").Select
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("AC Hose Dimensional Data.xls").Activate
Range("D1").Select
ActiveSheet.Paste
Windows("sorted.xls").Activate
End Sub
This process would need to be repeated a varying number of times dpending upon the worksheet titles in the source ("sorted.xls") workbook. The range of worksheet titles in this workbook can be as many as 550 different worksheet names.
So my question is there an easier way and does anyone have a better way to approach this problem.
Thanks for your help in advance.
Shawn
Here is the code I have thus far:
Sub Disperse_Data1()
Sheets(" 4828-010O").Select
Range("B1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Open Filename:= _
"C:\Documents and Settings\A369747.000\My Documents\AC Hose Dimensional Data.xls"
Range("A1").Select
ActiveSheet.Paste
Range("C1").Select
Windows("sorted.xls").Activate
Sheets(" 4828-010I").Select
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("AC Hose Dimensional Data.xls").Activate
Range("C1").Select
ActiveSheet.Paste
Windows("sorted.xls").Activate
Sheets(" 4828-010V").Select
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("AC Hose Dimensional Data.xls").Activate
Range("D1").Select
ActiveSheet.Paste
Windows("sorted.xls").Activate
End Sub
This process would need to be repeated a varying number of times dpending upon the worksheet titles in the source ("sorted.xls") workbook. The range of worksheet titles in this workbook can be as many as 550 different worksheet names.
So my question is there an easier way and does anyone have a better way to approach this problem.
Thanks for your help in advance.
Shawn