Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Name file automatically
I'm struggling with a problem that I bet is easy as ****, but anyway...
I want to make a macro that saves only sheet number five (named "giro") of my workbook, and at the same time gives the file the name of the value of cell Q1 and cell B9 on this sheet. Is this possible? Thanx a lot in advance... |
#2
|
|||
|
|||
I'm not sure what you have in Q1 and B9, but maybe...
Option Explicit Sub testme() Dim wks As Worksheet Set wks = ActiveWorkbook.Worksheets("giro") wks.Copy 'to a new workbook With ActiveSheet .Parent.SaveAs Filename:="C:\" _ & .Range("q1").Value & .Range("b9").Value & ".xls", _ FileFormat:=xlWorkbookNormal .Parent.Close savechanges:=False End With End Sub There is no error checking -- make sure those values in Q1 and B9 will make a nice name. (Dates are problems. Windows files can't have /'s in them.) .Parent.SaveAs Filename:="C:\" _ & format(.Range("q1").Value, "yyyy_mm_dd") _ & .Range("b9").Value & ".xls", _ FileFormat:=xlWorkbookNormal Might work for you. KingKarl wrote: I'm struggling with a problem that I bet is easy as ****, but anyway... I want to make a macro that saves only sheet number five (named "giro") of my workbook, and at the same time gives the file the name of the value of cell Q1 and cell B9 on this sheet. Is this possible? Thanx a lot in advance... -- Dave Peterson |
#3
|
|||
|
|||
Thanx a lot...
This worked fine. I only had to add the line in the macro that tells it to print :-) Still, after using this I encoutered another problem: when opening the saved file and having the original workbook open, the saved file automatically updated itself with the latest cell values (because alle the cells in "giro" are linked to another sheet in the workbook). Is there a way to get around this problem? Karl Erik Kvalsund Dave Peterson skrev: I'm not sure what you have in Q1 and B9, but maybe... Option Explicit Sub testme() Dim wks As Worksheet Set wks = ActiveWorkbook.Worksheets("giro") wks.Copy 'to a new workbook With ActiveSheet .Parent.SaveAs Filename:="C:\" _ & .Range("q1").Value & .Range("b9").Value & ".xls", _ FileFormat:=xlWorkbookNormal .Parent.Close savechanges:=False End With End Sub There is no error checking -- make sure those values in Q1 and B9 will make a nice name. (Dates are problems. Windows files can't have /'s in them.) .Parent.SaveAs Filename:="C:\" _ & format(.Range("q1").Value, "yyyy_mm_dd") _ & .Range("b9").Value & ".xls", _ FileFormat:=xlWorkbookNormal Might work for you. KingKarl wrote: I'm struggling with a problem that I bet is easy as ****, but anyway... I want to make a macro that saves only sheet number five (named "giro") of my workbook, and at the same time gives the file the name of the value of cell Q1 and cell B9 on this sheet. Is this possible? Thanx a lot in advance... -- Dave Peterson |
#4
|
|||
|
|||
You could convert to values.
Option Explicit Sub testme() Dim wks As Worksheet Set wks = ActiveWorkbook.Worksheets("giro") wks.Copy 'to a new workbook With ActiveSheet With .UsedRange .Value = .Value End With .Parent.SaveAs Filename:="C:\" _ & .Range("q1").Value & .Range("b9").Value & ".xls", _ FileFormat:=xlWorkbookNormal .Parent.Close savechanges:=False End With End Sub You'll have to play catchup with your changes. I only added: With .UsedRange .Value = .Value End With Not sure if it's easier to add this change or your print statements. KingKarl wrote: Thanx a lot... This worked fine. I only had to add the line in the macro that tells it to print :-) Still, after using this I encoutered another problem: when opening the saved file and having the original workbook open, the saved file automatically updated itself with the latest cell values (because alle the cells in "giro" are linked to another sheet in the workbook). Is there a way to get around this problem? Karl Erik Kvalsund Dave Peterson skrev: I'm not sure what you have in Q1 and B9, but maybe... Option Explicit Sub testme() Dim wks As Worksheet Set wks = ActiveWorkbook.Worksheets("giro") wks.Copy 'to a new workbook With ActiveSheet .Parent.SaveAs Filename:="C:\" _ & .Range("q1").Value & .Range("b9").Value & ".xls", _ FileFormat:=xlWorkbookNormal .Parent.Close savechanges:=False End With End Sub There is no error checking -- make sure those values in Q1 and B9 will make a nice name. (Dates are problems. Windows files can't have /'s in them.) .Parent.SaveAs Filename:="C:\" _ & format(.Range("q1").Value, "yyyy_mm_dd") _ & .Range("b9").Value & ".xls", _ FileFormat:=xlWorkbookNormal Might work for you. KingKarl wrote: I'm struggling with a problem that I bet is easy as ****, but anyway... I want to make a macro that saves only sheet number five (named "giro") of my workbook, and at the same time gives the file the name of the value of cell Q1 and cell B9 on this sheet. Is this possible? Thanx a lot in advance... -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saved *.csv file gives SYLK file type warning upon Excel 2003 open | Excel Discussion (Misc queries) | |||
How do you open a template at startup? | Excel Discussion (Misc queries) | |||
Automatically update from source when file opens | Excel Discussion (Misc queries) | |||
Locating a file in excel with a partial file name. | Excel Discussion (Misc queries) | |||
Enter Data Into Another Excel File Automatically | Excel Worksheet Functions |