Need VBA help

mduntley

Board Regular
Joined
May 23, 2015
Messages
134
Office Version
  1. 365
Platform
  1. Windows
I am just wondering how this is possible. I am working on a project for work to make things automatic, but I am having a struggle to find a way to have the VBA to save it as a name that is on the form. I have show the formula in D2 that is in D3. I want the file name to be what is in D3, but it will be on a xmlx as this vba will live in a different workbook.

abcd
1NameTomato
2Product NumberProduct 1234=CONCATENATE(B2," ",B1,TEXT(TODAY(),"yyyy-mm-dd"))


3 Product 1234 Tomato2019-07-03

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 
Last edited:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Which workbook is Active at the time of the save?
 
Upvote 0
I have the VBA currently open a excel document to paste the data into and i want that document that was open to be saved.
 
Upvote 0
I don't need to know that you have the VBE open or what workbooks are open.
I need to know which workbook is active because if it isn't the one that you are saving then we need to know the workbook name.
 
Last edited:
Upvote 0
How the VBA is set up, the active workbook that the VBA is on is the workbook need to be saved.

if this is helpful, this is my vba so far,

Rich (BB code):
Sub PreferenceCard()
    Range("A6").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Workbooks.Open Filename:= _
        "C:\Users\name\Desktop\FileName.xlsx", _
        UpdateLinks:=0
    Range("A6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("OR Level Sheet").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C7").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("A2").Select
End Sub
 
Upvote 0
So just to be clear, the code is in the workbook being saved and the cell D3 is in a workbook called "FileName" on a sheet called "OR Level Sheet".
Correct?
If FileName is not the real name of the workbook please either post it or let us know that you are aware that you will need to change it as it has to be in the code we write.
 
Upvote 0
I am aware that I need to change the red text to match with the file and the document. I have removed it for company reasons.
 
Upvote 0
Maybe..

Code:
    Dim fName As String
    fName = Workbooks("Filename").Sheets("TheSheetName").Range("D3").Value

    ActiveWorkbook.SaveAs Filename:="C:\Users\name\Desktop\" & fName & ".xlsm", _
                          FileFormat:=52, CreateBackup:=False
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top