Good Morning,
I have an excel workbook that has multiple tabs. Here is what I am trying to accomplish:
1. I would like a macro to save the current active sheet by its worksheet tab name: Some_Active_Sheet_Name2 or Some_Active_Sheet_Name3 or Some_Active_Sheet_Name4
2. I would like to reference a specific cell on Sheet1!A2 to properly name the file. For example if the data in Sheet1 A2 said "Orlando", then I would like the file named Orlando_Some_Active_Sheet_Name2 or Orlando_Some_Active_Sheet_Name3.
3. I would like the macro to copy the contents on the current active sheet and paste it into a text file and save automatically.
Here is what I have so far:
'Copy the contents of a worksheet, and save it as a new workbook as a .txt file
'
Sub Some_Active_Sheet_Name2()
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim wbDest As Workbook
Dim fName As String
'References
Set wbSource = ActiveWorkbook
Set wsSource = ThisWorkbook.Sheets("Some_Active_Sheet_Name2")
Set wsSource = ThisWorkbook.Sheets("Some_Active_Sheet_Name3")
Set wbDest = Workbooks.Add
'Copy range on original sheet
'Using usedrange can be risky and may return the wrong result.
wsSource.UsedRange.Copy
'Save in new workbook
wbDest.Worksheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
'Get file name and location
fName = ThisWorkbook.Path & "\Some_Active_Sheet_Name2.txt"
fName = ThisWorkbook.Path & "\Some_Active_Sheet_Name3.txt"
'Save new tab delimited file
wbDest.SaveAs fName, xlText
wbDest.Close SaveChanges:=True
End Sub
I have an excel workbook that has multiple tabs. Here is what I am trying to accomplish:
1. I would like a macro to save the current active sheet by its worksheet tab name: Some_Active_Sheet_Name2 or Some_Active_Sheet_Name3 or Some_Active_Sheet_Name4
2. I would like to reference a specific cell on Sheet1!A2 to properly name the file. For example if the data in Sheet1 A2 said "Orlando", then I would like the file named Orlando_Some_Active_Sheet_Name2 or Orlando_Some_Active_Sheet_Name3.
3. I would like the macro to copy the contents on the current active sheet and paste it into a text file and save automatically.
Here is what I have so far:
'Copy the contents of a worksheet, and save it as a new workbook as a .txt file
'
Sub Some_Active_Sheet_Name2()
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim wbDest As Workbook
Dim fName As String
'References
Set wbSource = ActiveWorkbook
Set wsSource = ThisWorkbook.Sheets("Some_Active_Sheet_Name2")
Set wsSource = ThisWorkbook.Sheets("Some_Active_Sheet_Name3")
Set wbDest = Workbooks.Add
'Copy range on original sheet
'Using usedrange can be risky and may return the wrong result.
wsSource.UsedRange.Copy
'Save in new workbook
wbDest.Worksheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
'Get file name and location
fName = ThisWorkbook.Path & "\Some_Active_Sheet_Name2.txt"
fName = ThisWorkbook.Path & "\Some_Active_Sheet_Name3.txt"
'Save new tab delimited file
wbDest.SaveAs fName, xlText
wbDest.Close SaveChanges:=True
End Sub
