Facing issue with my Code to save files with name from reference

[allExcell]

Hi All,

I need your help in fixing the following code, not sure why it's not working the way I want it to be. Please see below for more details:

In the following code I'm trying to save files with specific file name and the value has to be taken from Sheet1, cell N2 of the Original file.
Can you please let me know why it's not happening and guide me accordingly? I'm badly in need of your help.

Sub Copy_NewBook()
    Dim sPath As String
    Dim twbk As Workbook
    Dim oWbK As Workbook
    Dim Sh  As Variant
Dim FName As String
Dim FPath As String
 
    sPath = "C:\Users\xxxx\Desktop\Reports\Template.xlsx"
    Set twbk = ThisWorkbook
    Set oWbK = Workbooks.Open(sPath)
    
    
        For Each Sh In twbk.Worksheets
        If Sh.Name <> "CC" Then
     GoTo 12
        Else
        GoTo 10
    End If
12        Sh.Range("B5", Sh.Range("B5").End(xlToRight).End(xlDown)).Copy
        oWbK.Worksheets(Sh.Name).Range("B3").PasteSpecial Paste:=xlPasteValues
        Next Sh
     
'File name has to be with file Owner's name, which is to be taken from below address, i.e. Sheet1, N2 cell ref.
FName = twbk.Sheets("sheet1").Range("N2").Value
FPath = "C:\Users\xxxx\Desktop\Reports\Uploads\"

10    oWbK.SaveAs FileName:=FPath & FName & "_" & Format(Now(), "yyyymmdd  hhmm AMPM"), FileFormat:=51, Password:="", _
        WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
      
    oWbK.Close saveChanges:=False
'    MsgBox ("Report Generated !!!")
    
End Sub

 

[wigi]

I would add & ".xlsx" in the SaveAs command, as the last part of the filename.
If this would not help, use F8 to step through the code and inspect the contents of the variables.

 

[allExcell]

Hi Wigi,

I'm not good in coding and still in the process of learning. I made few changes in the code as follows:

Dim oWbK As Workbook
Dim ws1 As Worksheets
Set ws1 = oWbK.Sheets("Sheet1") 'This is direct the cell ref. to Sheet1.N2//Else if I give N2 alone as range it picks up a wrong value from from active 'sheet

Finally at the end, i wanted to refer to sheet1.N2 for teh value to set the file name.

10    oWbK.SaveAs FileName:="C:\Users\xxxx\Desktop\Reports\Uploads\xx_" & ws1.Range("N2").Value & "_" & Format(Now(), "yyyymmdd"), FileFormat:=52, Password:="", _
        WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

but, this way I'm getting Run time Error 91 as Obj var or With Block Varnot set, highlighting teh above given code.

 

[wigi]

What are the exact contents of ws1.Range("N2").Value ?

 

[allExcell]

Hi wigi, it has the names of the owners, so the files would be saved with their name along with Date.

 

[wigi]

Did you already try to add the ".xlsx" at the end of the filename ?

 

[allExcell]

Hi Wigi, there is a small correction, please see below:

Dim ws1 As Worksheets
    Set ws1 = twbk.Sheets("Sheet1")

twbk is the workbook I'm copying from.

For .xlsx thing, I've tried something, please refer to my below codes for more details:

Sub Copy_NewBook()
    Dim sPath As String
    Dim twbk As Workbook
    Dim oWbK As Workbook
    Dim Sh  As Variant
    
    sPath = "C:\Users\xxxx\Desktop\Reports\Template.xlsx"
    Set twbk = ThisWorkbook
    Set oWbK = Workbooks.Open(sPath)
    
    
        For Each Sh In twbk.Worksheets
        If Sh.Name <> "CC" Then
     GoTo 12
        Else
        GoTo 10
    End If
12        Sh.Range("B5", Sh.Range("B5").End(xlToRight).End(xlDown)).Copy
        oWbK.Worksheets(Sh.Name).Range("B3").PasteSpecial Paste:=xlPasteValues
        Next Sh
     
    Dim strCellREF2Txt As String
    Dim strFILEname As String
    strCellREF2Txt = twbk.Sheets("Sheet1").Range("N2").Value
    strFILEname = "C:\Users\xxxx\Desktop\Reports\Uploads\xx_" & strCellREF2Txt & ".XLSX"
    Application.DisplayAlerts = False
[B]10    ActiveWorkbook.SaveAs FileName:=strFILEname, _
        FileFormat:=xlExcel8, CreateBackup:=False
[/B] Application.DisplayAlerts = True
End Sub

Here I'm getting runtime error: 1004, while it's highlighting bold lines.

 

[wigi]

In this topic, you already used 3 types of FileFormat's in the SaveAs command: 51, 52, xlExcel8 (56).
Which one do you need?

 

[allExcell]

I'm ok with 51. I was trying with all sorts.

 

[wigi]

Then put the 51 in the code and do the tests.
If it does not work, use F8 to step through the code, line by line. See what happens (or does not happen).