PDA

View Full Version : Need help with my macro "Sub or function not defined"



pekimo
12-25-2017, 01:18 AM
Hello everyone, first off, Merry Christmas!

Now.. Basically i am trying to make a macro that does many things at once (I am very new to excel and VBA as you will probably tell once you see my code lol.

I want the macro to do all of these things in this order:

1) save the current workbook
2) Unhide the sheet called "next"
3) Unprotect all sheets in the workbook (the password is password)
4) Add + 1 to Range C3 from "summary"
5) I then have to copy and paste (values only) of 3 different ranges to 3 different locations (as you can see in my attempt below)
6) Then i need to clear the contents of a whole bunch of ranges (as you can see in my attempt below)
7) Hide sheet "next" again.
8) Protect all my sheets in the workbook again (the password is password)
9) I then want to SAVE AS to "C:\Users\Spreads\ and name it "Admin" + B3 + C3 as xlsx (As you can see in my attempt below at the bottom)

I tried to run this macro and i get the error "Sub or function not defined", i have no idea what this means :/

Also; I feel like there has to be a quicker and shorter way to write that code, but im just too inexperienced to know how.
If anyone wishes to help me with this it would be greatly appreciated, If you do try to rewrite it, i don't expect you to put all my values in for me,
You can just use a symbol such as # or $ and i can fill them in myself, thankyou very much and Merry Christmas again!





Application.ScreenUpdating = False
ActiveWorkbook.Save
Sheets("Next").Visible = True
Worksheet(“Summary”).Unprotect "password"
Worksheet(“Inventory”).Unprotect "password"
Worksheet(“Orders”).Unprotect "password"
Worksheet(“Purchases”).Unprotect "password"
Range("Summary!C3").Value = Range("Summary!C3").Value + 1
Worksheets("Inventory").Range("E10:E159").Copy
Worksheets("Next").Range("C2").PasteSpecial Paste:=xlPasteValues
Worksheets("Summary").Range("C20:C44").Copy
Worksheets("Next").Range("D2").PasteSpecial Paste:=xlPasteValues
Worksheets("Summary").Range("C14").Copy
Worksheets("Next").Range("A2").PasteSpecial Paste:=xlPasteValues
Range("Orders!B48:Orders!EX78").ClearContents
Range("Purchases!B10:Purchases!EW14").ClearContents
Range("Purchases!B20:Purchases!EW24").ClearContents
Range("Purchases!B31:Purchases!EW35").ClearContents
Range("Purchases!B42:Purchases!EW46").ClearContents
Range("Purchases!B53:Purchases!EW57").ClearContents
Range("Purchases!B64:Purchases!EW68").ClearContents
Range("Purchases!B74:Purchases!EW78").ClearContents
Range("Purchases!B85:Purchases!EW89").ClearContents
Range("Purchases!B96:Purchases!EW100").ClearContents
Range("Purchases!B107:Purchases!EW111").ClearContents
Range("Purchases!B119:Purchases!EW123").ClearContents
Range("Purchases!B129:Purchases!EW133").ClearContents
Range("Purchases!B140:Purchases!EW144").ClearContents
Range("Purchases!B151:Purchases!EW155").ClearContents
Range("Purchases!B162:Purchases!EW166").ClearContents
Range("Purchases!B174:Purchases!EW178").ClearContents
Range("Purchases!B184:Purchases!EW188").ClearContents
Range("Purchases!B195:Purchases!EW199").ClearContents
Range("Purchases!B206:Purchases!EW210").ClearContents
Range("Purchases!B217:Purchases!EW221").ClearContents
Range("Purchases!B229:Purchases!EW233").ClearContents
Range("Purchases!B239:Purchases!EW243").ClearContents
Range("Purchases!B250:Purchases!EW254").ClearContents
Range("Purchases!B261:Purchases!EW265").ClearContents
Range("Purchases!B272:Purchases!EW276").ClearContents
Sheets("Next").Visible = False
Worksheet(“Summary”).Protect "password"
Worksheet(“Inventory”).Protect "password"
Worksheet(“Orders”).Protect "password"
Worksheet(“Purchases”).Protect "password"
ActiveWorkbook.SaveCopyAs "C:\Users\Spreads\Admin" & Range("Summary!B3").Value & Range("Summary!C3").Value & ".xlsx"
Application.ScreenUpdating = False
End Sub

macropod
12-25-2017, 02:56 AM
Cross-posted at:
https://www.mrexcel.com/forum/excel-questions/1036487-need-help-my-macro-sub-function-not-defined.html
https://www.excelforum.com/excel-programming-vba-macros/1213306-need-help-with-my-macro-sub-or-function-not-defined.html


Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3

Note: No support should be offered until the OP posts an undertaking to abide by this board's rules - he's taken exception to similar rules on another board.