Module not working

[thesilkster]

Hi i have created a macro the inserts a pivot and set the fields and values but excel creats a new sheet and the number goes up by 1 ie sheet 1 next is sheet 2 but my macro looks for sheet1

 

[Abdul Matheen]

Hi,

The macro will create new sheet with sequential number, hence you should not specify as sheet1 instead you have mention as activasheet. The macro creates a new sheet and activated.

Remove these 2 line
Sheets("sheet1").select
sheets("sheet1").name="Pivot1"

put the below code:
Activesheet.name="Pivot1"

Regards
Abdul Matheen

 

[Mike H..]

Hi i have created a macro the inserts a pivot and set the fields and values but excel creats a new sheet and the number goes up by 1 ie sheet 1 next is sheet 2 but my macro looks for sheet1




View attachment 9965

Hi,

You can add and name the sheet in a single line like this.

Worksheets.Add(After:=Sheets(ActiveSheet.Name)).Name = "Pivot1"

 

[thesilkster]

thanks for the replys my work around was to save the exel book as a template then it always produced a sheet 1 every time you open it and try and save.

it could be that i did not explain my problem fully i will upload a sample the proccess goes like this

1. open macro work book
2. copy data from data extract
3. Paste into Macro work book
4. Run Macro Ctr+m removes and insert named columns
5. Run Macro Ctr+b insert pivot table sorts data... Great
6. Run Macro Ctr+q clears main data ready for next day
7. Copy data extract
8. Paste into Macro Book
9. Run Macro Ctr+m removes and insert named columns
10. Run Macro Ctr+b Error as its looking to insert Sheet 2 Now and name it Pivot 1 i already have this..

the book i uploaded has the data as the desk clerk has just pasted the data in No2
any help is appreciated