Solved: Confused about Workbooks object

[DaveK]

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

[Mavyak]

Declare a variable of type Workbook like so:
Dim WB as Workbook
Then replace this line:
Workbooks.Open Filename:=workingRawDataFile (by the way, your code lists variable name workingRawDataile without the F).

with this line:
Set WB = Workbooks.Open(wrokingRawDataFile)
Then you can refer to the variable WB anytime you want to control the workingRawDataFile workbook.

The whole problem you're having is that workingRawDataFile contains the workbook filename, not the workbook name. The file name has ".xls" on the end of it. The wrokbook name does not.

[Bob Phillips]

When selecting a range, you cannot select a range in another worksheet, or another workibook, it has to be the activesheet and active workbook. Of course, you can activate each object from the top down, but it is far better to not do any selecting, and use object variables as suggested.

[DaveK]

Thanks for your replies... so I've made a few small changes, and here is the code as of now... and the 2nd instance CELLS.FIND does not work.
ERROR object variable not set.

I have used both ways of activating the working file... they both work,


Sub Accelerator_Report()
Dim Path_to_Desktop As String
Dim YearMonthFriday As String

Dim workingRawDataFile As String

Dim WB As Workbook

Dim AcceleratorAgendaReport As String
Dim AcceleratorAgendaReportFullPath 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\"
AcceleratorAgendaReport = YearMonthFriday & "_Accelerator Agenda Report.xls"

AcceleratorAgendaReportFullPath = Path_to_Desktop & AcceleratorAgendaReport

'the newly created REPORT file is placed on the WINDOWS DESKTOP

Workbooks.Add 'create a new blank EXCEL spreadsheet

Worksheets(1).Activate
ActiveSheet.Name = "All Team Managers"
ActiveWorkbook.SaveAs Filename:=AcceleratorAgendaReportFullPath

workingRawDataFile = path_to_AcceleratorRawData_folder & "WebReport Excel.xls"

' ==================================================
' get the first EXCEL raw data file from the
' FULLYEAR.MONTH.WEEKENDING_AcceleratorRawFiles FOLDER

Set WB = Workbooks.Open(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(AcceleratorAgendaReport).Worksheets("All Team Managers").Activate

Cells(1, "A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


' ===============
' get COLUMN B

Set WB = Workbooks.Open(workingRawDataFile)

' Workbooks.Open Filename:=workingRawDataFile
' Worksheets(1).Activate
' Range("A:AA").Select

THIS NOW FAILS. It worked above, but now it does not work?

I AM CONFUSED, and I dont understand why it fails.
The worksheet is active, it doesnt seem to matter if a set a range or not.


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(AcceleratorAgendaReport).Worksheets("All Team Managers").Activate
Cells(1, "B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False




I dont understand why the CELLS.FIND fails. It worked above. What am I missing here?
THANKS so much... I really need to get past the simple stumbling block, and then I can complete the many columns.

Dave

[DaveK]

Set WB = Workbooks.Open(workingRawDataFile)
Then you can refer to the variable WB anytime you want to control the workingRawDataFile workbook.

The whole problem you're having is that workingRawDataFile contains the workbook filename, not the workbook name. The file name has ".xls" on the end of it. The workbook name does not.

Well, I tried that too, and it seems that is not the issue.
In the line such as
Workbooks(AcceleratorAgendaReport).Worksheets("All Team Managers").Activate

the Workbooks(filename) MUST contain the .xls or it does not work.
So to me it appears the Workbooks object does in fact contain the .xls at the end of it.

Correct?

Now the CELLS.FIND does not work. Still hope for someone to help me please.
THANK YOU.

[mdmackillop]

Avoid the Activating and Selecting. Create variables for the Workbooks and sheets and use them to qualify Cells or whatever. You should end up with something like this (untested)

Dim WB As Workbook
Dim WS As Worksheet
Dim c As Range
Dim Tgt As Range

Set Tgt = Workbooks(AcceleratorAgendaReport).Worksheets("All Team Managers").Cells(1, "A")
Set WB = Workbooks.Open(workingRawDataFile)
Set WS = WB.Worksheets(1)

Set c = WS.Cells.Find(What:="Team Manager", LookIn:=xlValues _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
Range(c, c.End(xlDown)).Copy
Tgt.PasteSpecial Paste:=xlPasteValues
End If

[DaveK]

THANK YOU! This is exactly what I needed and it works perfectly.

I realllllyyyy appreciate your help so much. I am so happy to be a member here.

THANKS
Dave