Referencing Inactive Workbook Cells
 

Good morning all

Hope someone can provide some help here. I have created a macro that takes a
specific worksheet from the workbook and saves it as its own workbook (code
shown below) referencing 2 cells in the worksheet as the filename.

Sub Savefile()

'opening workbook

ActiveSheet.Copy ' Copies active sheet to a new workbook
ActiveWorkbook.SaveAs Range("H6").Value & Range("H7")

End Sub

However, as part of the filename, I would also like to include a cell from a
different worksheet in the original workbook (location shown below)

workbook name: version2.xls
worksheet: MasterDMA
cell: E6

Please could someone provide advise on how to reference that cell as well
within the filename.

Thanks so much

Regards,
PVANS

Referencing Inactive Workbook Cells
 

set rng = WORKBOOKS("version2").Worksheets("MasterDMA").Rang e("E6")
ActiveWorkbook.SaveAs Range("H6").Value & Range("H7") & rng.Value

"PVANS" wrote in message
...
Good morning all

Hope someone can provide some help here. I have created a macro that takes
a
specific worksheet from the workbook and saves it as its own workbook
(code
shown below) referencing 2 cells in the worksheet as the filename.

Sub Savefile()

'opening workbook

ActiveSheet.Copy ' Copies active sheet to a new workbook
ActiveWorkbook.SaveAs Range("H6").Value & Range("H7")

End Sub

However, as part of the filename, I would also like to include a cell from
a
different worksheet in the original workbook (location shown below)

workbook name: version2.xls
worksheet: MasterDMA
cell: E6

Please could someone provide advise on how to reference that cell as well
within the filename.

Thanks so much

Regards,
PVANS

Referencing Inactive Workbook Cells
 

Sub Savefile()

Dim strFile as String
strFile = WorkBooks("version2.xls").Sheets("MasterDMA").Rang e("E6")
ActiveSheet.Copy
ActiveWorkbook.SaveAs "c:\" & strFile & Range("H6").Value & Range("H7")
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"PVANS" wrote:

Good morning all

Hope someone can provide some help here. I have created a macro that takes a
specific worksheet from the workbook and saves it as its own workbook (code
shown below) referencing 2 cells in the worksheet as the filename.

Sub Savefile()

'opening workbook

ActiveSheet.Copy ' Copies active sheet to a new workbook
ActiveWorkbook.SaveAs Range("H6").Value & Range("H7")

End Sub

However, as part of the filename, I would also like to include a cell from a
different worksheet in the original workbook (location shown below)

workbook name: version2.xls
worksheet: MasterDMA
cell: E6

Please could someone provide advise on how to reference that cell as well
within the filename.

Thanks so much

Regards,
PVANS

Referencing Inactive Workbook Cells
 

Thanks Jacob

think I got it sorted now, thanks for the quick response

Regards,

"Jacob Skaria" wrote:

Sub Savefile()

Dim strFile as String
strFile = WorkBooks("version2.xls").Sheets("MasterDMA").Rang e("E6")
ActiveSheet.Copy
ActiveWorkbook.SaveAs "c:\" & strFile & Range("H6").Value & Range("H7")
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"PVANS" wrote:

Good morning all

Hope someone can provide some help here. I have created a macro that takes a
specific worksheet from the workbook and saves it as its own workbook (code
shown below) referencing 2 cells in the worksheet as the filename.

Sub Savefile()

'opening workbook

ActiveSheet.Copy ' Copies active sheet to a new workbook
ActiveWorkbook.SaveAs Range("H6").Value & Range("H7")

End Sub

However, as part of the filename, I would also like to include a cell from a
different worksheet in the original workbook (location shown below)

workbook name: version2.xls
worksheet: MasterDMA
cell: E6

Please could someone provide advise on how to reference that cell as well
within the filename.

Thanks so much

Regards,
PVANS