VBA Save as new workbook Use MACRO ON NEW workbook?

[Melimob]

Hi All

Not sure if this can be done?

Ultimately I want to create a new workbook without formulas with a part dynamic file name but the user selects the folder.

I was thinking I could:

a) SaveAs 'File name would read a cell +todays date'.
b) user chooses path
c) saves
d) in newly saved wkbook? copy and pastes over itself to remove formulas
e) deletes unwanted sheets
f) saves again

The code I have for this is below but I didn't know how to do a) from above?
also, it's not saving as a new file it just writes over this one?

I guess I should also add a box in case the file already exists it asks if they want to overwrite?

Sub SaveAsNewWkbook_New()
    
    RemoveFilters1a
        
    Application.GetSaveAsFilename ActiveWorkbook.Path
    
    ActiveWorkbook.Save
    
    All_Cells_In_All_WorkSheets_1
    ActiveWorkbook.Save
End Sub

RemoveFilters1a code:

Sub RemoveFilters1a()


    Dim WS As Worksheet


    With Application


        .EnableEvents = False
        .ScreenUpdating = False




        For Each WS In ActiveWorkbook.Sheets


            On Error Resume Next


            WS.ShowAllData


            On Error GoTo 0


        Next


        .EnableEvents = True
        .ScreenUpdating = True


    End With


End Sub

All_Cells_In_All_WorkSheets_1 code:

Sub All_Cells_In_All_WorkSheets_1()
    Dim sh As Worksheet
    
    Sheets("ImportNonCCB").Visible = True
    Sheets("LKUPs").Visible = True
    
    For Each sh In ActiveWorkbook.Worksheets
        sh.Select
        With sh.UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
        End With
        Application.CutCopyMode = False
    Next sh
    
    Sheets("CC Reconfiguration Data").Range("I3" & LastRow) _
    .Formula = "=VLOOKUP(tblCCReconfig[Control Centre Company Builds],Table6[[Control Centre Company Builds]:[POA GDS]],6,0)"
    
    Sheets("ImportNonCCB").Visible = False
    Sheets("ImportCCBMI").Visible = False
    Sheets("ImportCCB").Visible = False
    Sheets("LKUPs").Visible = False
    Sheets("Instructions").Delete


    
End Sub

any advice gratefully received...this is the final piece of this project and I am done! woo hoo!

 

[JLGWhiz]

on saving the file as a new workbook:
The SaveAs method in Excel does create a new workbook. It leaves the old file intact on the hard drive as it was when the workbook was opened, unless the user saves any changes before executing the SaveAs. When doing the SaveAs by code, after the transaction processes the new workbook will inherit any macros that were in the original workbook, unless the SaveAs changed the file type to a macro disabled type workbook. But, since vba is an application of its own, residing in the Windows environment, any active code will continue to work until the new workbook is closed, even if the new workbook is not a .xlsm type. After the SaveAs, there should be two files existing with the same data but different names (and different file types if the user chose that). The on screen file will be the new workbook with the original file safely tucked away on the hard drive or server as applicable. The naming and path designation of the new file can be accomplihed by the user if they use the GetSaveAsFilename method in VBA, but varibles cannot be used in the imput area of the GetSaveAsFilename diaolog box. The user would have to navigate to the appropriate folder using the arrows at the top left of the dialog box and then enter the desired file name in the input area of the dialog box. With all that said, it would probably be easier to have the full name (path and filename) of the new file entered in a cell somewhere (say Range("A1") and just use the cell reference to do a regular SaveAs in VBA.

ActiveWorkbook.SaveAs Range("A1").Value

You could then change all the formulas to values by:

With ActiveWorkbook.UsedRange
    .Value = .Value
End With

Hope this helps/

 

[Melimob]

on saving the file as a new workbook:
The SaveAs method in Excel does create a new workbook. It leaves the old file intact on the hard drive as it was when the workbook was opened, unless the user saves any changes before executing the SaveAs. When doing the SaveAs by code, after the transaction processes the new workbook will inherit any macros that were in the original workbook, unless the SaveAs changed the file type to a macro disabled type workbook. But, since vba is an application of its own, residing in the Windows environment, any active code will continue to work until the new workbook is closed, even if the new workbook is not a .xlsm type. After the SaveAs, there should be two files existing with the same data but different names (and different file types if the user chose that). The on screen file will be the new workbook with the original file safely tucked away on the hard drive or server as applicable. The naming and path designation of the new file can be accomplihed by the user if they use the GetSaveAsFilename method in VBA, but varibles cannot be used in the imput area of the GetSaveAsFilename diaolog box. The user would have to navigate to the appropriate folder using the arrows at the top left of the dialog box and then enter the desired file name in the input area of the dialog box. With all that said, it would probably be easier to have the full name (path and filename) of the new file entered in a cell somewhere (say Range("A1") and just use the cell reference to do a regular SaveAs in VBA.

ActiveWorkbook.SaveAs Range("A1").Value

You could then change all the formulas to values by:

With ActiveWorkbook.UsedRange
    .Value = .Value
End With

Hope this helps/

Hi JLGWhiz

Hugely grateful for the explanation. It really helps.

Just one question, is it possible to at least use the path of where the workbook is currently opened from and then reference the cell for the name?

I.e. combining these two somehow?

ActiveWorkbook.SaveAs Range("A1").Value
ActiveWorkbook.Path

 

[Melimob]

Hi JLGWhiz

Hugely grateful for the explanation. It really helps.

Just one question, is it possible to at least use the path of where the workbook is currently opened from and then reference the cell for the name?

I.e. combining these two somehow?

ActiveWorkbook.SaveAs Range("A1").Value
ActiveWorkbook.Path

I think I found it by searching on internet.. will test now..

Sub SAVEMEAS()
    ThisFile = Range("A1").Value
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & Application.PathSeparator & ThisFile
End Sub

thank you!

 

[JLGWhiz]

I think I found it by searching on internet.. will test now..

Sub SAVEMEAS()
    ThisFile = Range("A1").Value
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & Application.PathSeparator & ThisFile
End Sub

thank you!

It is only necessary to use the path as part of the SaveAs statement if a) the ActiveWorkbook path is different than the default path or b) you want the new workbook saved to a different directory than the ActiveWorkbook. In some cases you might need to create a directory using the MkDir function and then do the SaveAs, in which case you would also include the path in the SaveAs statement. It helps to understand how the directory paths fit into the file structure and how and when to maneuver among them. Otherwise, you might end up 'losing' your files, especially if you are on a LAN and use both your hard drive and the server to store files.

 

[Melimob]

It is only necessary to use the path as part of the SaveAs statement if a) the ActiveWorkbook path is different than the default path or b) you want the new workbook saved to a different directory than the ActiveWorkbook. In some cases you might need to create a directory using the MkDir function and then do the SaveAs, in which case you would also include the path in the SaveAs statement. It helps to understand how the directory paths fit into the file structure and how and when to maneuver among them. Otherwise, you might end up 'losing' your files, especially if you are on a LAN and use both your hard drive and the server to store files.

ok thank you JLGWhiz - useful!

 

[Melimob]

Hi JLGWhiz

So I tried to fix this using record macro and adding it in here however I need to know how I should reference my sheets properly.

So this is the macro which is called in the 'create new workbook' so the actions should apply to the new workbook not the master.

I've commented out what I am trying to achieve within the code:

Sub All_Cells_In_All_WorkSheets_6()
    Dim sh As Worksheet
    
'unhide sheets so that the next actions would work
    Sheets("ImportNonCCB").Visible = True
    Sheets("LKUPs").Visible = True
  
'for all sheets copy and paste special values over itself except for the COD Mapping sheet


    For Each sh In ActiveWorkbook.Worksheets
    If sh.Name <> "COD Mapping" Then
   
      With sh.UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
       End With
       Application.CutCopyMode = False
    End If
Next sh
              
'for the CC Recon.. sheet, place the below formula and copy it down.  Format column center. This is now erroring.  I can live without the formatting if this is whats causing the error?
           
    Sheets("CC Reconfiguration Data").Range("I3") _
    .Formula = "=IFERROR(VLOOKUP([Control Centre Company Builds],Table6[[Control Centre Company Builds]:[POA GDS]],6,0),"")"
    Selection.AutoFill Destination:=Range("tblCCReconfig[POA GDS]")
    Range("tblCCReconfig[POA GDS]").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
' For sheets COD Mapping (in new workbook) - copy and paste special values on specific ranges


   With ActiveWorkbook.Sheets("COD MAPPING")
    Range("D1:BA2").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Application.CutCopyMode = False
    
    Range("A5:BA15").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("D18:BA19").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("A22:BA36").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
    
       'hide below sheets
    Sheets("ImportNonCCB").Visible = False
    Sheets("LKUPs").Visible = False
    Sheets("ImportCCB").Visible = False
    Sheets("ImportCCBMI").Visible = False
    Sheets("Instructions").Delete
    
    
End Sub

Any advice gratefully received.

 

[JLGWhiz]

If you are working in only one workbook you do not need to keep referencing the ActiveWorkbook. Excel knows that with only one workbook open, any sheet reference goes to that workbook, so you just use the sheet names and ranges to navigate through your data. To save code writing space, the sheets can be assigned to variables with meaningfuld names:
First: Declare the variables at the beginning of your code.

Dim imNCCB As Worksheet, imCCB As Worksheet, imCCBMI As Worksheet, lkUP As Worksheet, instSh As Worksheet

Then initialize the varibles with Set statements

Set imNCCB = Sheets("ImportNonCCB")
Set luUP = Sheets("LKUPs")
'same for each of the variables

Then these can be used everywhere in the code that you would normally use the full sheet reference. To further reduce the code writing and avoid using the Activate and Select method which causes the flicker and flash on the monitor, use With...End With method.

With imNCCB
    .Range("A1:D5").Font.Color = vbRed
    .Range("A6:D10").ClearContents
End With

If only one statement per sheet then the with statement is not necessary

imNCCB.Range("A7") = "Hello"

The variable includes the full sheet name and the workbook it belongs to, so when you see it in the code you know which workbook you are dealing with, in case you have more than one workbook open. Then when you set your variable you would use the workbook as part of the set statement.

Set imNCCB = Workbooks(1).Sheets("ImportNonCCB")

All of this will come with time and practice. But getting away from the Activate and Select method will speed up the code and the efficiency of your work. And that is it for lessons in writing code.
Regards, JLG

 

[Melimob]

JLGWhiz - I can't thank you enough for your valuable help.

Not only have I been able to get this done, more importantly, I now understand how to start to write code -well at least the basics so thank you.

Below code works however:

1) I wanted the new workbook to save without macros however? Unsure how to do this but if it has to remain with macros, fine.

Sub SaveAsNewWkbook_New7()
    
    Application.ScreenUpdating = False
    
    RemoveFilters1a
        
    ThisFile = Sheets("Instructions").Range("G1").Value
    ActiveWorkbook.SaveAs FileName:=ActiveWorkbook.Path & Application.PathSeparator & ThisFile
    
    RemoveFormulas6
   
    ActiveWorkbook.Save
    
    Application.ScreenUpdating = True
        
    MsgBox ("Workbook Created. Post to SharePoint & Notify PMs.")
End Sub

2) I couldn't get the 'IfError' version to work on the sheet With ccReco nor could I get it to autofilter down. No biggie but the only piece I couldn't do.

Sub RemoveFormulas6()
'All_Cells_In_All_WorkSheets_6()


Application.ScreenUpdating = False
    


    Dim sh As Worksheet
    
 Dim imNCCB As Worksheet, imCCB As Worksheet, imCCBMI As Worksheet, lkUP As Worksheet, instr As Worksheet, COD As Worksheet


Set imNCCB = Sheets("ImportNonCCB")
Set imCCB = Sheets("ImportCCB")
Set imCCBMI = Sheets("ImportCCBMI")
Set lkUP = Sheets("LKUPs")
Set instr = Sheets("Instructions")
Set ccReco = Sheets("CC Reconfiguration Data")
Set COD = Sheets("COD MAPPING")


imNCCB.Visible = True
lkUP.Visible = True


      
    For Each sh In ActiveWorkbook.Worksheets
    If sh.Name <> "COD Mapping" Then
   
      With sh.UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
       End With
       Application.CutCopyMode = False
    End If
Next sh
              
    With ccReco
    .Range("I3").Formula = "=VLOOKUP(tblCCReconfig[Control Centre Company Builds],Table6[[Control Centre Company Builds]:[POA GDS]],6,0)"
    '.Range("I3").Formula = "=IFERROR(VLOOKUP(tblCCReconfig[Control Centre Company Builds],Table6[[Control Centre Company Builds]:[POA GDS]],6,0),"")"
    '.Range("I3:I").AutoFill Destination:=Range("tblCCReconfig[POA GDS]")
    '.VerticalAlignment = xlCenter
    End With
    
    With COD
    .Range("D1:BA2").Copy
    .Range("D1:BA2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    .Range("A5:BA15").Copy
    .Range("A5:BA15").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    .Range("D18:BA19").Copy
    .Range("D18:BA19").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    .Range("A22:BA36").Copy
    .Range("A22:BA36").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    End With
    
    imNCCB.Visible = False
    lkUP.Visible = False
    instr.Delete
    
    Application.ScreenUpdating = True
    
End Sub

Am so happy! thank you so so much again for all your help and time.!

 

[JLGWhiz]

To save a macro enabled workbook (.xlsm) as a macrodisabled workbook (.xlsx)

Dim myPath As String
myPath = "C:\DiffDir\"  'You only need this if the path is different than your default path.
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs myPath & "newFileName.xlsx", FileFormat:=51
Application.DisplayAlerts = True

The Display alerts bit will prevent the message telling you that you will lose your macros. See "Workbook.SaveAs Method" in VBA help file.