SaveAs Concatenation

[Guest]

Hi All......

I have a Major Workbook that I run a macro in to open another workbook and
copy some data from the Major book into it. Then I want to SaveAs that new
workbook to the same directory that the Major Workbook came from,(without
hardcoding the path in), and with a new FileName created by Concatenating
cells B4 and B3 from a sheet in the new book named Report1.

I've tried, but just can't seem to get it right.......

Your assistance would be much appreciated.

Vaya con Dios,
Chuck, CABGx3

 

[Bob Phillips]

Chuck,

air-coded

Set wbMajor = Workbooks("name of major workbook")
Set wbNew = Workbooks("name of new workbook)

With wbNew
.SaveAs Filename:=wbMajor.Path & "\" &
..Worksheets("Report1").Range("B4").Value & _

..Worksheets("Report1").Range("B4").Value & ".xls"
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

For a module within your Major Workbook, this code should do it:

Dim MajorBookName as String, FolderPath as String, SaveAsName as String

MajorBookName = ThisWorkbook.FullName
FolderPath = Left(MajorBookName,InStrRev(MajorBookName,"\"))
SaveAsName = FolderPath & Workbooks("Report1").Sheets("SheetName").Range(B4) _
& Workbooks("Report1").Sheets("SheetName").Range(B3) & ".xls"
Workbooks("Report1").SaveAs SaveAsName

Hope that does it for you!

 

[Guest]

Thank you kind Folks, Bob Philips and K. Dales......

You have made my day,

Vaya con Dios,
Chuck, CABGx3