Sheet Select Macro

  • Thread starter Thread starter PRINCE21
  • Start date Start date
P

PRINCE21

Hi, I have this problem,

firstly i have created a macro to create a pivot table of data when
ever the macro is activated. The macro automaticaly creates a new sheet
e.g. sheet10 to represent this data.

On that new sheet the macro has created i wanted to create anothe
rmacro that makes the sheet look tidy and nice. This involves copying
data from a different sheet into it.

The problem is i made this macro but there is an error. Say i delete
the sheet that is "Sheet10" and create a new one, the macro that i have
created does not work, this is because it cannot find sheet 10.

How can i make the macro comaptible with all sheets so no matter what
the sheet is called it still works on that selected sheet from which it
is activated.

Is there something on the lines of this, please help.:confused:
 
Right after the new worksheet with the pivottable is created, that new worksheet
is active.

You could set a worksheet variable to that active sheet and then use that later
in code:

dim NewPTWks as worksheet
'code to add pivottable
set newptwks = activesheet
'more code
'later....

with newptwks
.usedrange.columns.autofit
'anything else you need.
.name = "PT_" & format(now,"yyyymmdd_hhmmss")
end with
 
Keep getting Run-Time error 9
Subscript out of range.

Can you explain what you written more iam getting confused were to pu
what.

Anyon
 
Without seeing your code it's hard to tell what is going wrong, but my guess
is that you try to activate a sheet in a not proper way.

1) If your sheetname contains spaces make sure that you take those up in the
statement.
3) Make sure (if you're working with a variable containing your sheetname
that it is actually loaded with the (correct) name of your sheet.
4) Make sure that the workbook containing the sheet you want to open is the
active workbook or specify it in the activate command as wel
Workbooks("Your workbookname").worksheets("Your sheetname").activate
5) Do you use worksheetS
6) Surround the sheetname with " ". If you don't Excel assumes that you
have a name defined (with Insert / Name / ...).
7 If you're working with a loop (and thus use something like For I ... /
Worksheets(I).Activate / Next I , make sure that I is actually a number
smaller than or equal to the number of (non hidden) sheets.

All together : You try to activate a sheet and don't somehow Excel a "wrong"
index or name.


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
I agree with Auk that it's difficult to diagnose without your code and where you
got the run time error.

But I usually get this type of error when I refer to something that doesn't
exist:

Like with spelling errors:
worksheets("shee1t").range("a1").value = "xx"
instead of "sheet1"

But this code shouldn't have this error. I never referred to anything by name:

dim NewPTWks as worksheet
'code to add pivottable
set newptwks = activesheet
'more code
'later....

with newptwks
.usedrange.columns.autofit
'anything else you need.
.name = "PT_" & format(now,"yyyymmdd_hhmmss")
end with

===
Put this line at the top of the procedure (along with all your other "DIM's":

dim NewPTWks as worksheet


Then later, you have code that generates the pivottable. Use this line right
after you create the pivottable:

set newptwks = activesheet

Then continue with all your pivottable stuff.

Later, when you want to refer to the worksheet that contains the new pivottable,
you can use:

With newptwks
.range("x99").value = "hi there!"
end with
 
Back
Top