Need help creating a marco please

[myakymiv]

Hello, I need help making a macro for my excel sheet: I need a macro that saves a document into a certain folder but when it saves the file it needs to have all the cells be saved as "Values(v)" because currently have the cells get as formulas and other macro wont run cells if they have formulas in them.

Thank you for all your help!

 

[Logit]

Sub copyAll()


Dim Output As Workbook, Source As Workbook
Dim sh As Worksheet
Dim FileName As String
Dim firstCell


Application.ScreenUpdating = False
Set Source = ActiveWorkbook


Set Output = Workbooks.Add
Application.DisplayAlerts = False


Dim i As Integer


ThisWorkbook.Save


For Each sh In Source.Worksheets


    Dim newSheet As Worksheet


    ' select all used cells in the source sheet:
    sh.Activate
    sh.UsedRange.Select
    Application.CutCopyMode = False
    Selection.Copy


    ' create new destination sheet:
    Set newSheet = Output.Worksheets.Add(after:=Output.Worksheets(Output.Worksheets.Count))
    newSheet.Name = sh.Name


    ' make sure the destination sheet is selected with the right cell:
    newSheet.Activate
    firstCell = sh.UsedRange.Cells(1, 1).Address
    newSheet.Range(firstCell).Select


    ' paste the values:
    Range(firstCell).PasteSpecial Paste:=xlPasteColumnWidths
    Range(firstCell).PasteSpecial Paste:=xlPasteFormats
    Range(firstCell).PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=True, Transpose:=False


Next


' delete the sheets that were originally there
While Output.Sheets.Count > Source.Worksheets.Count
  Output.Sheets(1).Delete
Wend
FileName = "Testing WB"
Output.SaveAs "C:\Users\My\Desktop\New folder\" & FileName  '<--- edit path as needed


Workbooks("Testing WB.xlsx").Close  '<--- edit name of workbook


'.Close SaveChanges:=True
Application.ScreenUpdating = True


End Sub

 

[myakymiv]

Thank you so much, this works perfectly!

 

[Logit]

.
You are welcome.

 

[myakymiv]

I have a couple of questions, when I put in the file path I want the new file to be saved it just saves straight to my desktop, and also how would I make it so that the original file doesn't save but it stays as a "template"

 

[Logit]

.

Option Explicit


Sub copyAll()


Dim Output As Workbook, Source As Workbook
Dim sh As Worksheet
Dim FileName As String
Dim firstCell


Application.ScreenUpdating = False
Set Source = ActiveWorkbook


Set Output = Workbooks.Add
Application.DisplayAlerts = False


Dim i As Integer


For Each sh In Source.Worksheets


    Dim newSheet As Worksheet


    ' select all used cells in the source sheet:
    sh.Activate
    sh.UsedRange.Select
    Application.CutCopyMode = False
    Selection.Copy


    ' create new destination sheet:
    Set newSheet = Output.Worksheets.Add(after:=Output.Worksheets(Output.Worksheets.Count))
    newSheet.Name = sh.Name


    ' make sure the destination sheet is selected with the right cell:
    newSheet.Activate
    firstCell = sh.UsedRange.Cells(1, 1).Address
    newSheet.Range(firstCell).Select


    ' paste the values:
    Range(firstCell).PasteSpecial Paste:=xlPasteColumnWidths
    Range(firstCell).PasteSpecial Paste:=xlPasteFormats
    Range(firstCell).PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=True, Transpose:=False


Next


' delete the sheets that were originally there
While Output.Sheets.Count > Source.Worksheets.Count
  Output.Sheets(1).Delete
Wend
FileName = "Testing WB"
Output.SaveAs Environ("USERPROFILE") & "\Desktop\" & FileName '<--- edit path as needed


Workbooks("Testing WB.xlsx").Close  '<--- edit name of workbook


Application.DisplayAlerts = False
Application.Quit
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True


'.Close SaveChanges:=True
Application.ScreenUpdating = True


End Sub

 

[myakymiv]

.

Option Explicit


Sub copyAll()


Dim Output As Workbook, Source As Workbook
Dim sh As Worksheet
Dim FileName As String
Dim firstCell


Application.ScreenUpdating = False
Set Source = ActiveWorkbook


Set Output = Workbooks.Add
Application.DisplayAlerts = False


Dim i As Integer


For Each sh In Source.Worksheets


    Dim newSheet As Worksheet


    ' select all used cells in the source sheet:
    sh.Activate
    sh.UsedRange.Select
    Application.CutCopyMode = False
    Selection.Copy


    ' create new destination sheet:
    Set newSheet = Output.Worksheets.Add(after:=Output.Worksheets(Output.Worksheets.Count))
    newSheet.Name = sh.Name


    ' make sure the destination sheet is selected with the right cell:
    newSheet.Activate
    firstCell = sh.UsedRange.Cells(1, 1).Address
    newSheet.Range(firstCell).Select


    ' paste the values:
    Range(firstCell).PasteSpecial Paste:=xlPasteColumnWidths
    Range(firstCell).PasteSpecial Paste:=xlPasteFormats
    Range(firstCell).PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=True, Transpose:=False


Next


' delete the sheets that were originally there
While Output.Sheets.Count > Source.Worksheets.Count
  Output.Sheets(1).Delete
Wend
FileName = "Testing WB"
Output.SaveAs Environ("USERPROFILE") & "\Desktop\" & FileName '<--- edit path as needed


Workbooks("Testing WB.xlsx").Close  '<--- edit name of workbook


Application.DisplayAlerts = False
Application.Quit
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True


'.Close SaveChanges:=True
Application.ScreenUpdating = True


End Sub

I appreciate all the help, but every time I put the file patch in I get and error, this is what im typing. am I doing something wrong?

Output.SaveAs Environ("USERPROFILE") & "C:\Users\XBBL28N\Desktop\Test MSIM" & FileName '<--- edit path as needed

 

[Logit]

.
Sorry .... delete the comment about editing the path.

Simply use : Output.SaveAs Environ("USERPROFILE") & "\Desktop" & FileName

That will place the file on your desktop.

 

[myakymiv]

okay so I ran into an issue with that macro, after I run the macro, instead of just that one excel sheet closing all the excel sheets that I have open close and then I have to reopen them all, is there a way that just that one excel sheet that I need closes maybe by name or something?

 

[Logit]

.
Either comment out or delete these lines of code, toward the bottom of the macro :

'Application.DisplayAlerts = False
'Application.Quit
'ActiveWorkbook.Close SaveChanges:=False
'Application.DisplayAlerts = True




'.Close SaveChanges:=True