foxyginger
08-09-2017, 09:17 AM
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!
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!