Error 91 while trying to save a workbook

  • Thread starter Adam_needs_help
  • Start date
A

Adam_needs_help

Here is my code, any idea why I ma getting error 91? I use the same basic
code elsewhere and it works. I think there may be an error in which workbook
is active. I am trying to save the workbook I just copied a sheet into.

Windows("Make_The_MasterFile.xls").Activate 'not trying to save this workbook
Sheets("OverallBigHitters").Activate
ActiveSheet.Select
ActiveSheet.Copy 'trying to save this one
WBforBigHit = ActiveWorkbook.Name
 
D

Dave Peterson

What line causes the error? I don't see any saving going on.

I'd try:

workbooks("make_the_masterfile.xls").worksheets("Overallbighitters").copy

with activesheet 'the newly copied sheet
'.parent of the new worksheet is the new workbook
.parent.saveas filename:="C:\test.xls"
end with
 
A

Adam_needs_help

The last line of the code here causes an error. Once I set the variable to
the name of the workbook I save that workbook (I do it later on). My
explanation was poor, sorry.

WBforBigHit = ActiveWorkbook.Name
 
D

Dave Peterson

Did you declare wbforbighit as a string or an object/workbook?

Dim WBforBigHit as string
WBforBigHit = ActiveWorkbook.Name
or
dim WBforBigHit as workbook
set WBforBigHit = ActiveWorkbook
 
A

Adam_needs_help

as a workbook, under Public variables

Dave Peterson said:
Did you declare wbforbighit as a string or an object/workbook?

Dim WBforBigHit as string
WBforBigHit = ActiveWorkbook.Name
or
dim WBforBigHit as workbook
set WBforBigHit = ActiveWorkbook
Click to expand...
 
D

Dave Peterson

Then you have to use the "Set" command.

And the only way you rename a workbook is by saving it with a new name.

Adam_needs_help said:
as a workbook, under Public variables
Click to expand...
 
A

Adam_needs_help

That worked, thanks. Not sure why the other macros thought my variable was a
string, but that's why they were working. I decided the string way was
easier than the workbook way. Thanks for your help.
 
Top