Save WorkSheet as per Cell VALUE (keep original open, and do not open new)

[TTUK]

Hello all,

I am trying to achieve the following within VBA.

1. Save specific worksheet (IIR_temp) to a location shown in the cell in A1 on that page, which is a reference from another sheet =IIR_data!5 which looks like (\\server\report\test.xls)
2. Then have the sheet just be saved, and not to override the current spreadsheet which is open


At present this is what I have:

ActiveSheet.Copy ' Copies active sheet to a new workbook
ActiveSheet.SaveAs Range("A1").Value, xlOpenXMLWorkbookMacroEnabled


I just don't know how to get the formula to save the certain sheet in the specified location shown in A1, and allow me to return as I was in the original spreadsheet.

Hope someone can help me out on this.

Thank you!

 

[TTUK]

A1 is =IIR_data!A12
B1 is =IIR_data!A10

The A1 is the file location, and B1 is the file name.

Thank you,

 

[Fluff]

What the actual values, rather than the formulae?

 

[TTUK]

Hello Fluff,

For A1 is it = ="\\dataserver\ManagementSystem\Health & Safety Management System (HS)\Accident Incident Register (AIR)"
For B1 it is = =A8&B8&C8&D8&E8&F8

B1 is a combination of cells in a register that has been select and it reads (for example) - 05.01.2018_Incident Investigation Report

Thank you

 

[Fluff]

You're missing the final \ from the path name
try

ActiveSheet.Copy ' Copies active sheet to a new workbook
ActiveWorkbook.SaveAs Range("A1").Value & "\" & Range("B1").value, 56
ActiveWorkbook.Close

 

[TTUK]

Hello Fluff,

Apologies I copied the information incorrectly, the link in A1 does link to the file path wit hthe "" in.
="\\dataserver\ManagementSystem\Health & Safety Management System (HS)\Accident Incident Register (AIR)"

It still does not work unfortunately.

 

[Fluff]

Double check that there are spelling mistakes & leading/trailing spaces.
Also try this

ActiveSheet.Copy ' Copies active sheet to a new workbook
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Range("A1").Value & Range("B1").Value, 56
ActiveWorkbook.Close
Application.DisplayAlerts = True

 

[TTUK]

It takes the sheet and creates it as a new workbook (Book3) but doesn't have the file name nor has it saved in the location.

However this does throw up an error with this bit of code:

Public Sub Create_Links()

    Dim lastRow As Long
    Dim cell As Range
    
    'Create hyperlinks in column AW for each cell in column AW starting at AW8
    
    With Worksheets("Register")
        lastRow = .Cells(Rows.Count, "B").End(xlUp).Row
        For Each cell In .Range("A8:A" & lastRow)
            .Hyperlinks.Add anchor:=.Cells(cell.Row, "AW"), Address:="", TextToDisplay:="Create " & cell.Value & " folder"
        Next
    End With
    
End Sub

The above code allows when a selection is made in cell B on the sheet 'Register' it will create the 'Create (name) folder' here and allow me to automatically create a folder on our server based on the name/ID given.
I think Book 3 is looking for this piece of code perhaps? Not really sure what is going on!

Seems to be conflicting in some way?!

I really do appreciate all the support you have provided thus far!

 

[Fluff]

Could you supply the complete macro you are using that contains the saveas?

 

[TTUK]

Could you supply the complete macro you are using that contains the saveas?

Hello Fluff,

Here is the macro where I want to contain the SaveAs function of the worksheet.

A user can click on a specific line where the text says 'Raise Follow-up Form' it then opens a dialogue box, whereby the user selects 'Yes' it then copies data to be used in the IIR_temp worksheet.
I then need this worksheet to save like we have discussed.

Sub Followup_Confirm()     
    Dim Msg As String, Ans As Variant
     
    Msg = "Confirm you would like to raise a Follow-up form"
     
    Ans = MsgBox(Msg, vbYesNo)
     
    Select Case Ans
         
    Case vbYes
                        
        ' Copy date and paste in cell on row
        ActiveCell.FormulaR1C1 = "Form Raised"
        ActiveCell.Offset(0, 1).Select
        ActiveCell.FormulaR1C1 = "=R4C52"
        
        Selection.Copy
        
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        
        ' Select the worksheet that is to be saved
        Sheets("IIR_temp").Select
        
        ActiveSheet.Copy ' Copies active sheet to a new workbook
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs Range("A1").Value & Range("B1").Value, 56
        ActiveWorkbook.Close
        Application.DisplayAlerts = True
         
    Case vbNo
GoTo Quit:
    End Select
     
Quit:
         
End Sub

Thanks,

 

[Fluff]

What sheet is active when you run that macro?