Problem refering to Add-In sheet
 

Hello All

I have a file which contains code to copy information from a shee
called data to a sheet called Report. - Everything works fine.

I have several files to work on. Each file contains a sheet calle
data. I currently have to copy each Data sheet into my file containin
the macros then run the code.

I thought it would be better if I created a menu button to run the cod
and created the file as an Add-In. Then all I would need to do (once th
Add-In was installed) would be open the file containing the data an
click on the button created by the Add-In.

I added code to create a menubutton on workbook open and tested Ok so
removed the sheet called Data and created an Add-In from the file.

When I open any of the files containing the sheet "Data" and click th
button I get Run-time error 9 - Subscript out of range - and I trac
this to the line

Sheets("Info").Range("B1").ClearContents

This is refering to the sheet that has been hidden within the Add-In

How can I get round this problem, Do I have to "Show" the sheet or i
there a particular way of refering to a sheet within an Add-In. (o
perhaps it cannot be done at all like this?

Could someone please advise me as how I can progress.

TIA

Kenny
Win NT and 2000 with Office 9

--
N E Bod
-----------------------------------------------------------------------
N E Body's Profile: http://www.excelforum.com/member.php...fo&userid=1126
View this thread: http://www.excelforum.com/showthread.php?threadid=39745

Problem refering to Add-In sheet
 

This probably means that it is trying to find that worksheet in the
activeworkbook, not the add-in.

If the code is in the add-in, precede by ThisWorkbook

ThisWorkbook.Worksheets("Info").Range("B1").ClearC ontents


--

HTH

RP
(remove nothere from the email address if mailing direct)


"N E Body" wrote in
message ...

Hello All

I have a file which contains code to copy information from a sheet
called data to a sheet called Report. - Everything works fine.

I have several files to work on. Each file contains a sheet called
data. I currently have to copy each Data sheet into my file containing
the macros then run the code.

I thought it would be better if I created a menu button to run the code
and created the file as an Add-In. Then all I would need to do (once the
Add-In was installed) would be open the file containing the data and
click on the button created by the Add-In.

I added code to create a menubutton on workbook open and tested Ok so I
removed the sheet called Data and created an Add-In from the file.

When I open any of the files containing the sheet "Data" and click the
button I get Run-time error 9 - Subscript out of range - and I trace
this to the line

Sheets("Info").Range("B1").ClearContents

This is refering to the sheet that has been hidden within the Add-In

How can I get round this problem, Do I have to "Show" the sheet or is
there a particular way of refering to a sheet within an Add-In. (or
perhaps it cannot be done at all like this?

Could someone please advise me as how I can progress.

TIA

Kenny
Win NT and 2000 with Office 97


--
N E Body
------------------------------------------------------------------------
N E Body's Profile:
http://www.excelforum.com/member.php...o&userid=11263
View this thread: http://www.excelforum.com/showthread...hreadid=397456

Problem refering to Add-In sheet
 

Thanks Bob

Once again you have come to my rescue!

I have altered the code and it runs now except for the part which
prints a worksheet.

I had been printing "Report" sheet by

ActiveWindow.SelectedSheets.PrintOut Copies:=1

But I have no idea what to change this to to get it to print!

Any ideas?

Regards
Kenny


--
N E Body
------------------------------------------------------------------------
N E Body's Profile: http://www.excelforum.com/member.php...o&userid=11263
View this thread: http://www.excelforum.com/showthread...hreadid=397456

Problem refering to Add-In sheet
 

Kenny,

As the add-in is hidden, you cannot print directly from there. What you have
to do is copy it out to a new workbook, print that sheet, then close the new
workbook

ThisWorkbook.Worksheets("Report").Copy
With ActiveSheet
.PrintOut Copies:=1
.Parent.Close False
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"N E Body" wrote in
message ...

Thanks Bob

Once again you have come to my rescue!

I have altered the code and it runs now except for the part which
prints a worksheet.

I had been printing "Report" sheet by

ActiveWindow.SelectedSheets.PrintOut Copies:=1

But I have no idea what to change this to to get it to print!

Any ideas?

Regards
Kenny


--
N E Body
------------------------------------------------------------------------
N E Body's Profile:
http://www.excelforum.com/member.php...o&userid=11263
View this thread: http://www.excelforum.com/showthread...hreadid=397456

Problem refering to Add-In sheet
 

Thanks once again, That did the trick

Regards
Kenny

"Bob Phillips" wrote in message
...
Kenny,

As the add-in is hidden, you cannot print directly from there. What you
have
to do is copy it out to a new workbook, print that sheet, then close the
new
workbook

ThisWorkbook.Worksheets("Report").Copy
With ActiveSheet
.PrintOut Copies:=1
.Parent.Close False
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"N E Body" wrote
in
message ...

Thanks Bob

Once again you have come to my rescue!

I have altered the code and it runs now except for the part which
prints a worksheet.

I had been printing "Report" sheet by

ActiveWindow.SelectedSheets.PrintOut Copies:=1

But I have no idea what to change this to to get it to print!

Any ideas?

Regards
Kenny


--
N E Body
------------------------------------------------------------------------
N E Body's Profile:
http://www.excelforum.com/member.php...o&userid=11263
View this thread:
http://www.excelforum.com/showthread...hreadid=397456