VBA Creat a copy and Auto Delete not working

[PaulOPTC]

Good Morning,

I am hoping that someone will be able to help me with this, I feel like I am close, but it just is not doing what I would like it too.

I am not a VBA coder at all, I have built the last couple of sheets using frankenstine code from googling it, and tweaking it ever so slightly to fit my goals. However in this case, I can not seem to get it working, and I am not sure why.

I would like to create a button on my sheet that when clicked, it will do these following tasks (In order):

1. Save a copy of the entire workbook, in the same folder as the currentwork book, but with a slightly different name.
2. Delete all check boxes on the current worksheet
3. Delete entire rows, that have a specific word in them
4. Print the worksheet as a PDF and save it in a different folder - the folder will always be different, so maybe it makes more sence to set it as the current folder as the workbook?





First I made a button, that calls a few different moduels - No issues there I can keep it this way, or if we could set it up in one module that would also be fine.

All of these modules I have found by googling, and then tweaking it slightly, because of this, I do not know exactly what it wrong with it.


Module number 1:


Sub createnewWB()
Dim wbPath As String
Dim newWBname As String

newWBname = Range("C15")

wbPath = ActiveWorkbook.Path & "\"
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=wbPath & newWBname
End Sub


This module SHOULD hopfully save the entire workbook as a copy, using the name in C15, but it is currently not doing anything for me. C15 is "=N8&" - "&N10" Because it fills the job number and the job name based off what the user will type into it.


Module number 2:
Sub RemoveCheckboxes()
On Error Resume Next
ActiveSheet.CheckBoxes.Delete
Selection.FormatConditions.Delete
End Sub

This one works fine as it is, No issues there.

Module number 3:

Sub DeleteRowswithSpecificValue()
For i = Selection.Rows.Count To 1 Step -1
If Cells(i, 2).Value = "Auto Delete" Then
Cells(i, 2).EntireRow.Delete
End If
Next i
End Sub

This does nothing, I am not sure why? I was hoping that it would be able to delete rows that I label Auto Delete. The way I am labeling them Auto delete might be the issue? Example: "=IF(T16>0,T17,"Auto Delete")"

Module number 4:

There was a few different ones I was trying out, none of them worked, I dont know how to fix this, but Ideally, I would want the file name to be printed out the same as "C15" in module one.




I am taking any and all advice, I have spent too much time on this, and I can not get it working.

Sample.xlsm
	A	B	C	D	E		G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2													Proposal Type:
3													Time and material
4													Change Order
5													Proposal
6
7
8													Job Number:	1000
9													Change Order Number:
10													Job Address/Name:	Test Name
11
12
13													Submitted to:			Submitted by:
14
15	Project Specifications:		1000 - Test Name
16
17
18
19
20
21
22
23													Select Proposal Wording							Other
24	Base Bid:
25
26	•	Auto Delete
27
28	•
29
30	•
31
32	•
Proposal CO T&M

Cell Formulas
Range	Formula
C15	C15	=N8&" - "&N10
A24	A24	=IF('C:\Users\test\Desktop\Single Bid Template\Estimates\[Proposal, TnM, CO Template.xlsx]COTM Logic'!C2,"Scope of work:", "Base Bid:")
B26	B26	=IF(T16>0,T17,"Auto Delete")

 

[Engberg]

Module 3: What is it you want it to do exactly? As you have it right now, if you select rows 10-15 (so 5 rows) and run it, it will look for "Auto Delete" on rows 1-5 and delete if it finds it. Is that how you want it to work?

 

[Engberg]

Module 1:
(Heads up, this overwrites any other file with the same name without asking)

Sub createnewWB()
    newWBname = Range("C15")
    wbPath = ThisWorkbook.Path & "\"
    ActiveWorkbook.SaveCopyAs wbPath & newWBname & ".xlsm"
    MsgBox "File successfully saved as: " & newWBname & ".xlsm"
End Sub

 

[Engberg]

Module 4:

A bit wonky, I'll see if I can make something better, maybe someone can beat me to it.

Sub saveAsPDF()
    newPDFname = Range("C15").Value
    Test = Application.GetSaveAsFilename(InitialFileName:=newPDFname, fileFilter:="PDF (*.pdf), *.pdf")
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=newPDFname, OpenAfterPublish:=True
End Sub

 

[Engberg]

Module 4:

A bit wonky, I'll see if I can make something better, maybe someone can beat me to it.

Sub saveAsPDF()
    newPDFname = Range("C15").Value
    Test = Application.GetSaveAsFilename(InitialFileName:=newPDFname, fileFilter:="PDF (*.pdf), *.pdf")
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=newPDFname, OpenAfterPublish:=True
End Sub

Improved Module 4: Now allows you to cancel if you want to. This one also overwrites any files with the same file name.

Sub saveAsPDF()
    newPDFname = Range("C15").Value
    saveDialog = Application.GetSaveAsFilename(InitialFileName:=newPDFname, fileFilter:="PDF (*.pdf), *.pdf")
    If saveDialog = False Then
        MsgBox ("Save canceled")
        End
    Else
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=newPDFname, OpenAfterPublish:=True
    End If
End Sub

 

[PaulOPTC]

Module 1, try this:

Sub createnewWB()
    Dim fso As Scripting.FileSystemObject 'VBA window > Tools > References > Check "Microsoft Scripting Runtime" to make this work
    Set fso = CreateObject("Scripting.FileSystemObject")
    thisWBname = fso.GetBaseName(ThisWorkbook.Name) 'this WB name without extension
    newWBsuffix = Range("C15")
    wbPath = ThisWorkbook.Path & "\"
    Workbooks.Add
    ActiveWorkbook.SaveAs wbPath & thisWBname & newWBsuffix
End Sub

Module 3: What is it you want it to do exactly? As you have it right now, if you select rows 10-15 (so 5 rows) and run it, it will look for "Auto Delete" on rows 1-5 and delete if it finds it. Is that how you want it to work?

Good Afternoon,

Thank you for your reply;

The purpose of this workboook is to be a template for proposals for my company, this page in particular is the proposal page. Anything to the left of the gray bar, when printed will be on a PDF for my client to see.
All of the bullet points are different line items for the proposal - Example: (34) Xyz

These bullet points are automatically pulled from another sheet in the same workbook the "Totals" sheet. That sheet is for me to put my counts on, as well as do the math for a final price.


The final price is listed at the bottom of all of the bullet points on the proposal worksheet.

The goal of module three is to remove all of the empty bullet points, thus bringing my final price at the bottom higher, so my client doesnt have half a page of empty space with just bullet points.
I have currently been doing this by hand without any issues but I would like to automate the deletion .

I have it set to say "Auto delete" when it has no text from the other sheet, if it could delete that entire row (Ideally it would also delete the extra row directly below it, but beggers cant be choosers) thus moving up my final price I would be very happy.


Again thank you for your help! Have a nice weekend.

 

[Engberg]

Module 3: I think this works as you want it to. Will "Auto Delete" always be in the same column? Right now it searches in columns A:J, if "Auto Delete" is always found in the same column I might as well reduce the range that it has to look in.

Sub DeleteRowswithSpecificValue()

    Set Rng = Range("A1", "J" & Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row)
    For i = Rng.Cells.Count To 1 Step -1
        If Rng(i).Value = "Auto Delete" Then Range(Rng(i).EntireRow, Rng(i).Offset(1, 0).EntireRow).Delete
    Next i
   
End Sub

 

[PaulOPTC]

Module 3: I think this works as you want it to. Will "Auto Delete" always be in the same column? Right now it searches in columns A:J, if "Auto Delete" is always found in the same column I might as well reduce the range that it has to look in.

Sub DeleteRowswithSpecificValue()

    Set Rng = Range("A1", "J" & Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row)
    For i = Rng.Cells.Count To 1 Step -1
        If Rng(i).Value = "Auto Delete" Then Range(Rng(i).EntireRow, Rng(i).Offset(1, 0).EntireRow).Delete
    Next i
  
End Sub

Thank you so much for your help, I just implimented it, everything worked great! I really appreciate it!