Simple VBA Code help

Rob_010101

Board Regular
Joined
Jul 24, 2017
Messages
198
Office Version
  1. 365
Platform
  1. Windows
Hello,

My excel is a master document that pulls in data from different excel sheets in different departments, each of which are all updated daily. I need to save the "master" excel once a week into a different excel with all the values pasted. This creates a weekly snapshot of the data as it is at the point of saving but leaves the "master" excel with all the formulas, so it keeps updating.

I have tried to use the below assigned to a button however in addition to creating a new document with paste values, it is also pasting values in the "master" document rendering it useless.

VBA Code:
Sub MakeBook()
Dim sh As Worksheet
For Each wb In Sheets
    wb.Activate
    Cells.Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues
Next
With ActiveWorkbook
    s = Replace(.FullName, .Name, Format(Date, "dd-mmm-yy")) & ".xlsm"
End With
ActiveWorkbook.SaveAs Filename:=s
End Sub

It should "Save As" and save into a new excel, as the date, with values pasted. The "master" excel i.e. the sheet being saved from should retain all the formulas.

Hope that makes sense.

Chris
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Something like this perhaps.
VBA Code:
Sub MakeBook()
    Dim sh As Worksheet, wb As Workbook, s As String
  
    With ActiveWorkbook
        s = Replace(.FullName, .Name, Format(Date, "dd-mmm-yy")) & ".xlsm"
        .SaveCopyAs s
    End With  
    Set wb = Workbooks.Open(FileName:=s)
    For Each sh In wb.Worksheets
        sh.UsedRange.Value = sh.UsedRange.Value
    Next sh
    wb.Close Savechanges:=True
    MsgBox "New workbook '" & s & "' has been created. ", vbOKOnly, Application.Name
End Sub
 
Upvote 0
Something like this perhaps.
VBA Code:
Sub MakeBook()
    Dim sh As Worksheet, wb As Workbook, s As String
 
    With ActiveWorkbook
        s = Replace(.FullName, .Name, Format(Date, "dd-mmm-yy")) & ".xlsm"
        .SaveCopyAs s
    End With 
    Set wb = Workbooks.Open(FileName:=s)
    For Each sh In wb.Worksheets
        sh.UsedRange.Value = sh.UsedRange.Value
    Next sh
    wb.Close Savechanges:=True
    MsgBox "New workbook '" & s & "' has been created. ", vbOKOnly, Application.Name
End Sub
1707465987127.png
 
Upvote 0
VBA Code:
Sub MakeBook()
    Dim sh As Worksheet, wb As Workbook, s As String
    
    With ActiveWorkbook
        If .Path = "" And Left(.Name, 4) = "Book" Then
            MsgBox "'" & .Name & "' is a new workbook that has never been saved and thus has no path to build a new filename with. Please run this macro on a workbook that has been" _
            & " previously been saved to disk. ", vbOKOnly Or vbExclamation, Application.Name
            Exit Sub
        End If
        
        s = Replace(.FullName, .Name, Format(Date, "dd-mmm-yy")) & ".xlsm"
        
        For Each wb In Workbooks
            If UCase(wb.FullName) = UCase(s) Then
                MsgBox "Please close workbook '" & wb.Name & "' before proceeding", vbOKOnly Or vbExclamation, Application.Name
                Exit Sub
            End If
        Next wb
        
        Select Case MsgBox("The workbook you have selected for processing is:" & vbCrLf _
                & "" & vbCrLf _
                & "Name: '" & .Name & "'" & vbCrLf _
                & "Path: '" & .Path & "'" & vbCrLf & vbCrLf _
                & "New workbook to be created: '" & s & "'" & vbCrLf & vbCrLf _
                & "Continue?", vbYesNo, Application.Name)
            Case vbNo
                Exit Sub
        End Select
        
        If UCase(s) = UCase(.FullName) Then
            s = Split(s, ".")(0) & "_Copy" & ".xlsm"
        End If
        
        .SaveCopyAs s
        DoEvents
    End With
    
    Set wb = Workbooks.Open(Filename:=s)
    For Each sh In wb.Worksheets
        sh.UsedRange.Value = sh.UsedRange.Value
        DoEvents
    Next sh
    wb.Save
    wb.Close Savechanges:=False
    MsgBox "New workbook '" & s & "' has been created. ", vbOKOnly, Application.Name
End Sub
 
Upvote 0
Hi

I'm still getting

1707724055703.png


The file is on SharePoint and seems to be trying to save to a web address, might this be the issue?

1707724242342.png


1707724590226.png
 
Upvote 0
It is important to always mention that you are using SharePoint file paths. That changes things significantly as not all VBA file functions work with SharePoint 'http' style paths. Runtime error 1004 is specific to a line of code; but your screen shot does not indicate which one. Which line of code produces the runtime error 1004?
 
Upvote 0
It is important to always mention that you are using SharePoint file paths. That changes things significantly as not all VBA file functions work with SharePoint 'http' style paths. Runtime error 1004 is specific to a line of code; but your screen shot does not indicate which one. Which line of code produces the runtime error 1004?
Apologies, I only realised this when you added the helpful message and it showed the full path.

Unfortunately, the editor doesn't highlight the line of code the error applies to.
 
Upvote 0
Unfortunately, the editor doesn't highlight the line of code the error applies to.

In the VBA editor menu, go to the Tools->Options->General tab. Set "Break on All Errors".

1707843474768.png



(Alternatively, move the code out of the Workbook code module into a standard code module that you add).

Re-run the code. When the runtime error occurs, press the 'Debug' button to determine the line of code.
 
Upvote 0
I think the problem has to do with .SaveCopyAs s ,since for whatever reason, MS chose not to modify that function to support SharePoint & OneDrive http style paths. See if this works for you.

VBA Code:
Sub MakeBook()
    Dim sh As Worksheet, wb As Workbook, s As String, ABFilePath As String
    Set wb = ActiveWorkbook
    
    ABFilePath = wb.FullName
    s = Replace(wb.FullName, wb.Name, Format(Date, "dd-mmm-yy")) & ".xlsm"
    
    For Each sh In wb.Worksheets
        sh.UsedRange.Value = sh.UsedRange.Value
    Next sh
    
    wb.SaveAs Filename:=s                       'save to SharePoint filepath
    DoEvents
    MsgBox "New workbook '" & s & "' has been created. ", vbOKOnly, Application.Name
    Workbooks.Open Filename:=ABFilePath         're-open original workbook
    wb.Close Savechanges:=False 'exit
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,929
Messages
6,175,460
Members
452,644
Latest member
gjcase

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top