Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Nacro for copy content of same cell from different worbooks.

0

I need to run the same macro each time on another workbook which have different values (text) in the same cell. The Macro should copy the content of this cell into the clipboard and then paste it from the clipboard to the file name for the "save as"  command.

As mentioned, after that the Macro will be applied on another worbook, which has different value (text) in the same cell, to copy its content into the clipbiard and then paste it as the file name of the "save as" command.

excel
posted by: YBB on Thu Nov 28, 2019 at 4:50 am
Answer
Discuss

Discussion

Hi YBB
Please confirm your requirements as I understand them
1. You require a workbook with a Macro. "Maco Book"
2. When the macro executes it will get the value for a cell in another work e.g A1 "OtherBook1"
3. The macro will then perform a "Save As" on the the original workbook "Macro Book" and it will be named "OtherBook1A1"
4. You can run the macro again from either "Macro Book" or "OtherBook1A1"
and it will do the above but using "OtherBook2" and the value in "A1"

All I need to know now is how do you want to run the Macro? How do you want to provide the name of the other workbook? What is the address of the same cell in these other workbooks?
Thanks
k1w1 
k1w1sm (rep: 197) Nov 28, '19 at 5:02 pm
Hi k1w1
Many thanks for your help.
Actually it is like the following:
The Macro could be in the PERSONAL book (with all other Macros I have). The PERSONAL will stay open in the background.
1.  I will open the Otherbook1 and execute the Macro (by using short cut) that will get the content of the Otherbook1A1, which is "Text1" for instance. Then the Macro will perform a "save as" of the Otherbook1 with the name "Text1" and then close both a new Text1book and the Otherbook1. End of Macro.
2.  Then I will repeat the procedure with the Otherbook2 : open the Otherbook2, execute the Macro, which will get the content of the Otherbook2A1 ("Text2" for instance) and save the Otherbook2 as Text2book, then close the books. (The PERSONALbook stays always open).
3.  Then I repeat the prcedure with Otherbook3 ...
Thanks and Best Regards,
YBB Nov 29, '19 at 12:01 am
Got yah. It will a couple of days before I can get back to this 
k1w1sm (rep: 197) Nov 30, '19 at 4:08 am
Add to Discussion

Answers

0

Hi all!

I need exactly the same Macro

posted by: Eddy on Fri Nov 29, 2019 at 2:01 am
Discuss
0

here is the code that does that (almost as recorded)

Sub SaveAsA1()
'
' Macro2 Macro
' save and close other book
'K1w1 dec 2019

'

Dim SaveName As String
Dim SavePath As String

    SavePath = ActiveWorkbook.Path
    SaveName = ActiveSheet.Cells(1, 1).Value & ".xls"  


    ActiveWorkbook.SaveAs Filename:= _
        SavePath & "\" & SaveName, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False

   ActiveWorkbook.Close

End Sub

Just put it in your personal excel and assign a short cut. 

It has just occured to me that you might not have your personal xl in your start up folder. It might still work if you don't and it is open. Also I didn't check wha happens to any changes you make to the speadsheet before the save as. I suspect theywill be lost. Just put a save before the save as. 

posted by: k1w1sm (rep: 197) on Sun Dec 1, 2019 at 4:24 pm
Discuss

Discussion

Hi k1w1
I've tried and it has a problem with finding the folder and or the file.
The Debug paints yellow the following:
Sub SAVEAS()
'
' SAVEAS Macro
' Dim SaveName As String
Dim SavePath As String     SavePath = ActiveWorkbook.Path
    SaveName = ActiveSheet.Cells(1, 1).Value     ActiveWorkbook.SAVEAS Filename:= _
        SavePath & "\" & SaveName, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False    ActiveWorkbook.Close End Sub

Please help.
Best Regards,
YBB Dec 2, '19 at 6:03 am
good work at least you have got it to run.
The first thing that springs to mind is that your text in A1 is not able to be used as a file name.
k1w1sm (rep: 197) Dec 2, '19 at 5:22 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login