Macros to save worksheet based on cell reference

J

JimmiOO

Board Regular
Joined
Jan 23, 2009
Messages
51
Hi

Could someone please help me with this? I have a macros ( code inserted ) as you can see in my code the macros save the workbook in any file name you chose just by changing the any filename option.

I would like this to be changed so that it saves based on a cell reference, say i has a name in lets say B10 i would like it to save as the name in B10.

Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayAlerts = False
    Dim bk1 As Workbook
    Dim bk As Workbook
    Dim myfilename As String
    myfilename = "C:\files\" & "[B]any filename[/B] - " & Range("J10") & ".xls"
    Set bk = ActiveWorkbook
    Set bk1 = Workbooks.Add(Template:=xlWBATWorksheet)
    bk1.Worksheets(1).Name = "Sheet1"
    bk.Worksheets("Sheet1").Cells.Copy _
            bk1.Worksheets("Sheet1").Cells
    On Error GoTo 0
    bk1.SaveAs Filename:=myfilename, _
               FileFormat:=xlNormal, _
               Password:="", _
               WriteResPassword:="", _
               ReadOnlyRecommended:=False, _
               CreateBackup:=False
    ActiveWindow.Close


End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
I am unsure what you are asking for. Does your current code give you an error or is it simply looking at the wrong cell? If so, change the J10 to B10.
 
Upvote 0
goofy78270 said:
I am unsure what you are asking for. Does your current code give you an error or is it simply looking at the wrong cell? If so, change the J10 to B10.
Click to expand...


No J10 is a different thing all together the part i need changed is

myfilename = "C:\files\" & "any filename - "

as you can see when saved the work sheet is saved to C:\files and it is saved as filename any filename. I need that changed so that it saves as the file name that is in a cell reference of my choice, B10 was an example.
 
Upvote 0
goofy78270 said:
Change "any filename -" to "Range("B10")" as you have the reference to J10
Click to expand...



Nah it doesn't work, the J10 is another cell reference as in a number, the way it would save is any file name + J10 something like this

any filename -01

i need the any filename to look at a cell for the file name info, lets say B10 was my name, i need it to save as my name - 01 so it looks at B10 for the file name and also J10 for the number
 
Upvote 0
myfilename = "C:\files\" & "any filename - " & Range("J10") & ".xls"
Click to expand...

Change to
Code: 
myfilename = "C:\files\" & Range("B10") & " - " & Range("J10") & ".xls"

What error do you get?
 
Upvote 0
Here is my code that I use to save with a file name created from a reference cell:

Code: 
Sub SaveWS_to_NewWBs()
Dim wb As Workbook
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Copy
    Set wb = ActiveWorkbook
    wb.SaveAs ThisWorkbook.Path & "\" & ws.Range("A8").Value & " " & ws.Name & " " & ws.Range("X8").Value & " " & Format(Date,"yyyymmdd") & ".xls"
    wb.Close False
Next ws
End Sub

This has to be copied into the current workbook & will save in the same path as the current workbook. My file name would be whatever is in cell A8 + the worksheet name + whatever is in cell X8 & the date.
 
Upvote 0
zakasnak said:
Here is my code that I use to save with a file name created from a reference cell:

Code: 
Sub SaveWS_to_NewWBs()
Dim wb As Workbook
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Copy
    Set wb = ActiveWorkbook
    wb.SaveAs ThisWorkbook.Path & "\" & ws.Range("A8").Value & " " & ws.Name & " " & ws.Range("X8").Value & " " & Format(Date,"yyyymmdd") & ".xls"
    wb.Close False
Next ws
End Sub

This has to be copied into the current workbook & will save in the same path as the current workbook. My file name would be whatever is in cell A8 + the worksheet name + whatever is in cell X8 & the date.[/quot


Cheers Zakasnak another quality example :)
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
VBA Script: Saving worksheet as CSV on MacOS
Replies
1
Views
1K
temphelp
T
D
  • Solved
Trying to Create Folder and Save to Folder based on cell values
Replies
8
Views
787
DeeMan
D
The Ruff Report
  • Solved
VBA - replace a character in a range to save
Replies
5
Views
258
RobP
R
Kristylee0228
  • Question
Populating Cell based on File Name and using a List
Replies
0
Views
227
Kristylee0228
Kristylee0228
S
  • Solved
VBA not save extension when run
Replies
4
Views
179
rollis13
rollis13

Forum statistics

Threads
1,216,182
Messages
6,129,364
Members
449,506
Latest member
nomvula

Share this page

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