Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old March 10th 11, 08:54 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 203
Default Macro to run on all files in a folder but exclude sheets with Pivot Table, Pivot Charts and Charts

"Nasir" wrote in message
...
On Mar 8, 3:42 am, "Clif McIrvin" wrote:
"Nasir" wrote in message

[...]

Hi Clif
Thanks for your help
I changed the lines as per your instructions.

When I run the Private Sub ScanWorkbooks
I get Runtime error 1004 in Sub FormatWorksheet
“Select method of Range class failed”
sh.Range("A2").Select <<< this line is highlighted

Can you guide me please?


-----------

That's the last line of FormatWorksheet, isn't it? So it appears that
everything else is doing what you need.

Try changing that line to

sh.Range("A2").Activate

and see if that works. If that doesn't fix it post back.
--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)



  #12   Report Post  
Old March 11th 11, 12:40 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 203
Default Macro to run on all files in a folder but exclude sheets with Pivot Table, Pivot Charts and Charts

"Nasir" wrote in message
...
On Mar 8, 3:42 am, "Clif McIrvin" wrote:
"Nasir" wrote in message

[ ]

Hi Clif
Thanks for your help
I changed the lines as per your instructions.

When I run the Private Sub ScanWorkbooks
I get Runtime error 1004 in Sub FormatWorksheet
“Select method of Range class failed”
sh.Range("A2").Select <<< this line is highlighted

Can you guide me please?

Hi Nasir

You're welcome.

I did some testing .... Range.Select and Range.Activate do not work
unless the sheet is the active sheet .... so change that one line to
these two lines:

sh.Activate
sh.Range("A2").Activate

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)


  #13   Report Post  
Old March 11th 11, 10:55 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2010
Posts: 9
Default Macro to run on all files in a folder but exclude sheets withPivot Table, Pivot Charts and Charts

On Mar 11, 3:40*am, "Clif McIrvin" wrote:
"Nasir" wrote in message

...
On Mar 8, 3:42 am, "Clif McIrvin" wrote: "Nasir" wrote in message

[ ]

Hi Clif
Thanks for your help
I changed the lines as per your instructions.

When I run the Private Sub ScanWorkbooks
I get Runtime error 1004 in Sub FormatWorksheet
Select method of Range class failed
sh.Range("A2").Select <<< this line is highlighted

Can you guide me please?

Hi Nasir

You're welcome.

I did some testing .... Range.Select and Range.Activate do not work
unless the sheet is the active sheet .... so change that one line to
these two lines:

sh.Activate
sh.Range("A2").Activate

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)


Thank you sooooooooooooooooooo much
Worked like a Magician Wand

Appreciate the great help from you.
Nasir
  #14   Report Post  
Old March 11th 11, 05:13 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 203
Default Macro to run on all files in a folder but exclude sheets with Pivot Table, Pivot Charts and Charts

"Nasir" wrote in message
...
On Mar 11, 3:40 am, "Clif McIrvin" wrote:
"Nasir" wrote in message

...
On Mar 8, 3:42 am, "Clif McIrvin" wrote:
"Nasir" wrote in message

[ ]

Hi Clif
Thanks for your help
I changed the lines as per your instructions.

When I run the Private Sub ScanWorkbooks
I get Runtime error 1004 in Sub FormatWorksheet
Select method of Range class failed
sh.Range("A2").Select <<< this line is highlighted

Can you guide me please?

Hi Nasir

You're welcome.

I did some testing .... Range.Select and Range.Activate do not work
unless the sheet is the active sheet .... so change that one line to
these two lines:

sh.Activate
sh.Range("A2").Activate

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)


Thank you sooooooooooooooooooo much
Worked like a Magician Wand

Appreciate the great help from you.
Nasir


You're welcome. Thanks for feeding back.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to group data in pivot charts MichaelR Excel Programming 0 June 16th 08 10:37 PM
pie charts automatically exclude zero values Cris[_2_] Charts and Charting in Excel 1 March 4th 08 03:43 AM
PivotTable Charts Joey Excel Discussion (Misc queries) 1 February 12th 08 12:30 PM
charts exclude cell with formula linda Charts and Charting in Excel 0 September 19th 07 05:12 AM
macro for printing pivot charts marina madeleine Excel Programming 0 October 15th 04 02:03 PM


All times are GMT +1. The time now is 11:17 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright ©2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017