DaveK
08-03-2008, 08:11 PM
I am still kinda new to VBA, and this should be so simple, yet I've tried everything... and I stil cant get this to work... with ERROR subscript out of range. Can someone please help me understand.
Workbooks(workingRawDataFile).Worksheets(1).Activate
This works in some places, yet not in other places in my VBA.
I think I am confusing objects.
I am trying to write a simple VBA to find and copy certain data from several different existing workbooks, and create a new workbook with the chosen data in it. Seems very easy... yet I am having trouble with it.
Here is my code so far, and with COMMENTS in it where the problem is.
Sub Accelerator_Report()
Dim Path_to_Desktop As String
Dim YearMonthFriday As String
Dim workingRawDataFile As String
Dim AcceleratorReport As String
Dim AcceleratorReportFullPath As String
Dim path_to_AcceleratorRawData_folder As String
' =========== initialise values
YearMonthFriday = "2008.08.01"
Path_to_Desktop = "C:\Documents and Settings\dk\Desktop\"
path_to_AcceleratorRawData_folder = Path_to_Desktop & YearMonthFriday & "_AcceleratorRawFiles\"
AcceleratorReport = YearMonthFriday & "_AcceleratorReport.xls"
AcceleratorReportFullPath = Path_to_Desktop & AcceleratorReport
'the newly created REPORT file is placed on the WINDOWS DESKTOP
Workbooks.Add 'create a new blank EXCEL spreadsheet
Worksheets(1).Activate
ActiveSheet.Name = "Team Managers"
ActiveWorkbook.SaveAs Filename:=AcceleratorReportFullPath
workingRawDataFile = path_to_AcceleratorRawData_folder & "WebReport Excel.xls"
' ==================================================
'lets go get the first EXCEL raw data file from the
' FULLYEAR.MONTH.WEEKENDING_AcceleratorRawFiles FOLDER
Workbooks.Open Filename:=workingRawDataFile
Worksheets(1).Activate
Cells.Find(What:="Team Manager", LookIn:=xlValues _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks(AcceleratorReport).Worksheets("Team Managers").Activate
Cells(1, "A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' EVERYTHING WORKS OK UNTIL HERE
' ===============
' get COLUMN B
'===THIS BELOW DOES NOT WORK! ? I dont know why...
Workbooks(workingRawDataFile).Worksheets(1).Activate
Range("A:AA").Select
Cells(1, "A").Select
Cells.Find(What:="Lead Author", After:=ActiveCell, LookIn:=xlValues _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks(AcceleratorReport).Worksheets("Team Managers").Activate
Cells(1, "B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I have tried this as well, and it works a little further, then the CELL fails.
Workbooks.Open Filename:=workingRawDataile
Worksheets(1).Activate
Range("A:AA"). Select
Cells(1, "A").Select
Workbooks(workingRawDataFile).Worksheets(1).Activate
This works in some places, yet not in other places in my VBA.
I think I am confusing objects.
I am trying to write a simple VBA to find and copy certain data from several different existing workbooks, and create a new workbook with the chosen data in it. Seems very easy... yet I am having trouble with it.
Here is my code so far, and with COMMENTS in it where the problem is.
Sub Accelerator_Report()
Dim Path_to_Desktop As String
Dim YearMonthFriday As String
Dim workingRawDataFile As String
Dim AcceleratorReport As String
Dim AcceleratorReportFullPath As String
Dim path_to_AcceleratorRawData_folder As String
' =========== initialise values
YearMonthFriday = "2008.08.01"
Path_to_Desktop = "C:\Documents and Settings\dk\Desktop\"
path_to_AcceleratorRawData_folder = Path_to_Desktop & YearMonthFriday & "_AcceleratorRawFiles\"
AcceleratorReport = YearMonthFriday & "_AcceleratorReport.xls"
AcceleratorReportFullPath = Path_to_Desktop & AcceleratorReport
'the newly created REPORT file is placed on the WINDOWS DESKTOP
Workbooks.Add 'create a new blank EXCEL spreadsheet
Worksheets(1).Activate
ActiveSheet.Name = "Team Managers"
ActiveWorkbook.SaveAs Filename:=AcceleratorReportFullPath
workingRawDataFile = path_to_AcceleratorRawData_folder & "WebReport Excel.xls"
' ==================================================
'lets go get the first EXCEL raw data file from the
' FULLYEAR.MONTH.WEEKENDING_AcceleratorRawFiles FOLDER
Workbooks.Open Filename:=workingRawDataFile
Worksheets(1).Activate
Cells.Find(What:="Team Manager", LookIn:=xlValues _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks(AcceleratorReport).Worksheets("Team Managers").Activate
Cells(1, "A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' EVERYTHING WORKS OK UNTIL HERE
' ===============
' get COLUMN B
'===THIS BELOW DOES NOT WORK! ? I dont know why...
Workbooks(workingRawDataFile).Worksheets(1).Activate
Range("A:AA").Select
Cells(1, "A").Select
Cells.Find(What:="Lead Author", After:=ActiveCell, LookIn:=xlValues _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks(AcceleratorReport).Worksheets("Team Managers").Activate
Cells(1, "B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I have tried this as well, and it works a little further, then the CELL fails.
Workbooks.Open Filename:=workingRawDataile
Worksheets(1).Activate
Range("A:AA"). Select
Cells(1, "A").Select