Quick VBA Assist, saving to cell reference & as values.

[sanantonio]

Hi All,

I've a macro I'm borrowing from another macro of mine. The current macro copies a specific worksheet within the macro enabled workbook to a new workbook and saves that workbook.

Sub step6()
 

Dim theNewWorkbook As Workbook
Dim currentWorkbook As Workbook

'currentWorkbook is the source workbook, create a new workbook referencing to it with theNewWorkbook
Set currentWorkbook = ActiveWorkbook
Set theNewWorkbook = Workbooks.Add

'do the copy (it's better to check if there is already a 'Worksheet 1' in the new workbook. It it exists delete it or rename it
currentWorkbook.Worksheets("Output").Copy before:=theNewWorkbook.Sheets(1)

'Remove default sheets in order to have only the copied sheet inside the new workbook
Application.DisplayAlerts = False
Dim i As Integer
For i = theNewWorkbook.Sheets.Count To 2 Step -1
    theNewWorkbook.Sheets(i).Delete
Next i
Application.DisplayAlerts = True

'Save File as XLSM
saveLocation = "C;\C_Drive\Data_for_" & Format(Date, "mmdd") & ".xlsm"
theNewWorkbook.SaveAs Filename:=saveLocation, FileFormat:=XlFileFormat.xlOpenXMLWorkbookMacroEnabled
theNewWorkbook.Close


 
End Sub

What I ideally need it to do is have the "Save location" and name reference a cell.

And then currently it copies the worksheet over like a ctrl+v and maintains all formulas etc and takes across the hidden columns. How can the code be changed so that it just copies and pastes values? So that the hidden columns and formulas don't follow it across?

Thanks in advance!

 

[DanteAmor]

Try this:

Sub step6()
  Dim theNewWorkbook As Workbook
  Dim currentWorkbook As Workbook
  Dim sPath As String, sName As String
  
  'currentWorkbook is the source workbook, create a new workbook referencing to it with theNewWorkbook
  Set currentWorkbook = ActiveWorkbook
  With currentWorkbook.Worksheets("Output")
    sPath = .Range("A2").Value      ' Save location
    sName = .Range("A3").Value      ' name reference a cell.
    .Copy
  End With
  Set theNewWorkbook = ActiveWorkbook
  
  'Save File as XLSM
  With theNewWorkbook
    .Sheets(1).Cells.EntireColumn.Hidden = False
    .Sheets(1).Cells.Copy
    .Sheets(1).Range("A1").PasteSpecial xlValues
    .SaveAs sPath & sName & ".xlsm", XlFileFormat.xlOpenXMLWorkbookMacroEnabled
    .Close
  End With
End Sub