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

 

[allExcell]

Hi Wigi,

I tried the way you said but couldn't ascertain what is wrong with it. It's opening the Template file and copy pasting data in the tabs required, finally at the time of saving it's getting stopped. Earlier when I tried saving the file by generic name it was working fine. The extra portion that I added to segregate each file with their owner's name by showing a reference cell to pickup teh name from it started throwing error.

The error message which I'm getting is as follows:

Run Time Error: 1004
The file could not be accessed. Try one of the following:
* Make sure the specified folder exists.
* Make sure the folder that containsthe file is not read-only.
* Make sure the file name doesn't contain any of the following characters: < > ? []r *
* Make sure the file/path name doesn't contain more than 218

Here, I've already made necessary checks, file is opening and data is being copied, so there is no question about path not being defined properly or the file being a read only one. There is no special characters in it and the count is definitely less than 218. With my level of knowledge I'm going no where. Please help.

 

[wigi]

Please make sure that it absolutely works fine with hardcoded filename and path.
Do not change anything else in the current code, only change the dynamic filename with the hardcoded one.
When that works, factor in the dynamic part.
Use a message box (MsgBox ...) to make sure you grab the correct data from the sheet and cell.
There's nothing else I can do and that I would do to make it work.

 

[allExcell]

Hi All,

Can anyone help me into this? I'm not able to fix my code. If there is any other way of doing it please assist.

Thanks in advance.

 

[wigi]

As stated above:

1. post your code where the path and file is hardcoded, and which works
2. then, post your code with the dynamic file reference in (so, workbook.worksheet.range reference) and which might not be working.

That should be sufficient information to get it working. Remember that you can see your screen and hard drive and network, the helpers cannot see that.

 

[allExcell]

Hi Wigi,

Please see below my code after hardcoding and it works absolutely fine.

Sub Copy_NewBook()
    Dim sPath As String
    Dim twbk As Workbook
    Dim oWbK As Workbook
    Dim Sh  As Variant
    sPath = "C:\Users\xxxx\Desktop\Report_Automation\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
  

10    oWbK.SaveAs FileName:="C:\Users\xxxx\Desktop\Report_Automation\Uploads\Report_" & "[B]Name[/B]" & "_" & Format(Now(), "yyyymmdd"), FileFormat:=51, Password:="", _
        WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
        
''10            oWbK.SaveAs FileName:="C:\Users\xxxx\Desktop\Report_Automation\Uploads\Report_" & [B]Range("A1") [/B]& "_" & Format(Now(), "yyyymmdd"), FileFormat:=51, Password:="", _
''        WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    oWbK.Close saveChanges:=False
''    MsgBox ("Report Generated !!!")
End Sub

Once I tried replacing hardcoded value "Name" with "Range("A1")" and ran it step by step pressing F8 and found that Range("A1") is showing Range("A1") = "DataSource:" but not showing the actual value.
But to my utter surprize, when I went back and checked the reference cell value by clicking on it I found that in my code, instead of showing "DataSource:", it is showing the exact name as Range("A1") = "abc". Any guess??

 

[wigi]

Range("A1") should be qualified: what workbook and what worksheet is it?

That's why I wrote above: dynamic file reference in (so, workbook.worksheet.range reference)

Without qualifications, Range("A1") refers to the active sheet on the active workbook.

 

[allExcell]

Hi Wigi,

It should be twbk.Worksheets("CC").Range("N2")

 

[wigi]

Then please insert it into the SaveAs command.

To make sure it's correct, do:

MsgBox twbk.Worksheets("CC").Range("N2")[B]

[/B]just in front of the SaveAs command.

 

[allExcell]

As you said, if I keep it like in the below code, it's working fine.

oWbK.SaveAs FileName:=twbk.Worksheets("CC").Range("N2"), FileFormat:=51, Password:="", _
        WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

 

[wigi]

As you said, if I keep it like in the below code, it's working fine.

oWbK.SaveAs FileName:=twbk.Worksheets("CC").Range("N2"), FileFormat:=51, Password:="", _
        WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

Then, what is the issue? Clearly, cell N2 at that sheet contains the path and the filename and the extension. IF not, it would not work.
Are you now saying that if you change the contents of cell N2 but the path is hardcoded in the SaveAs command, it does not work anymore?