Create List Of Open Workbooks

Dec 10, 2009

create a list of all open workbooks? I've found various examples of code that will do this in a Message Box, but what I am after is for them to be listed actually in the worksheet (let's say in column AA).

View 5 Replies


How To Create List Of Excel Workbooks

Nov 27, 2013

make a list of all Excel Workbooks in specific folder(My Documents). And once clicked, it will open the file immediately.

View 10 Replies View Related

How To Create New Workbooks Based On Validation List

Feb 22, 2012

Every two weeks: During Payroll, we have a list of managers and specific employees under that manager.

The managers name is in Column A and they repeat.

Normally we filter that manager, copy and paste the data into a new Workbook, save it, and send the info out to that manager. We do not want other managers to see the information provided to the specific manager.

This is a long tedious process as there are close to 30 managers.

1. Is there a way to run a macro (presumably based on a validation list) to filter, copy the worksheet into a new workbook for each of the 30+ managers?

2. Is there a way to save each with a specific name like "Payroll Feb11 - Manager Name.xlsx"

View 9 Replies View Related

Create Multiple Workbooks With Names Based On List

Sep 2, 2007

I have some very tedious work to do in Excel:

table looks like following:
DepID name function
S1 a YY
S1 b XX
S1 c ww
S2 d oo
S3 e ii
S3 f ll
S4 t mm
. . . . . .. . . .
. . . . . .. . . .
. . . . . .. . . .

S7999 u ee
S7999 w aa

My task is to create new folders for each department according to DepID, which means if there are 7999 departments, I have to create 7999 folders, any VBA code can do this?

View 9 Replies View Related

Create New Workbooks From Filtered List Then Autofit Columns

Jan 16, 2008

I used the code below to successfully create workbooks from the filtered list using the below code provided on this forum (see link below). The only problem is that I want the resulting columns to fit to width.

Looping Through A Range - Use Result As Criteria In A Filter

View 9 Replies View Related

Suppress Open Events When Using Workbooks.Open

Dec 3, 2008

I have built a sub that prompts the user for a folder then opens every workbook in the folder 1 at a time to get stats on the contents of each workbook. Worked like a dam until I ran into an unexpected bug. Some of the users built on open events in their workbooks. ...

Right now my routine inventories workbooks to get formula counts, cell counts, most complex formula, highest value... it does this by looping throught the sheets and the cells. If there is a way of obtaining those stats without opening the workbook I may need to rethink a lot of my work.

way to suppress the code in the target workbook I open through

View 9 Replies View Related

Create Open Copy Of Open Workbook

Jul 30, 2009

Create a full copy of an open workbook (eg. activeworkbook MyFile.xls) using VBA, with the new copy (eg Book1.xls) open as well ,without having to save a copy first then open it ?

View 9 Replies View Related

Macro -open The Look In List” And Stop So I Can Pick A File To Open

Oct 18, 2008

I want to create a macro that will “open the look in list” and stop so I can pick a file to open. I’ve tried to use “record a macro” and “ctrl-o”, but the record a macro won’t stop until I pick a file or cancel the file list. I also tried to use “o” in the short cut key box

View 5 Replies View Related

Open Several Workbooks

Feb 23, 2010

I have a macro on a workbook that when I run it, it's supposed to open a workbook, but it opens another workbook also.

How do I prevent this behavior?

View 9 Replies View Related

UDF #VALUE! When Other Workbooks Open With Same UDF

Nov 14, 2006

I have a UDF in a workbook that works fine until I open another workbook which contains the same UDF. When Excel does a full recalculation it not only recalculates the currently selected workbook but also recalculates all other open workbooks. Something from the currently selected workbook appears to interfere with the other open workbooks as these other workbooks show #VALUE! in all cells that use the UDF. Is there some way to make a UDF unique only to the workbook that it resides in such that opening another workbook with the same named UDF won't interfere with it?

Function SumRangeLookup(FromCode, ToCode, Database, FromColumn, ToColumn)
Dim Code As Range
Dim MonthColumns As Integer
Dim CalcResult As Double
SumRangeLookup = 0
For Each Code In Range(Database)
On Error Goto SkipCode
If Code >= FromCode And Code <= ToCode Then
For MonthColumns = FromColumn To ToColumn
CalcResult = CalcResult + Code.Offset(0, MonthColumns)
Next MonthColumns
End If
Next Code
SumRangeLookup = CalcResult
End Function

View 6 Replies View Related

Workbooks Open In Same Window

Feb 4, 2014

I have a workbook open. When I try to open a separate workbook, it opens already opened workbook (basically linking the workbooks as one). I want to have my workbooks on two separate screens this is preventing that. How to unlink these workbooks.

View 1 Replies View Related

Moving Between 2 Open Workbooks

Nov 14, 2008

Having some issues moving between 2 open workbooks. I just want to add a new workbook, select the first workbook and active sheet then move that sheet to the new workbook that was just made and lastly save the new workbook with the name of the sheet that was just moved. I have some code but it is not working correctly.

View 5 Replies View Related

Referring To Open Workbooks

Jan 27, 2009

Sub vocab6()

Dim fname As String
fname = "c:Documents and Settings

Workbooks.Open Filename:=fname

Workbooks(fname).Worksheets("tangible nouns").Range("A1").Value = 9

End Sub

I just want to open a workbook saved on my desktop (from a different workbook), and insert 9 into the first sheet (which is named tangible nouns).

View 9 Replies View Related

Open Workbooks In Two Folders

Jun 24, 2009

I have a script below that opens all the workbooks in a folder based on a msgbox. The script works as is. However what I would like to do is remove the msgbox and have the script go back and forth between the folders.

Example it would open one workbook in the (Large Area)folder call my other script (Hazleton_Data_Conversion) Return Open a workbook in (Varsity) Folder Call the
my (Hazleton_Data_Conversion)and repeat the process till all the workbooks in both folders have been processed.

Like I said this script works with the msgbox I just like to change to without msgbox and to flip back and forth.

View 3 Replies View Related

Save All Open Workbooks In Xls

Dec 2, 2009

I am using a macro to open .txts in excel in seperate workbooks

I look for a macro to save all open woorksbooks in .xls

View 12 Replies View Related

Userform And Other Workbooks Open

Dec 28, 2009

I have an excel sheet that has a userform on, when this is open my users are required to cross reference with another excel sheet they have just typed up to make sure they enter the right information into the userform. The only problem i have is, when the userform is open my users cant access any other excel workbooks.

View 2 Replies View Related

VBA To Open All Workbooks In Folder?

Oct 17, 2013

I have a macro that opens all workbooks in a folder and searches for a few terms returning the appropriate rows, my problem is that someone has decided to protect some of the sheets so when the files are opened you get the password box popup. I have added the (filename, readonly) segment however this has not solved the issue, the macro displays an error stating a workbook is open.

Is there any way to resolve this without unprotecting the sheets?

View 2 Replies View Related

Workbooks.Open Function

Jul 19, 2007

I am trying to use Workbooks.Open() function in a function. The function is not working as desired (it does not return workbook object), but when i use same piece of code in sub(macro), it works fine. I am not sure if I cant use this API inside function. I am using function because I would like to call this inside cell and that should return some value.

View 9 Replies View Related

Loop Through Open Workbooks

Jun 11, 2009

I would like to run a macro on all open workbooks except the workbook where the macro is saved. I thought I could just do the following but it does not run my macro on all the work books:

Sub apply()
For Each wb In Workbooks
If wbk.Name ThisWorkbook.Name Then
End If
Next wb

End Sub

View 9 Replies View Related

Check Which Workbooks Are Open

Aug 23, 2007

I have written a VBA code to make a pivot table from data in another workbook,A, and transfer it to workbook B. I however dont want to restrict this code to workbook A only. This is because usually workbook A will have varying names everytime it is sent. I want to be able to get data from any other workbook that is open at the same time as work book B. What code can I use to select/activate another unspecified open workbook and get data from there?

View 3 Replies View Related

Close All Open Workbooks Except......

Feb 25, 2008

I made an Excel based program that uses multiple workbooks with two main workbooks (“Master List” and “Products”) that all the others pull information from. I run into a problem when a user clicks the close X in the upper right hand corner and the entire application closes. I found some code that will let me close all the workbooks but one (the code is put in the “Products” workbook in the BeforeClose event).

For Each wb In Workbooks
If Not wb Is ThisWorkbook Then
wb.Close SaveChanges:=True
End If
Next wb

Is there a way to modify the code so it will close all the workbooks but the two main ones if someone clicks the close X button in the upper right hand corner?

View 3 Replies View Related

Set Variables To All Open Workbooks Where Name End With..

Mar 15, 2008

I have a folder containing a number of worksheets, these worksheets are of 2 types, a customer submitted workbook and, associated with each of these, anything between 2 and 7 locally created workbooks. I have a macro which opens a customer workbook and all the relevant local workbooks. My problem is that I need to declare a variable for each of the workbooks in the form

Dim N As Integer, WbN As workbook,Wb1 As workbook
If activeworkbook. name Like *ALL.xls Then
Set wb1 = activeworkbook
Set WbN = activeworkbook
End If

This code is contained within a Found files loop

View 2 Replies View Related

Macro Cycle Through Open Workbooks

Mar 29, 2013

I am trying to cycle ALL open workbooks so that I can choose the file to manipulate in subsequent code this is the code that I am utilizing furhter below

this is my code


For Each wb In Workbooks
If MsgBox("Do you want to do access this Workbook for the Update " & Chr(10) & Chr(10) & wb.Name, vbYesNo) = vbYes Then
VI_wb = wb.Name
I = True
End If
Next wb

But for some reason it only loops through .xls and xlm workbooks BUT not .xlsx

is there a way that I can loop through any and all open workbooks.

The reason I do this is because my update are based on many numerous excel workbooks with differnet extentions, and naming conventions and many of the come via email, I cannot use the eact naming convention

View 1 Replies View Related

VBA Code To Run Macro In All Open Workbooks

Mar 12, 2014

I have a macro to get copy of sheet named "Doc Info" from workbook File 1 to active workbook.

I could do it for one file on any active workbook.

But what I would require is, upon executing this macro , i want this macro to get executed in all open workbooks( could be any numbers ).

I want to move copy of sheet from File 1 to all open workbooks ( which i am doing it manualy for every file )

All these open workbooks could be from any folder , wont be in same folder.

So logic is to execute my macro apply in all open workbooks in my computer.

Below is the code and i have attached file for test

HTML Code: 

Sub Copysheet()
Dim wSht As Worksheet
Dim wBk As Workbook
Dim wBk1 As Workbook

Set wBk = ActiveWorkbook 'Workbooks("File 2.xls")
Set wBk1 = Workbooks("File 1.xlsm")
Set wSht = wBk1.Sheets("Doc Info")

wSht.Copy before:=wBk.Sheets(1)

End Sub

View 2 Replies View Related

Macro To Merge Open Workbooks

Oct 15, 2008

the code necessary to merge all open workbooks (each contains only one worksheet) into 1 workbook made up of all of these individual worksheets?

View 2 Replies View Related

Macro To Save As Csv All Open Workbooks

Apr 14, 2009

i have this macro to save as a csv this macro only save the workbook the you are looking at, i will like for this macro to save as a csv all open workbooks preserving the name of the xls file.

Dim strFilename As String
strFilename = ActiveWorkbook.Name
strFilename = Left(strFilename, InStr(strFilename, ".")) & "csv"
ActiveWorkbook.SaveAs filename:=strFilename, FileFormat:=xlCSV
End Sub

View 2 Replies View Related

Code To Work Between Two Open Workbooks

Mar 23, 2014

I have this code...

Sub Macro1()
For Each wb In Workbooks
If wb.Name Like "Test Book*" Then


can't get it to work properly...Basically I want the code to copy the date in cell F1 of the 'Log' sheet in Workbook 'Main' - then goto an open Workbook called 'Test Book' (note: this Test Book is a partial string name used hence the other code around it) and paste the copied date into cell O2 in sheet 'Data'

Using this pasted date in cell O2 carry out the required filter function.

The copy/paste of the date from my Workbook Main to Test Book isn't working so the code then shows error when trying to filter the data using the pasted date.

View 4 Replies View Related

Getting Window Handle Of All Open Workbooks?

Apr 10, 2004

I am trying to get the caption that is displayed on each window of all the open workbooks. I am using the code below to loop through all the workbook windows, get their handles and finally display their respective captions on a Msgbox.I am having 2 problems:

1- The code loops only once instead of once for each open workbook !

2- The Msgbox displays an empty string instead of the workbook caption !

If I am not mistaken, problem 2 is due to the fact that the Class name"EXCEL7" used in the code refers to a worksheet and not to a workbook. The trouble is I can't figure out what a Workbook Class name is so that I can use it in the FindWindowEx Function


Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long[code]......

View 9 Replies View Related

Macro To Open Passworded Workbooks

Jun 20, 2008

I've got a workbook ("Overview") which needs to refer to other workbooks (actually staff timesheets) which sit on a shared drive. All of these are passworded so only the staff member (and myself) can access them

As the workbook opens, it reads the links from the other files, and thus I have to enter the passwords one by one to update the data. I have come up with a macro which should do the job as follows:-

Private Sub Workbook_Open()

MsgBox "OK to run macro?"
' Message is here to let me know if the macro has run
Application.Workbooks.Open "S:LeaveTimesheetsFred 2008.xls", True, False, , "abc", "abc"
Application.Workbooks.Open "S:LeaveTimesheetsBill 2008.xls", True, False, , "def", "def"
Application.Workbooks.Open "S:LeaveTimesheetsTom 2008.xls", True, False, , "ghi", "ghi"
Application.Workbooks.Open "S:LeaveTimesheetsDick 2008.xls", True, False, , "jkl", "jkl"
Application.Workbooks.Open "S:LeaveTimesheetsHarry 2008.xls", True, False, , "mno", "mno"

End Sub

This seems to work, as it opens the workbooks. However, it doesn't run until after I've been manually prompted to enter the passwords. I'm guessing that the workbook links are taking precedence over the macro?

Is there either:-

a) A way to update the data into Overview without having to actually force the timesheets to open?

b) A way to make the macro run before the links update?

(btw I've not compromised the user passwords as Overview is itself passworded, so no-one can view the code!)

View 9 Replies View Related

2 Open Workbooks - Find 1 Delete From Other

Jul 21, 2008

I have 2 open workbooks. I've picked up the filenames in VB.

One of them is strRemitN

Another one is strStateM

In strRemit i have invoice numbers (OP/I123456) in column A.

in strStateM the invoice numbers are in column C

How would i go around, in VBA, to search for the first invoice number from strRemitN to strStateM, delete the row, then go to the next, all the way to the bottom?

Dim intLRow as integer
intLRow = Range("A65536").End(xlUp).Row

View 9 Replies View Related

Copyrights 2005-15, All rights reserved