MACRO: SaveAs Cell Value File Name (Pull Two Cells within Worksheet as File Name)

[foxyginger]

Hello!

I'll try to explain as best I can. I have created a MACRO which when using a button, creates a worksheet out of a form -- then copies the new worksheet and formats it correctly for my purposes creating a new workbook and saves it as "DATA Equity Wire Transfer Request.xlsx" to M:




The MACRO I was using to do this is:




Sub CreateDataFormANDsaveAsXLSX()
'
' CreateDataFormANDsaveAsXLSX Macro
' Copies the information output from 'ETL' and pastes it without formulas to a new workbook with the worksheet name "DATA"; then saves As "DATA Equity transaction request form" to the individuals' M: Drive.
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Sheets("ETL").Select
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Range("C7").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "DATA"
Application.CutCopyMode = False
ChDir "M:\"
ActiveWorkbook.SaveAs Filename:="M:\DATA Equity Wire Transfer Request.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub






Now my question is, how can I change the file name it is saved as so that it will pull from two cells within one of the worksheets?

For example -- M:/Entity Number (Cell D4) Date (D23)_Equity Wire Transfer.xlsx OR M:/z609410 08-01-2017_Equity Wire Transfer.xlsx



This is the MACRO I tried:





Sub CreateDataFormANDsaveAsXLSX()
'
' CreateDataFormANDsaveAsXLSX Macro
' Copies the information output from 'ETL' and pastes it without formulas to a new workbook with the worksheet name "DATA"; then saves As "DATA Equity transaction request form" to the individuals' M: Drive.
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Sheets("ETL").Select
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Range("C7").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "DATA"
Application.CutCopyMode = False
Dim FName As String
Dim FPath As String

FPath = "M:"
FName = Sheets("Equity Wire").Range("D4").Text & "\" & Sheets("Equity Wire").Range("D23").Text
ThisWorkbook.SaveAs Filename:=FPath & "\" & FName


End Sub


But, I received an error message and when debugging it stated the line written in red above was the error.

20037

The file I am saving is a new workbook, but the file name cells I would be using would be from the previous workbook -- For example, the form which has the button is in workbook "ST-Equity transaction request form.xlsm" -- but the new file I would like to save which is created by that button is currently "DATA Equity Wire Transfer Request.xlsx". The cells I need to use for the file name are in Workbook --- "ST-Equity transaction request form.xlsm" on the second worksheet called "Equity Wire" and the cells needed are Entity Number (D4) and Date (D23).

How do I change this line to accomplish my goals, or what other information do you need to be able to help me with this issue?

THANK YOU!

[Paul_Hossler]

1. I added CODE tags - you can use the [#] icon to insert the
... tags and paste your macro

2. It'd help to know EXACTLY what is in D4 and D23

I tried to derive it from


For example -- M:/Entity Number (Cell D4) Date (D23)_Equity Wire Transfer.xlsx OR M:/z609410 08-01-2017_Equity Wire Transfer.xlsx

but I didn't want to guess

[foxyginger]

Thank you, and in cell D4 I would want to pull the property number so it would look like this (z609410) and in cell D23 would be the date the transfer was made (formatted 08-01-2017)-- both of these cells would be input each time before the MACRO is run because it is being input into a form on the second tab named 'Equity Wire'.

[foxyginger]

What I believe the problem to be is that when I run this MACRO it is creating a new workbook and then when I try to SaveAs it needs to pull the file name information from the previous workbook (not the newly created one), is there a way to file path it to the previously opened workbook?

Does that make sense? Even if it could pull the information from the first workbook and add it to the newly created workbook-- use that information to SaveAs and then delete that information (property/entity number and date) off of the new workbook so it doesn't mess up the formatting, that could be another option.

This has got to be confusing considering I am brand-new to VBA trying to explain something that I don't understand to someone who doesn't have a clear reference of what I am talking about.

[Paul_Hossler]

It was only a little confusing :)

When dealing with 2 (or more workbooks) I find it's easier and clearer to be very specific and identify everything very specifically

This is what I used to test -- I forced in some values (like D4 and D23) and used a file part that I had <> M




Option Explicit

Sub CreateDataFormANDsaveAsXLSX()
'
' CreateDataFormANDsaveAsXLSX Macro
' Copies the information output from 'ETL' and pastes it without formulas to a new workbook with the worksheet name "DATA"; then saves As "DATA Equity transaction request form" to the individuals' M: Drive.
'
' Keyboard Shortcut: Ctrl+Shift+R
'

Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim filePath As String, fileName As String, fileExt As String, fileFullname As String

Application.ScreenUpdating = False

Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("ETL")

ws1.Range("D4").Value = "Z609410"
ws1.Range("D23").Value = DateSerial(2017, 8, 1)

ws1.Copy

Set wb2 = ActiveWorkbook
Set ws2 = wb2.Worksheets("ETL")
ws2.Name = "DATA"

ws2.UsedRange.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

filePath = wb1.Path ' or M: without a \
fileName = ws1.Range("D4").Value & "-" & Format(ws1.Range("D23").Value, "mm-dd-yyyy")
fileExt = ".xlsx"

fileFullname = filePath & Application.PathSeparator & "DATA Equity transaction request form" & fileName & fileExt

'just delete it if it's there
On Error Resume Next
Application.DisplayAlerts = False
Kill fileFullname
Application.DisplayAlerts = True
On Error GoTo 0

wb2.SaveAs fileName:=fileFullname, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

ActiveWindow.Close

wb1.Activate

Application.ScreenUpdating = True
End Sub

[foxyginger]

I had to make sure there wasn't anything confidential or anything but, to show what is happening:

This is the form we are using.

20048

This is the second half of the form, with the MACRO button I created.

20049

Once the MACRO button is pushed it pulls the information from the form using VLOOKUPS, etc. to the other tabs and then puts it in the necessary format on this ETL tab. Next it copies this information, opens a new workbook, renames the first sheet of the new workbook "DATA" and uses the 'paste special' function to take the formulas out.

20051

This is the new workbook that is created.

20050

Currently it is saving this new workbook as a fixed name, which means each time it is run it overwrites the last file made (which is okay) BUT I'd like to have the new workbook saved as ____(prop name) _____ (date) fixed name.xlsx and to get the property name and date there it would have to save from the first picture cells.

Does this help?