• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Module not working

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




upload_2014-8-30_15-48-12.png
 
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
 
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"
 
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
 

Attachments

  • Main CN Data Temp1.xlsm
    56.5 KB · Views: 1
  • data for excel Main CN.xls
    135.5 KB · Views: 0
Last edited:
Back
Top