Save as macro

[Guest]

Happy Holidays


I want to make a macro that will "save as" my exel file. Keeping the same file name (follow_up.xls)
but create a new directory and saving it there. The name of this directory should b
the contents of one of the cells in this file (ex: 12345)

The reason I want to do this is because I have a macro that switches back and forth to another file (list.xls)
If I change the name of my first file, this macro doesn't work any more

Windows("Follow_up.xls").Activat
Application.CutCopyMode = Fals
Range("B2:E2").Selec
Selection.Cop
Windows("list.xls").Activat
Range("B1").Selec

Can I create a new directory this way, or is there an easier way of doing this

Thanks for any help

 

[Dave Peterson]

Maybe you could use some object variables that'll represent your
workbooks/worksheets, but I think you'll want to be more careful with the
worksheet names--instead of relying on the activesheet.

dim wkbkFU as workbook
dim wkbkList as workbook

set wkbkfu = workbooks("follow_up.xls")
set wkbkList = workbooks("list.xls")

wkbkfu.worksheets("sheet1").range("B2:E2").copy _
destination:=wkbklist.worksheets("sheet2").range("B1")

===

You could have dropped the variables completely and just:

workbooks("follow_up.xls").worksheets("sheet1").range("B2:E2").copy _
destination:=workbooks("list.xls").worksheets("sheet2").range("B1")

But if you're doing lots of stuff, wkbkFU will be easier to type. And you'll
only have to search for it and change it one spot when the workbook name
changes.

 

[Guest]

Thanks for the advise, Dave.

I'm new with Exel, so please bear with me.

I think I messed up on the way I asked my question. I probably should have said it like this:

Sub saveascode()
Dim sFileName As String

Range("A3").Select
Selection.Copy
Range("F3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
sFileName = Worksheets("Hotline").Range("F3").Value
ThisWorkbook.SaveAs Filename:="C:\peter\3\" & sFileName & ".xls"
Range("F3").Select
Selection.ClearContents
End Sub

This saves my file by giving it the file name found in A3.
I want to save it as C:\peter\3\"contents of A3"\"original file name".xls


I hope this is better

 

[Dave Peterson]

Option Explicit
Sub aa()

Dim myFolder As String

With ThisWorkbook
myFolder = .Worksheets("hotline").Range("A3").Value

On Error Resume Next
'just in case the folder doesn't exist
'but c:\peter\3\ must!
MkDir "c:\peter\3\" & myFolder
On Error GoTo 0

.SaveAs Filename:="C:\" _
& .Worksheets("hotline").Range("a3").Value _
& "\" _
& .Name
End With

End Sub


I'm not sure if you need the extra trailing backslash--depends on what's in A3.