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]

Hi Fluff,

Sure I'll PM you.


Thank you,

 

[Fluff]

Please do not PM the link, it needs to be posted to this thread in accordance with the forum rules.
Cheers

 

[TTUK]

Hello Fluff,

Here is the link to the spreadsheet in question. Thanks!

https://1drv.ms/x/s!AvSvv_qkPI2QhRA1t-azW3yEclrR

 

[Fluff]

Thanks for the file
Change this line as shown

ActiveWorkbook.SaveAs Range("A1").Value & Range("B1").Value [COLOR=#ff0000]& ".xls"[/COLOR], 56

and that worked for me.
However this will not save in the Air 501 etc folder. To do that you'll need to change IIR_temp B2 to look at IIR_Data A6 rather than A10.

 

[TTUK]

Hi Fluff,

I'm still getting the error, when I click to RUN the option of creating the IIR_temp it opens up a new Worksheet (Book1) for example, then an error appear of:

Run-time error '9'
Subscript out of range

I select Debug and shows the following code with the red text highlighted in yellow:

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
    
[COLOR=#ff0000]    With Worksheets("Register")[/COLOR]
        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

Thanks,

 

[Fluff]

When you say

 when I click to RUN the option of creating the IIR_temp

Do you mean when you select a cell in col AY on sheet Register, or are you running the macro some otherway?

 

[TTUK]

When you say

 when I click to RUN the option of creating the IIR_temp

Do you mean when you select a cell in col AY on sheet Register, or are you running the macro some otherway?

Sorry I mean when I select the cell in AY which I require to have the follow-up form created for I get the error above.

Thank you,

 

[Fluff]

Not sure why you get that, as it's working ok in the workbook you supplied, but try this

    With [COLOR=#ff0000]ThisWorkbook[/COLOR].Worksheets("Register")

 

[TTUK]

Not sure why you get that, as it's working ok in the workbook you supplied, but try this

    With [COLOR=#ff0000]ThisWorkbook[/COLOR].Worksheets("Register")

YES! Fluff you are brilliant - this is now working completely as intended!

Many thanks, have a good rest of your day!

Regards,

 

[Fluff]

Glad to help & thanks for the feedback