I want to use a for command, but not include all the i's between two numbers. The reason is I want it to activate open workbooks that have similar names, like '110000 2007.xls', '120000 2007.xls', '130000 2007.xls', '150000 2007.xls' and so on.
I want my code to look something like this:
For i = 11, 12, 13, 15
Windows(10 * i & "000 2007.xls").Activate
Is there a way I can do this? Or use another command?
I need a macro to go into each subfolder: Open each excel workbook (which will usually contain one sheet), and insert a blank new row at the top of each sheet (without overriding existing row), filling it with headings like January, Feb....December.
I'm looking for some way to open multiple protected workbooks in one time, these workbooks has been protected , but all with the same password. usually, when i open one workbook, open ,enter password, but there are so many of them that i want to open all these workbooks in the same time, the problem is each workbook showed a enter-password box, it so inefficient, i want all these protected workbooks show only one enter-password box.
I have a created a main workbook with a macro that opens workbooks on a particular sheet from a list of workbook paths and filenames in the main workbook. The macro are suppose to copy the sheet and paste it into a sheet with a new name that I have defined from the list in the main workbook. I can get the macro to open the workbooks and close them. But I can't get the macro to copy the sheet into the main workbook.
I have attached the code and the workbook. I used slet_ark to delete previous sheet and hentark and henttekstfil to open the different workbooks.
The code I use are:
Public Sub Slet_ark() Dim ark() As Variant Dim Counter As Long Dim FirstSheet As Long Dim LastSheet As Long
I have data spanning many files which are named with the date on which they were created (so there are 31 files for August). For example: PL080107, PL080207, PL080307, PL080407...)
I'm trying to compile all of my data into one workbook, and have the macro to append each file to a list. Now I need the macro to either OPEN each file, or ACTIVATE each file so that the rest of the Macro can grab the necessary data.
I say Open or Activate, because I can MANUALLY Open a full month's worth of files if it's easier code. If not, I would like it to open and close each workbook on its own.
I have over 7 month's worth, so opening 31 files 7 times is better than opening over 200 files individually!
I currently have a piece of code that opens all of the files in a folder that are called "*agent*", opens them and copies information. Now, these files come with numbers at the beginning which, are always the same. I only want to open certain files that begin with, for example, 801, 802, 803, 804, 805 and 806. How would I write this into my code? As you can see from the below code, it now looks for the files that all have "agent" in the name, but this is opening files that have that name but are not the right ones. Here is my current macro...
I hope this isnt as simple as putting "MyFile = Dir(MyFolder & "*801*", "*802*")" etc.
I'd like to use VBA to open 3 workbooks in their own session/instance/etc of Excel 2007. Ideally, there will be one main workbook, that upon opening will subsequently open the other 3 workbooks.
Also, when I open that first main workbook, I will obviously have to enable macros to get any code to run. Is there a way to carry this enabling of macros over to the other sessions to avoid having to enable macros in all 3 other workbooks? (Changing my security settings isn't an option)
I tried all failed.We have about 160 different workbooks (one for each business unit) stored online. Staff enters information about their weekly revenue and expenses and here at head office I collect that information and consolidate them.What I am trying to do is that;1.) Create a master Workbook with ~160 worksheets (One worksheet for each unit) named exactly the same with other workbooks2.) And macro can pull the information from related files stored in a certain folderIt is very much like another members problem but I am not sure why I cant get that code working for me? [URL]
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 workbooks.open
I am working with Excel for Mac. This has presented a number of challenges but I have successfully handled them. I am having a problem though and would love some input. I have a workbook that contains a pivot table. The pivot table is based on "Sheet1" of my workbook. The user will hit a button on the pivot table spreadsheet and the first operation is to select a file from their computer. I am using a function called - MacScript to open the finder window and select the file. The file name (with full path) is stored in a variable called filepath. The file consists of a single sheet - Sheet1 and I select all and copy the cells. I then go back to the original workbook, select my worksheet with the source data for the pivot and I again select all and paste the new information over the old data. I have a named range called pivotsource that references the 9 columns that are required for the pivot table. What I would like to do now is go back to the original workbook and close it, do not save anything, just close it like nothing happened. I have tried to close it by using:
Since I had a large amount of data that is in the clipboard, I will also have to deal with the message coming up asking what I want to do with the clipboard. My bigger problem is that the activation of the filepath workbook doesn't work. How can I get back to the original workbook? I thought about the index of the workbooks but I don't know if the users will have other workbooks open at the same time. If they did, I could not reference it.
I have created a user form that has 6 input boxs and at the bottom of the userform has a seventh box that has a running total. I have two buttons one to "accept" and one to "cancel". My prloblem is creating the code to make it work correctly.
1) if a cell in B5:B20 become the active cell than the userform is to open up. 2) once the useform is open the user can input up to six different numbers into the input box's which will show the runinng total in the seventh box. 3) Once all numbers are inputed into the input box's the user can hit "accept" to post the total into the active cell or hit "cancel" and nothing have nothing happen.
What is the code to activate a hyperlink in an active IE window, new tab (IE7), as opposed to a new window, which is the result of:
Private Sub CommandButton14_CLICK() Dim strClick1 As String Dim strClick1default As String Set WEBPAGE = CreateObject("InternetExplorer.Application")
strClick1default = Range("c51").Value strClick1 = InputBox("Paste the weblink for the trace, or accept the default, and press OK to initiate the trace.", "ClickTrace", strClick1default) Range("c51").Value = strClick1 If vbOK Then With WEBPAGE .Visible = True .navigate strClick1 End With
End If This code runs the hyperlink activation fine, but opens a new instance of IE, whereas I want it to use the already open and active instance, but using a new tab.
I have a userform in "text1.xls" workbook when I click on commandButton it validates Staff name and Staff number stored in sheet1 of "test1.xls" workbook and opens test2.xls file which is in different folder. Problem is how do I code to close or unload "test1.xls" after opening test2.xls
Private Sub goButton_Click()
F$ = "D: estFiles est2.xls" ' can I use Path so I don't need to type the full directory path
I compared data between excel worksheets all the time, well recently my computer was upgraded from Windows XP to Windows 7 and since then I have not been able to figure out how to have an Excel file to open in the Excel window selected(or last active window) when I have more than one workbook open. Is there a way to do this?
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 SkipCode: End Function
I want to create a standard macro that will copy values from a Master workbook to the current, active workbook I have open. The problem I have run into is when I record a macro it also records the name of the active workbook I'm copying data into and I would like it so when I bring up a workbook I just run the macro and it copies the values in. I'll be doing 8 different macros that each copy different values from the Master spreadsheet
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.
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.
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.
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.
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?
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.
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?
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?