HELP: VBA Macro: Save a sheet and name it using a cell on another sheet.

hazio123

New Member
Joined
Dec 12, 2009
Messages
2
Hey guys,

Wondering if you can help; I have a slightly specific one which I'm struggling to crack.

My workbook has 4 sheets:

1) 'ORIGINAL' (as an example, lets say A1 value = 'face', A2 value = 'cheese' and A3 value = 'head'
2) 'TEXT1'
3) 'TEXT2'
4) 'PRINTOUT'

In short I'm trying to save 'text1', 'text2' and 'printout' as individual files naming them by using cell values in the sheet named 'original'.

- Sheet 'TEXT1' needs to be saved as a text (delimited) file with the value of A1 (face.txt)

- Sheet 'TEXT2' needs to be saved as a text (delimited) file with the value of A2 (cheese.txt)

- Sheet 'PRINTOUT' needs to duplicate/create a copy and save (as a single excel sheet) with the value of A3 (head.xls)

(...ideally all at the click of a button with a macro assigned to it...)

-----------------------------

The list of commands would looks somthing like this (im just not sure how to write the VBA for it):

>active sheet 'text1'
>save as (.txt) with value of (sheet:eek:riginal! A1)
>active sheet 'text2'
>save as (.txt) with value of (sheet:eek:riginal! A2)
>active sheet 'printout'
>create copy in new book
>save as (.xls) with value of (previous document?!? sheet:eek:riginal! A2)
>close book (and therefore revert to the last workbook)
>active sheet 'original'

Any help doing this would be very very very much appriciated.

Cheers!
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
You should try to use menu Tools/Macro/record Macro does wonders. Then you anly ned to do some editing, like setting the file name to a cell content.
Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 13/12/2009 by Sijpie
'
'
    Sheets("TEXT1").Select
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Jaap\My Documents\Teddy.txt", FileFormat:= _
        xlText, CreateBackup:=False
    Sheets("TEXT2").Select
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Jaap\My Documents\Teddy2.txt", FileFormat:=xlText _
        , CreateBackup:=False
    Sheets("PRINTOUT").Select
    Sheets("PRINTOUT").Copy
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Jaap\My Documents\teddy3.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    ActiveWindow.Close
End Sub
So that is the recorded code.
Now you need to get the filenames from Options and stick them in there (and use your own file directory of course)

Code:
Sub Macro1()
'
dim filePath as string
dim fileName as string
 
filePath = "C:\my directory\"
 
fileName = worksheets("ORIGINAL").Range("A1").value
    Sheets("TEXT1").Select
    ActiveWorkbook.SaveAs Filename:= _
        filePath & fileName, FileFormat:= _
        xlText, CreateBackup:=False

fileName = worksheets("ORIGINAL").Range("A2").value
    Sheets("TEXT2").Select
    ActiveWorkbook.SaveAs Filename:= _
        filePath & fileName, FileFormat:=xlText _
        , CreateBackup:=False
 
fileName = worksheets("ORIGINAL").Range("A3").value
    Sheets("PRINTOUT").Select
    Sheets("PRINTOUT").Copy
    ActiveWorkbook.SaveAs Filename:= _
        filePath & fileName, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    ActiveWindow.Close
End Sub

Now all you need to do is put a macrobutoon in ORIGINAL and link it to this macro
 

hazio123

New Member
Joined
Dec 12, 2009
Messages
2
You guys are good! :) Thanks

I use the 'record macro' function all the time but when clicking save I obviously couldn't select the cell. I wasn't sure how to make excel determine that either but now I know.

Thanks for your help, awesome!

Happy Christmas.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,176
Messages
5,600,154
Members
414,367
Latest member
dw970906

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
Top