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

[hazio123]

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 (sheetriginal! A1)
>active sheet 'text2'
>save as (.txt) with value of (sheetriginal! A2)
>active sheet 'printout'
>create copy in new book
>save as (.xls) with value of (previous document?!? sheetriginal! 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!

 

[sijpie]

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.

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)

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]

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.