question about a code to "save as"

Z

Zygoid

i have a command button on a spread sheet that, when clicked, will sav
the spread sheet in a folder and name it, depending what is in certai
cells. my problem is where i want it saved.

right now, i have all spread sheets being saved into one certai
folder, but i would like the spread sheet to be saved into a folde
that is specified in cell d2.

this is the code i have now.

Sub Save_As_FileName()
FName1 = Range("d2").Value
FName2 = Range("d3").Value
FName3 = Range("d5").Value
FName4 = Range("d6").Value
Fname5 = Range("d7").Value
pth = "f:\bids\"
ActiveWorkbook.SaveAs Filename:=pth & FName2 & " " & FName3 & " "
FName4 & " " & Fname5 & " " & FName1 & ".xls", _
FileFormat:=xlNormal, CreateBackup:=False
End Sub


I have tried changing the pth="f:\bids\" to many different things an
can't seem to figure it out.

any help? Thanks
 
B

BrianB

Sometimes Excel does not seem to like doing too much work in one go. Tr
this :-


Code
 
Z

Zygoid

i see how that would create the file name, but i am not understandin
how that will save the workbook into a file designated by the text tha
is typed into cell d2.

i would still need the pth="f:\bids\" ?

in f:\bids i have 4 folders... bdg, ear, gjg and jpr (sales re
intials)
in cell d2 one out of the four is typed.

i would like the workbook saved into one of those folders when th
command button is clicked
 
B

BrianB

Sorry ... add the path at front as you do already :-

MyFileName =pth & FName2 .. etc
 
D

Dave Peterson

I've never seen excel fail in code like yours. But I have failed to ensure that
there was good stuff in those cells.

I'd use BrianB's technique and then add a line like:

msgbox myfilename

right before the saveas command. Then I could see if I'm getting the ranges
from the correct worksheet.

I think I'd be a little more careful here:

with worksheets("sheet1")
FName1 = .Range("d2").Value
FName2 = .Range("d3").Value
FName3 = .Range("d5").Value
FName4 = .Range("d6").Value
Fname5 = .Range("d7").Value
end with

(My guess is you're getting the values from the wrong worksheet.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top