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
Reply With Quote
