How to set cell as the name of the other open workbook
Requirement:
The user has this rather unusual task to set the name of a cell based on the name of the other (second workbook which is open) - do you think this can be done?
There will be two open workbooks named one.xlsm and two.xlsx.....so the user needs to set A1 on sheet1 of workbook named one.xlsm as two.
Solution:
Code:
Sub SetCellVal()
Dim WB As Workbook
For Each WB In Workbooks
If WB.Name <> ThisWorkbook.Name Then
Sheets("Sheet1").Range("A1") = WB.Name
End If
Next WB
End Sub
The macro loops through the two open workbooks and checks to see if the workbook name is equal to the workbook containing the macro. If the name is not the same as the workbook containing the macro, the name of the workbook is placed in A1 of Sheet1. The macro assumes that only the two workbooks are open
Obtained from the OzGrid Help Forum.
Solution provided by Mumps.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to copy data in VBA from different named workbook each time |
| How to find one value with another workbook and copy correspondent value |
| How to create a button to run formula on every sheet in workbook |
| How to copy from multiple workbooks to master file and keep overwrite data |
| How to use Excel VBA macro to import data from multiple workbooks to main workbook |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
