Free Microsoft Excel 2013 Quick Reference

Save file name using Cell value

I am trying to create a macro which will save the file and take the file name from cell values eg if cell A1 = "488" and Cell B1= "Buyers" when saved the file name would be 488_Buyers.

Can anyone point me in the right direction?

Thanks

Vetequk


Anyone know of a way to create a linked workbook file name using cell
variables.

I have a workbook that will pull in data from a .cvs file generated daily.
The date (yyyymmdd) included in the file name changes each day. Was looking
for a way to grab the date from the master spreadsheet header and build the
..csv file name. Was hoping to not have to manually create a new file name
for each day of the month. Something along the lines "daily_data_(cell
R1).cvs" == daily_data_20050101.cvs

Thanks

RichT

Anyone know of a way to create a linked workbook file name using cell
variables.

I have a workbook that will pull in data from a .cvs file generated daily.
The date (yyyymmdd) included in the file name changes each day. Was looking
for a way to grab the date from the master spreadsheet header and build the
..csv file name. Was hoping to not have to manually create a new file name
for each day of the month. Something along the lines "daily_data_(cell
R1).cvs" == daily_data_20050101.cvs

Thanks

RichT

how do i save a file based on a predetermined value in a cell.

ex. the value in cell B11 contains a date. i want that date to be the file
name that it gets saved as.

regards,

jat jaswal

Good day to all-
I am trying to save a file as a cell value, but need it to add leading zeros, as it needs to be 8-digits (invoice #).
Here is what i am trying to use:

(all is dim'd, snippet of code is here):
Range("A1").select
WorkbookName = ActiveCell.Text
WorkbookName = "00000000" & WorkbookName
WorkbookName = Right(WorkbookName, 8)

and this is the message i get in return:
"Compile error: Wrong number of arguments or invalid property assignment"

what am i missing/not understanding? If cell A1 = "145780", i need it to return "00145780"; if it is "270", I need it to return "00000270"; if it is "80045810" i need it to return "80045810"
thanks-

Hi People

I am a novice at this stuff and have been on this for a big part of the day and it is driving me nuts.

I have a master workbook with 8 worksheets that are exported, as required, in pairs. I have tried to create a macro that will export these (to a new workbook) and autosave them to a directory, with a file name incorporating cell value "A2" of one of the worksheets.

so the example below shows what i have been using, with the two worksheets i'm exporting being "SW Field Sheet" and "EDMS_SW". "SW Field Sheet" contains the cell reference i want included in the output file name.

This worked at one stage, but has not since. Whats wrong?

Is the reference to "SW Field Sheet""A2" unclear?

Sub SWexport()
'
' SWevent Macro
'
' Keyboard Shortcut: Ctrl+s
'
    Range("D4").Select
    Sheets(Array("SW  Field Sheet", "EDMS_SW")).Select
    Sheets("EDMS_SW").Activate
    Sheets(Array("SW  Field Sheet", "EDMS_SW")).Copy
    ActiveWorkbook.SaveAs Filename:= _
        "Z:CLER_50ReportsR_" & ActiveSheet.Range("A2").Value & "_050_SW Field
Results.xls" _
        , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    Windows("FS_100204_50_Field Monitoring Sheet MASTER v5.xlsm").Activate
End Sub
Cheers

Nick

I've looked around for this macro but could not find it.

Im looking for a macro that will save as: File name A1 in tab delimited format.
Code:
Sub Tab_Delimited_Save()
'
' Tab_Delimited_Save Macro
' Macro recorded 9/8/2008 by Admin'
' Keyboard Shortcut: Ctrl+d
'
    ActiveWorkbook.SaveAs Filename:= _
        "C:Documents and SettingsadminDesktopSolano NumberingVBM BT17.txt", _
        FileFormat:=xlText, CreateBackup:=False
    Range("I22").Select
End Sub
That's what I have, but I want the name of the file to be what is in Cell A1 every time in that same folder. Then after I save I want it to say Yes to keeping the file in this format.

In this example A1 for this file was named VBM BT17

I have 200 of these to do.

I am new to VB and excel macros. I am currently writing what is turning out to be quite a complicated macro (though probably mainly because of the way I am doing it ). Part of the macro creates new worksheets based on the value of cells in a list, so i get a one worksheet for each different cell value and the worksheets name=that cells value. However, I would like to be able to refer to those worksheets in formula on other worksheets i.e ="NAME!"B2. The problem is that some of the cells contain values such as N%, so although it will make a worksheet called that I cannot refer to it in a formula. As such I need to know how to do one of three ways (unless anyone can think of another way).

1. Change awkward cell values before I make the worksheets
2. Rename the awkwardly named worksheets after they are made
3. Be able to use the awkward worksheet names in a formula

Please help, there is already smoke coming out of my ears and I fear and mushroom cloud will blossom from the top of my head soon.

Dewey

What I am trying to do, is lookup cells in a different sheet. Using cell values, to make up the name of the sheet I am trying to use.
On sheet 1 the database in cells A1=A A2=B B1=C B2=D (all values being text)
Ok now my 2 sheets besides the database are called A@C and B@D.
Now normally you would use ='A@C' to ref the respective sheet. What I want to do is use the values in cells A1 and B1, in place of the A and C. So in my mind it would look like ='A1@B1' for A@C and ='A2@B2' for B@D. Obviously it isnt quite that simple or I wouldn't have a question. Please any help here would be appreciated and save me many hours.

I have a report which produces apprx. 1500 separate district / store level reports. I am trying to build a macro that will do the following:

1.Run approx 100 district level reports and save each report as the district name, based off a cell value.
2. For each store level report (apprx 1400), open the correct district level report based off a cell value and paste that store a new tab.

I already have the code in place to do the individual store and district reports as a print job. But I am a newbie when it comes to VBA macros. So I am not sure how to get this to loop correctly and do what I need. I have kind of got the first step to work. But have no idea how to complete the second step. Any idea or suggestions would be greatly appreciated.

Thanks,

Shawn Stephens

Hello to everyone, this is my first post on here. I have looked but couldn't find an answer to this.

I have worked as a programmer before so tended to do anything complicated using a program written for the purpose so have only used Excel occasionally. I have started to use it more often now and was trying to create a household budget planner.

I have created a seperate tab for each expense named for example 'mortgage', 'house insurance' etc.

On the first 'master' page I can refer to data on the other pages without problem but, rather than typing the name on a worksheet numerous times, is there a way of using the value of one cell as the name of the reference worksheet. ie

| 8 | 9 | 10
A | mortgage | =mortgage!$B$4 | =mortgage!$B$6

The column 8 has the name of the worksheet in it and I wanted to replace the word 'mortgage' in the reference of A9 and A10. This will make errors less likely and updating/adding etc easier.

Something like a9 being =(A8)!$B$4 <<<<-- This obviously doesn't work

Thanks in anticipation...

I need to link to a different workbook but using cell values.

For example, in the attached file in cell B4 it looks like this:

='C:Documents and SettingsAdministratorDesktop[2508 Howell Jan-Apr 2008.xls]Week 9'!$D$20

I need a way to change Week 9 in the above formula using a validation list.

I would also like to know how to specify the above workbook referenced using a cell as well, for instance, C:Documents and SettingsAdministratorDesktop[2508 Howell Jan-Apr 2008.xls] would be the contents of a cell or the ability to click on a cell and have it bring up an open dialog box to browse to the file and input it's path into that cell.

Hi
I want to modified the code in this tread to include the number in cell A1.

	VB:
	
ActiveWorkbook.SaveAs "Report" & Format(Now, "yyyymmdd") & ".xls" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So the file name will be Report_"A1"_20051018.xls

Rune

I have been pulling data from the New Web Query command, but its kinda slow. If I could change the URL using cell values it would be much faster.
The macro I would use is
Sub Macro2()
'
'
' Macro recorded 3/19/2007 by DavalryTwo
'
' Keyboard Shortcut: Ctrl+b
'
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.nba.com/games/20061213/BOSPHI/playbyplay.html", Destination:= _
        Range("A1"))
        .Name = "playbyplay"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "4"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub
So basically in the "URL;http://www.nba.com/games/20061213/BOSPHI/playbyplay.html", Destination:= _
I would want to change the date 20061213 to a cell value and same with BOSPHI. Can anyone help me with the coding or let me know if this is possible to do. Thanks in advance guys.

I need a macro when i click the "Save" button it will save the excel sheet in certain path i specify with a cell value as the attached excel in "C4" , Copy paste formula as value including the table .

Appreciate your help .

Hello everyone. I am coming to you for extreme help. I have been googling for days now and can't seem to find the answer.

Background. My company is going through a major project by converting 10,000 customers to a new system and we need to fill out a form for each member. My project consists of 3 parts, which I will outline here to see if anyone at all can help me.

1st - i have a form already created for this first part. After the form is created, i wish to have a button on the form that executes a Macro. This Macro will...

Auto save the file as a pdf with the file name as 'Cell contents of C5' in the form of a PDF. However, it needs to go to a specific folder on a network drive of "L:Order processILEC queue" BUT!!! At this location, I need to create a sub folder labeled "FC - 'cell contents of C5(minus comma)' & 'cell contents from G5'

FYI - C5 is the customer name as 'Last, First' If needed to simplify this macro, I could seperate it out, but would rather not.
G5 will be the customer ID number.

That is the first step.

The second step...
I can create the form that auto fills from sheet 1 to sheet 2. But i will then just need to PRINT sheet 2. (there may be some additional data I will need to enter on sheet 2, so the macro button could be on sheet 1 or 2.)

The third step - if its even possible...

For scheduling these installations, my company utilzes about 6 technician calendars that are on a microsoft exchange server. I would LOVE for a macro that auto creates a calendar event based on specific items in the worksheet. However, I am unsure if this can be done by clicking on a check box of which calendar it stores on or a drop down, or if IS possible...
I would be happy to elaborate on this step if someone thinks its possible, but i am leaving it vague for now since I do not think it is possible.

If ANYONE is able to assist me, it would be so gratefully appreciated!

Thank you!

Hi

Need way how to run macro which name is cell value
Example :

Click on button (or any shape) will run macro which name is in cell A1

How can I use cell values in an equation?

For example in one cell is the number 1 and the next cell is the number 10. Let's assume this input means I want to get the sum of all cells in column A from row 1 to 10.

How can I create a formula that is able to form an equivalent of "SUM($A1:$A10)" by using the 1 and 10 values found int he sheet. If, instead the values were 5 and 50, I would be looking for "SUM($A5:$A50)".

Anybody have any insights on whether or not this is possible in Excel 2003? I know how to reference other cells, but not how to use those values in formulas.

Thanks.

Hello,

i try to list all sheet name and cell value but i don't know how
i include sample workbook before and after

thanks for your help

Regard
Sino

Hi,

I am trying to save a workbook by using the value in cell A1 as part of the file name. The file name should be "ORDERS" followed by the date found in A1.

The following code is doing the job except that it also replaces the active sheet name with the file name. I do not want to change the name of the sheet and am not sure why this is happening.

Code:
Thanks,
Paula

When you click File>Save As, you get a save as window that shows the current file name to "Save As"

I would like to create a macro that shows the values in a couple of cells (lets use Range("A1") & Range("B2")) instead of the current file name.

I am trying to populate a quote log based on one cell entry. The cell that would have the entry would be a file name such as 12345 (without directory, path, and file type).
Currently, my worksheet is set up so that column A would have my file name and the remaining columns contain information "collected" for specific cells in the file referenced by column A. Those items are things such as a date, customer name, etc...
I need this link to update whether the files are opened or not. Here is what I thought would be my link... ='myservermyshareTRI["A2"]&.xls&quote'!B5. This gives me a #ref. Please help.

The actual name of the file contains the TRI but only the numbers would be entered into the spreadsheet for a reference to the file name. I have a working hyperlink that takes me to the document using the cell entry. I just cannot get any values from the linked document like I want.

I need this becuase my supervisor is always creating quotes and does not want to take the time to enter all of the information in a log. If he enters the quote number at least, I am hoping to have the rest done automatically.

hi,

I want to use a cell from another sheet as my save file name. This is the code i have been using to save within the existing sheet

ThisFile = Range("A4").Value
    ActiveWorkbook.SaveAs FileName:=ThisFile
Thanks in advance

Simon Green

I'm trying to create a macro that will save a copy of one sheet in a workbook and use a value from a cell within that sheet as the filename...all without user input. Basically what I'm trying to do is create a backup copy of each printed page of a read-only file.

In cell C5, I have my users "Full Name" in Cell H5, I have my user "SSN" I would like to use VBA to Save to a file name that combines the two cell as follows

Cell C5 = John Moore Cell H5 = 222-33-4444 I want to save as " John Moore - 4444" (using only the last 4 of the SSN) if possible I would like the save as filename to be Moore -4444 (Last Name and Last 4 of SSN but this may be to difficult since the first and last name lenght will vary. I currently using the follow code that work but it save as the full Name and the entire SSN "John Moore - 222-333-4444. I don't understand what I need to do to modify the code to get the file name to be

1 John Moore - 4444 or
2 Moore - 4444

Here's the current code i"m using

Private Sub CommandButton1_Click()
'Sub CommandButton1_Click()
        
Dim flPath As String
flPath = "C:Budget"
flPath = flPath & Format([C5], "Full Name") & " - " & [H5] & ".xls"
flPath = Application.GetSaveAsFilename(flPath, "Microsoft Office Excel Workbook (*.xls), *.xls")
If flPath <> "False" Then ThisWorkbook.SaveAs flPath
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

End Sub

I would be grateful for any help