ActiveWorkbook.SaveAs changes the Tab name - ARRGGG....

N

NotToShabby

New Member
Joined
Jun 16, 2005
Messages
11
Anyone -
I am doing two differnt save methods for one file. The first method is to save one of the sheets that has a list of generated datapoints for a helix (x,y,z point locations) and the data needs to be Tab delimited to import into another program. This I can do with the AcitveWorkbook.SaveAs without much problem - the tab and the saved file name can be the same.

Where I am having problems is with the final save. I switch to the main sheet - called "Calc" and run the ActiveWorkbook.saveas. What happens is the tab now will change to whatever file name save the file as. This I can not have as the maco looks specifically for the "Calc" sheet. Running the macro again fails because of the new sheet name.

How do I prevent this from happening????

***************
'Save Tab Delimited Worksheet
FileSaveAs = Excel.ActiveSheet.Name
Excel.ActiveWorkbook.SaveAs Filename:=FileSaveAs, FileFormat:=-4158
'Switch to main sheet
Sheets("calc").Activate
'Prompt for file name
NewFile = Application.GetSaveAsFilename(NewFile)
'Save file
ActiveWorkbook.SaveAs (NewFile)
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Any Luck?

I have not been able to figure this out. Am I approaching this from the wrong viewpoint? Is there another way to save in VB script?
 
Upvote 0
NotToShabby,

Create a new workbook, copy the sheet to the new workbook, delete any blank sheets and then save the new workbook then return to the existing workbook. Post back if you need help with the code.
 
Upvote 0
Not sure how this helps

The worksheet Calc is deeply embedded with links to several other sheets (about 8) thorughout the the workbook. Not sure how copying the worksheet to a new workbook helps. Are you refering to the tab delimited worksheets?
 
Upvote 0
Re: Not sure how this helps

NotToShabby said:
Are you refering to the tab delimited worksheets?
Click to expand...
Yes. Unless I misunderstood. You are trying to save one sheet of a workbook to a tab delimited file and then want to return to the original workbook. Not so?
 
Upvote 0
None of the below suggestions have been tested. You will have to do the needful.

What is the format in which XL saves the file the 2nd time around? Is it the correct XL format? Or some other? Maybe, the tab-delimited formati?

If so, and if you want the 2nd save to be as an XL file, try the following...

(a) Specify the fileformat the 2nd time around
(b) For the 1st save try savecopyas (if it allows the specification of a file format)
(c) For the 1st save, make a copy of the sheet (copying only values) and save that copy as a tab-delimited file.

Even if the first 2 don't pan out, the last option, while the most work, should work as you want.
 
Upvote 0
Ahnold - yes, I want to save the fist worksheet as tab delimited, and the second save would save the full workbook as .xls.

Tusharm - after the fist save, the file format for the workbook is tab delimited. I have tried to force the second save to FileFormat := xlNormal, however it still changes the tab name for the "calc" sheet to whatever I have just named the file during the SaveAs.

I will try the third option -
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Save As does not work properly
Replies
7
Views
785
Rainmanne
R
R
  • Question
Making a model read-only
Replies
6
Views
612
Candyman8019
C
L
  • Question
Activate file with file name depending on cell value changed
Replies
1
Views
350
OaklandJim
O
B
  • Question
VBA & OneDrive: SaveAs to OneDrive results in 1004 error
Replies
1
Views
1K
MedicineVan
M
R
  • Solved
Macro to save a tab with changing name
Replies
5
Views
278
nemmi69
nemmi69

Forum statistics

Threads
1,214,585
Messages
6,120,394
Members
448,957
Latest member
Hat4Life

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top