Open/Add New Workbook Macro Code

May 12, 2008

I am trying to write code to open a new (blank) workbook while in an existing workbook (I am then going to pass data between the two which is easy). When I try to record the code to get the syntax for opening a new workbook it will not record any code. I also need to name the new workbook based on text in a cell in the existing workbook. I just saw before posting this that the code;

workbooks.add

will open a new workbook now I just need to name the new workbook based on text in my existing workbook.

View 2 Replies

2002 Code V 97 Code: Add A Small Workbook Open Event Code Which Works For Me But Debugs For The Others

Jan 27, 2009

I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..

View 2 Replies View Related

Code To See If A Workbook Is Open

Apr 28, 2009

I need to error check to see if a worbook is open or not. if it is not open i need it to open it. i supplied the code i am trying but it is not working. I keep all my workbooks in XLSTART.

View 2 Replies View Related

MacRO: Check If WorkBook Open. If Not, Open It.

Sep 27, 2006

I did a macro on my mac to transfer a sheet from one workbook to another worbook. It works very well when the destination workbook is open. Therefore I wanted to add some piece of code to check if the destination workbook is open. If not then I wanted the macro to open it before tranfering the sheet. Here is the code I�m using for tranfering the sheet

Sub Transfer_Sluttet()
If ActiveSheet.Index <> Sheets.Count Then
Application.DisplayAlerts = False
Set ws = ActiveSheet
Sheets(ws.Index + 1).Delete
ws.Move Before:=Workbooks("Sluttet.xls").Sheets("sheet2")
'Moves active sheet to beginning of named workbook.
'Replace Test.xls with the full name of the target workbook you want.
Application.DisplayAlerts = True
End If
End Sub

This is the type of macro I useually use on my pc to check if a workbook is open and if not then open it

If IsWorkbookOpened("Filename.xls", "C:Documents and ..................

View 3 Replies View Related

Workbook Open Code Fails

Oct 4, 2006

I tried testing, msgbox displays the correct last modified file "MMO Activity Report 09-29-06.xls" in path D:MMOWorkfile but I was not able to open the workbook.

Sub testGetNewestFile()
MsgBox NewestFile("D:MMOWorkfile", "*.xls")
Workbooks.Open (NewestFile("D:MMOWorkfile", "*.xls"))

View 9 Replies View Related

Code To: Open Another Workbook And Close Current

Jul 31, 2007

I want code to:
Open EstimatingSheet.xls
SaveAs current workbook as Range B13 &".xls"
Open EstimatingSheet.xls workbook from somewhere on C:
Then close the one with B13.xls

View 9 Replies View Related

Delete Open Or Closed Workbook Code

Aug 8, 2007

I am trying to write code in the VB behind Excel that will allow me to navigate to an excel file (can be open or closed) and delete it. ie navigates to file xxx.xls at N:/xxx/xxx/ selects the file and then deletes it.

View 4 Replies View Related

Modify Existing Macro To Copy To Different Workbook Instead Of Open Workbook?

Dec 7, 2012

Here's my macro:

Code:
Sub CopyRow()'
'Copies row to new sheet, highlights it, marks column 'A' as copied.
'
Dim cCell As Range
Set cCell = Selection.Cells(1, 1)
Selection.Copy
Sheets("Sheet2").Select
Rows("2:2").Select

[Code] .....

Is it possible to modify it to paste into a different workbook called c:filesDestination.xlsm, instead of the existing workbook (Source.xlsm)? The destination sheet name is the same (Sheet2). It's OK if both workbooks are open at the same time.

View 2 Replies View Related

Skip Saving The Code Everytime You Open The Workbook

May 8, 2009

I have a workbook with lots of code to automate several things that I do. It does a great job at doing things I need it to do, but it takes foreeeveeerrr to save the workbook. The code has made the workbook a large file, and it takes a few minutes to save it. Is there a way to skip saving the code everytime you open the workbook. I don't ever need to change to code so I don't need to save it every time.

View 3 Replies View Related

VBA Code To Select Specific Sheet Upon Open Of Workbook?

Jan 11, 2013

I am looking for the code that I would use so that when the workbook is opened it would always open with "Main" sheet.

View 4 Replies View Related

VBA Code To Allow User To Select Range In Another Open Workbook?

Jul 26, 2013

I need the code to allow a user to select a column of their choice in an open workbook of their choice so that data can be lookuped up and changed. I am happy to create the code to do the lookup/changing, however the nearest I have found is the use of an inputbox which is limited to the active spreadsheet only. Some people have said the input box could be used over to another spreadsheet but I have not found that to work.

What I will need returned is the workbook name, sheet name and column. That will allow me to point to that and do the task.

This code could be used by 5 people and those 5 people could be running it on 20 different spreadsheets.

I thought this would be easy to find but it has eluded me. When using Excel normally, if you do a simple lookup formula, or create pivot function, excel prompts the user to select the workbook/sheet.

View 4 Replies View Related

Run Macro In Open Workbook When Workbook Becomes Activated Or Deactivated?

Apr 8, 2013

Is there a way to run a macro in an open workbook when the workbook becomes activated or deactivated? So if I have workbook A open with a macro in it and workbook B open as well, can I have the macro run on workbook A when that becomes the workbook I see on my screen?

View 2 Replies View Related

Object Defined Error When Code In Workbook Open Event

Sep 7, 2007

I have a macro that checks if a username is in a particular list, and if it is, it unhides certain sheets in the workbook.

The code runs fine if I just run it as a macro or off a command button, but I am trying to execute it when the workbook opens and I keep getting a 57121, Application defined or object defined error.

The code is below;

Private Sub Workbook_Open()

DoEvents

Dim Res1 As VbMsgBoxResult
Dim GovRng As Range

For Each GovRng In Sheets("Map").Range("GovernanceMembers")
If GovRng.Value = Application.UserName Then Goto 111
Next GovRng

Exit Sub

View 6 Replies View Related

Prompt Open Closed Workbook Then Copy Range And Paste Special Into Open Workbook

Apr 6, 2013

I have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.

I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.

I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need

A prompt to open workbook
Copy range (c8,d69)
Close work sheet
Paste special .value (c8,D69)

I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.

week 1 week 2 week 3 week 4
Product
quantity
cost
quantity
cost
cost
quantity
cost

1
2
3
4
5
6
7
8

View 7 Replies View Related

Macro Coding: Add A Code To The Sort And Paste Macro That Will Open The Second Spread Sheet

Jul 21, 2007

I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the

Windows("estimate sheet one.xls").Activate

View 2 Replies View Related

Open File Macro: Same Directory As Macro Workbook

Nov 17, 2006

I created folder on my desktop to hold various documents. I have master excel document which contains forms and command buttons that open documents from same folder. Is there a way to change code instead of having full file path to specify current directory. I'm worried that if I move this folder to another location then my open file button will not work since location is changed.

Also when I open with command button word document, I have a button on word document to close. I can close document but word application is still active. Can I close word application with the document at the sam time. This is the code I'm using: "ActiveWindow.Close"

View 3 Replies View Related

Run Macro With More Than 1 Workbook Open

Apr 29, 2009

I currently have a macro set up to run once a workbook is saved/ closed. This works fine unless tehre is more than one workbook open. When I try it with more than 1 different workbook open I get a run error.

My code begins:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

and then performs the macro. I don't really want to specify the filename as this is liable to change. Is there anyway of adding a "this workbook" command to the text string, and if so where would it go?

View 10 Replies View Related

Macro To Open New Workbook

Dec 16, 2011

if workbook("Result") is not open then
add new workbook and name it "Result + date and time"
else activate workbook "Result"

View 3 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 Code - Open As Read Only

Jan 21, 2009

below code

Sub Collect_OS_Data()
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Dim fso As Object, fld As Object, fil As Object, fldPath As String, wbSrc As Workbook, wbCur As Workbook
fldPath = "Q:PublicPAYMENTS Q&RREPORTSSuspense Activity BUSINESSOngoing - Suspense BUSINESS - Activity Reports"
Set wbCur = ActiveWorkbook
ActiveSheet.Name = "Raw Data"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.getfolder(fldPath)
On Error GoTo ErrHandler
For Each fil In fld.Files

This opens all spreadsheets within a folder and takes the data i require out of each one and pastes to a new workbook, however the files are password protected. Can you add into the code to open each spreadsheet as a readonly copy and paste data and then close without a save prompt or denying the save prompt ?

View 9 Replies View Related

Open A Pdf File In Macro Code

Aug 11, 2004

how to open a pdf file (located: "C:myfile.pdf") in macro code?

View 4 Replies View Related

Open Web Page Via Macro Code

Feb 24, 2008

I have been looking for some VBA code that simple, when it runs, opens a browser and a specific, hard coded website. Is this possible in VBA?

View 2 Replies View Related

Open Web Page Macro Code

Mar 5, 2008

I am currently using the following code in order to open a web page by clicking a button:

Private Sub Timetable18_Click()

ActiveWorkbook.FollowHyperlink Address:="http://journeyplanner.tfl.gov.uk/user/AHF/JP15__00006e8e.pdf"
NewWindow = True

End Sub

However, when this is executed it returns the following error:

Run-time error '-2146697210 (800c0006)':
Method 'FollowHyperlink' of object '_Workbook' failed

Clearly, I'm doing something wrong. I wondered if it had anything to do with the fact that the webpage I'm trying to open is a pdf and not html or similar, but this doesn't appear to be the issue.

View 4 Replies View Related

Start Macro At Workbook Open

Nov 14, 2008

I found an outlook plugin that saves any incoming emails as text. It also has an option to run an exe or bat file on saveing the email to text. I have a macro in a worksheet that will process the email to a database and send out a response.

What I am trying to figure out is how to run the macro in the worksheet whenever an incoming email is saved. So I have the 2 components that I need, I just need to tie them together so that everything is automated.

Does anyone know how to achieve this? Can I use a batch file to open an excel workbook and run a macro? Or can I create a standalone macro (outside of excel) that can open the excel file and run the macro.

View 3 Replies View Related

Open Workbook Macro Only Runs Once..

Jun 30, 2009

i have a button on my workbook1 that opens another workbook (lets call it workbook2) and automatically runs a macro on opening it. Once opened i have a button on workbook2 to close itself. I then go back to see workbook1. This all works but then when i click on the button to open the workbook2 again it opens but the macro does not run.

View 4 Replies View Related

Trying To Get A Workbook.Open Macro To Work

May 29, 2007

I'm trying to get a Workbook.Open macro to work. I"m using the exact name for the location. For example:

Workbooks.Open ("O:AdministrationPM Weekly's2007PMWeeklyWE")

My other locations are working fine. For example I am using:

Workbooks.Open ("O:AdministrationDCS Week EndingStats"

View 9 Replies View Related

LastRow Does Not Seem To Look In The Workbook That The Macro Has Open

Jan 15, 2008

The problem is that LastRow does not seem to look in the workbook that the macro has open, and always returns a value of 1. If I place the sheet that i want to run the macro on, in the workbook that contains the macro there is no problem. What do I need to do to fix this.

LoopCount = Sheets("Dates").Range("A" & Rows.Count).End(xlUp).Row
rng = 2
i = 1
Do
Selection.AutoFilter Field:=7, Criteria1:="=" & Sheets("Dates").Range("A" & rng), Operator:=xlAnd
z = Range("B" & Rows.Count).End(xlUp).Row
For y = 2 To z
If Rows(y).Hidden = False Then Exit For
Next y
MsgBox y
MsgBox z
Range("H" & z).Formula = "=Countif($A$" & y & ":$A$" & z & ",A" & z & ")"
Range("H" & z).Copy Destination:=Range("H" & y & ":H" & z)
rng = rng + 1
i = i + 1
Loop Until i = LoopCount

View 9 Replies View Related

Macro: Write To Another Open Workbook

Nov 17, 2006

Trying to Figure out how to open another Excel (based on an template). From Dok1 I start A Macro To Open Doc2.xlt (using Workbooks.Add). Then I Would Like To Write to a Cell But I end Up Writing To The Cells Of Dok1 Even Though It's Dok2 That Is Visable and Activated. I allso do this with word, I have no problems there (Excel to Word) But there i'm using Bookmarks To transfer data. As far as i know , you can't use bookmarks in excel.

View 4 Replies View Related

Check If A Workbook Is Open Macro

Jun 21, 2007

I am looking to check if its open, if it is, then copy the row, if not, then close it.

I am still new to vb, so learning as I go, but looked at many other posts, and there are so many ways i have seen to open one, I'm not sure which is the best, and none that I have seen show me if they check if its open.

View 9 Replies View Related

Macro To Open Workbook With Password

Jul 25, 2007

I have a workbook with the following

Sub Workbook_Open()
Sheets("Occ-Rent-Concess Worksheet").Select
ActiveSheet.Unprotect Password:="pass"
ActiveSheet.Select
Cells.Locked = True
ActiveSheet.Protect Password:="pass"
pass:
UserName = InputBox("Enter Log On Name to Continue..............................................

This code opens a msgbox that asks for a password and then unlocks certain cells based on what the password is. I have 140 of these workbooks. I need to be able to open them all and change one cell in each file, save it and close. I've gotten the macro written to open, change and save the files, but I am still having to type the 'password' 140 times. Is there a way to bypass it?

View 4 Replies View Related