Needing a macro to save in another workbook

Saa62

New Member
Joined
Mar 20, 2002
Messages
20
I sent this before but received an error so I am not sure it went through.

I hope I am not asking a simple question thats been responded to numerous times but a quick search did not show anything like I needed to know for my problem.

Example:
I have workbook "ABC" in a folder "123". I need a macro in workbook "ABC" to "save as" the open workbook into folder "456" with the same file name as the open workbook. I also need a notation made in a cell of each of the workbooks showing the workbook has been copied after the save as command has completed. I have gotten most of this to work with the excemption of being able to save the new workbook in a different folder with the same file name as the open workbook. Workbook "ABC" will have various saved file names with the same macro copied into them, so the save as macro will need to be able to refer to the file name or a cell within the file to use as the name for the new file in the second folder.


Anybody have any suggestions or need clarification of what I am talking about?

Thanks
This message was edited by Saa62 on 2002-03-21 16:48
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
I tried but cannot exactly get a grip here.
You want Workbook 1 from folder A to run its own macro and save itself as Workbook 2 in folder B?

How's that for crafty repetion?

I think you are saying that you want workbook 1 to keep a running list of all the saved as workbook names in folder B which have the same name from what I can gather.
We know that two files with the same extention cannot live together in the same folder...

Whatever you are trying to do, it can most likely be done with VB.
However, I really need to know exactly what you intend to do???????

What are the cell references for as far as which macro is concerned?????

Try again please!
 
Upvote 0
Let me go back a little farther to explain where I ended up, to hopfully clarify what I am trying to do.

I have an Access database with a macro that will pull selected pieces of data from a record. Then copy this data into an excel workbook into particular cells on the first sheet in the excel workbook. That information is then linked to several other sheets within the workbook to be placed in the correct cells to fill out the various forms used by my company. The macro will then do a "save as" and name the file by the log number from the Access record and put it into another folder. This last "Save As" is Workbook 1 in folder A that you used in your description. This mean that workbook 1 will take on various names depending on what log number the data was pulled from in Access. All of the above in done from Access.

This much of the program has worked well. The information in the workbook is then copied off and sent to the various suppliers. We are now wanting to email the information to the various suppliers but before it can be sent out it must be reviewed by another department within our company.

This is why I need to be able to send a copy of workbook 1 in folder A to folder B with the same file name. Folder B would be a folder that is available to the necessary personel to review. The notation I need would be something like "Sent to TL" in workbook 1 in folder A to alert other users that the file had already been sent.

Due to large number of users that are not overly trained in moving files it is necessary to simplify it as much as possible. I am hoping that a macro button within the workbook would be easy enough to get the files where they need to go.

I hope that clarifies what I am wanting do. Any suggestions?

Thanks
This message was edited by Saa62 on 2002-03-23 06:24
 
Upvote 0
Ok. I think I understand.
You simply need the code to save the workbook in another folder while retaining its name.

I have never done that before, but even if you cannot save a workbook under the same name to a different location, it can be saved with a different name, and then renamed. All this can be done behind the scenes.

I'll see what I can draw up...
 
Upvote 0
I hope this is what you are looking for.
Make sure the path is edited and correct or errors will keep your mini macro from working correctly.

If this is not your answer, keep posting with as much info as you can give. There are some extremely capable people here. Someone will surely be able to help you.

Tom

P.S. Create the button you wanted and name it cmdSaveCopy, in design mode double-click to bring up the code window. Replace the buttons cmdSaveCopy_Click event with the code below and run after you have edited.
I can't get rid of the double There should only be one forward slash in each position.

Private Sub cmdSaveCopy_Click()

Dim CompletPathToFolder_B As String

On Error GoTo Oops

CompletPathToFolder_B = "C:WIN95 DesktopFolder_B" & ThisWorkbook.Name

ThisWorkbook.SaveCopyAs CompletPathToFolder_B

'notation
Sheet1.Range("A1").Value = "Last sent to TL on " & _
format(now,"MMMM DDD, YYYY HH:MM AMPM")


Exit Sub
Oops:
MsgBox "Error - Copy of " & ThisWorkbook.Name & " may not have been saved!"
End Sub


How do you get rid of these double forward slashes????
This message was edited by TsTom on 2002-03-23 07:42
 
Upvote 0
Prelimenary testing is showing this will solve my problem. I won't know for sure until I start incorporating it with rest of the program but this looks like it will do the trick.

Thanks

PS. The date and time stamp is a great idea on the notation. I will be using that.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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