Create Macro That Can Be Used In Any Workbook?

Jun 11, 2014

I would like to create a macro (with button) that can be used in any workbook. I tried creating a macro in a blank workbook and added a macro button to the Quick Access toolbar. It shows up in any workbook I have open BUT when I run the macro, it also opens up the blank workbook.

View 1 Replies


ADVERTISEMENT

Macro To Create New Workbook And Sheets Inside Each Workbook

Mar 31, 2009

I have a spreadsheet which has all the names of trips from a warehouse, the day that they operate (1,2,3 etc) and the job line allocated to each trip. It looks something like that:

A B C
DAYTRIPCUST

I would like to create a macro that will be creating 7 new workbooks and then in those workbooks as many sheets as the trips. In these sheets, the customers should be displayed.

How do I write it? I could not find how to have a "dynamic choice" in the macro. I.e. not to have the criteria as "1", "trips1" but to choose from the range of inputs that are available.

View 9 Replies View Related

Create New Macro In New Workbook With VBA

Apr 30, 2013

Is it possible to create a new workbook, add a macro to it, set it to run as an auto open event and then save and close the new workbook?

To explain a little better, I have a macro in a workbook. This workbook is opened by Filemaker which triggers the macro to run. The macro creates a number of different workbooks using information from an online file dump which is then imported into Filemaker.

However when the import is finished, I'd love for all the files that have been created to get deleted again. Now I realise I could just create a second workbook that Filemaker opens once the import is finished that deletes all the files. However I want to keep things as simple as possible for the user, and for them to have as few files as possible so things are less likely to get misplaced in the future. My concern is as they never see the workbook macro firing, they won't realise how important it is, even if I make it very clear.

So what I was thinking is at the end of the first macro, it creates another workbook, with an auto open event macro in it that simply calls a macro from the original workbook, and once created it is saved and closed. When Filemaker is finished with it's import, it could then open the new workbook which calls the macro in the original workbook. I could then have the original workbook macro delete all the added files, and close and delete the other macro enabled workbook it created. Which would leave me with the original workbook only.

View 3 Replies View Related

Macro: Create A New Workbook

Feb 17, 2010

I've created what I thought was going to be a simple macro which will create a new workbook, rename it "Applications Calls", then copy a pivot table/report from another workbook and paste it. Here's my rather amateur code which doesn't work.

Sub copyreport()
Workbooks.Add
ActiveWorkbook.Name = "Top Ten Apps Calls.xls"
Windows("Applications Calls Logged North.xlsm").Activate
Sheets("Calls Logged by Customer").Select
Cells.Select
Selection.Copy
Windows("Top Ten Apps Calls").Activate
Cells.Select
ActiveSheet.Paste
Range("A16").Select
ActiveSheet.PivotTables("PivotTable5").PivotSelect "Silo", xlButton, True
ActiveWindow.DisplayGridlines = False
ActiveWorkbook.ShowPivotTableFieldList = False
Range("A16").Select
End Sub

View 9 Replies View Related

Create Macro That Won't Allow Movement With Workbook Until Macro Run

Jan 28, 2014

Writing a Marco. I would like to place a macro on the opening page of a workbook and like to a button that executes three functions.

1) I need to hide selected tabs in a workbook
2) I need to copy paste values of a couple select cells in a workbook
3) Lastly, I would like to set up the workbook so that the use can not move beyond the opening tab with out clicking the macro to preform the steps above.

Is this possible? I know 1 and 2 are for sure, what about 3?

View 1 Replies View Related

Macro To Create New Workbook With Name Other Than Book1?

Oct 28, 2008

I'm tryign to create a macro which will copy certain worksheets into a new workbook (i.e. Book1), and the macro continues to work with this Book1. Is it possible to create a more robust macro which will rename Book1 into something usable (i.e. "Report")? because if the session of Excel has already had another worksheet, it will go to Book2, and then the macro won't work.

View 5 Replies View Related

Macro Will Not Create New Worksheet And Workbook

Oct 28, 2009

Im trying to create a document that creates a new workbook when certain functions are selected and have written a macro to assist with this. Basically im working on a comprehensive checklist with all possible scenarios that will need to be tailored for use, so you can create a document with just what is applicable to you and not the whole checklist. I have attached a sample of what i mean, along with clearer descriptors of what the document is designed to do.

View 5 Replies View Related

Create Workbook With Macro Code Within

Jan 11, 2007

Right now I use a VBA-Code to create new worksheets in a workbook. my Question:
is it possible to attach code to the newly created Worksheet via vba-code??

View 3 Replies View Related

Create A Simple Macro To Open A Second Workbook?

Jan 31, 2014

I am trying to create a simple macro to open a second workbook if the value in a cell in the current workbook is 'x' or if 'y' do nothing.

Basically I have a simple sales ledger, one column has a yes/no for whether or not an invoice is paid if no I want to open a second workbook 'debtors' so that I can manually input the details. I'm sure this is a very simple proceedure but I can't seem to crack it.

View 2 Replies View Related

Macro To Create A Copy Of Current Workbook

Mar 12, 2014

I have a workbook with Macros in it, now I want to create a copy of that workbook using VBA.

Here are the steps i want:
1. Create a copy of an open workbook
2. Open the Copy.
3. Close the original workbook.

View 1 Replies View Related

Create & Save Workbook To Same Path As Macro File

Aug 21, 2008

I have code to create a new workbook, and when I try to rename it I get "Compile error: Can't assign to read-only property."

Dim objXlApp As Object
Dim wkb As Workbook
Dim wks As Worksheet
Set objXlApp = CreateObject("Excel.Application")
' Create a workbook
Set wkb = objXlApp.Workbooks.Add
' Delete all worksheets bar the first one.
For Each wks In wkb.Worksheets
If Not wks.Index = 1 Then
wks.Delete

End If
Next wks
'Create some worksheets and names
With wkb
.Worksheets(1).Name = "myWorksheet1"
.Worksheets.Add.Name = "myWorksheet2"
.Worksheets.Add.Name = "myWorksheet3"
.Worksheets.Add.Name = "myWorksheet4"
End With...................................

View 5 Replies View Related

Create Username And Password To Login Into A Macro Enabled Workbook

Jan 3, 2011

I need a macro ie when a user opens a macro enabled workbook,he should be asked for a username and password to access workbook. Administrator should have right to create users with reset password rights

View 5 Replies View Related

Macro To Create Sheets In New Workbook Based On Template And Evaluator?

Feb 10, 2013

I had created a base data with evaluator and performance appraisals templates which should be sent to each evaluator for filling up the forms based on templates

Next,

Macro that would i require will be

Where in my base data if i select filter of "Arun", i will performance appraisal templates in column I. Next, i have sheets individually created templates which refer column I

So, based on arun filter if i select, it should create a new workbook for arun and six individual sheets of employees reporting to him with a base data and appraisal templates similarly if i select anita in filter , it should create a new workbook for anita and six individual sheets of employees reporting to her with a base data and appraisal templates

View 9 Replies View Related

Macro To Create Worksheet 'xxxx' And List All Worksheets In Workbook

Feb 2, 2009

I am after some code to create a new worksheet called 'xxxx' in my workbook, and then list all the sheets within the workbook (excluding the newly created 'xxxx' sheet.)

View 4 Replies View Related

Error Handling Within Errors (macro To Create New Worksheets In The Workbook)

Aug 12, 2009

I am currently creating a database which involves using a macro to create new worksheets in the workbook. When the macro is run and a work sheet is being inserted, an input box asks the user for a worksheet name.

As you can imagine, the worksheet does not like it when the worksheet name input by the user, is the same as one already existing and so throws up a 1004 error.

In order to resolve this error I have included an error handling code to request the user to input a differnet worksheet name, as the one previously inserted exists.

My problem: It all works fine until the user types in an existing worksheet name twice, so once initially and again when the error handler has prompted a second attempt. On the second incorrect input a 1004 error warning is displayed.

I would like the error handler to keep repeating until a worksheet name that doesn't exist is inserted by the user. Is this possible?

My code so far:

Sub NewTrancheSheet()
'
Sheets("Tranche Sheet Template").Visible = True
Sheets("Tranche Sheet Template").Select

Sheets("Tranche Sheet Template").Copy Before:=Sheets(1)

Sheets("Tranche Sheet Template (2)").Select
Sheets("Tranche Sheet Template (2)").Move After:=Sheets(Sheets.Count)
Sheets("Tranche Sheet Template (2)").Select.................

View 9 Replies View Related

Macro: Create Directory Based On Active Workbook And Save To It

Sep 8, 2006

Read “My Documents” Path And Use Result

Problem:

Note:

Typical user OS will be Windows XP Pro / Win 2K
Excel version : 97 / 2002 / 2003

1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers.

2. Per customer request, VBA application needs to save extracted files for future use.

3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”.

4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following:

Dim filepath As String
filepath = ThisWorkbook.Path
As an example – this code would produce a string definition of “filepath” – such as the following:............................

View 5 Replies View Related

Excel 2007 :: Create A Main Macro To Control Other Macros Within A Workbook?

Nov 7, 2012

"How to create a 'main' macro to control other macros within a workbook". I have my individual macros created. There's one macro for each sheet that searches online data and returns the information. I have one of these per sheet (a total of 20 sheets) since I couldn't find a way to have all 20 macros be on one sheet and still work. My trouble is that whenever I'm on my 'main' sheet and try to run the macro which applies to a 'secondary' sheet, I get an error. I have to select the sheet first, then run the macro and it works. Below is the macro on each sheet.

VB:
Sub Holding1()
Dim DataSheet As Worksheet
Dim EndDate As Date [code].....

View 8 Replies View Related

VBA Macro To Create Multiple Workbooks From One Master Workbook By Cost Centre

Jun 27, 2013

VBA Macro to work through a worksheet that consist of static data (tab 4) cost centres and to populate a new work book per cost centre consisting of three tabs for every cost centre found in the static data.

The master Workbook has the following tabs:

Tab 1 is called travel and consist of column a which is the cost centre number (plus 14 other columns)

Tab 2 is called Mobile and consist of column a which is the cost centre number (plus 14 other columns)

Tab 3 is called Expenses. and consist of column a which is the cost centre number (plus 14 other columns)

Tab 4 Static Date column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns).

If no information found on a specific cost centre, the tab will include the headers and return the words "No transactions for this period"

Whilst splitting data into Tabs the workbooks should check against the Static Data table and include cost center description in Column B of each tab in the new workbook.

If master workbook consist of the following....

Tab 1 is called travel and consist of column 1 which is the cost centre number (plus 14 other columns), cost centres, 555,557,

Tab 2 is called Mobile column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns) cost centres, 555, 78689,

Tab 3 is called Expenses. column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns), cost centres, 555,

tab 4 Static Date - column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns). cost centres, 555, 557,78689

It should output 3 workbooks by cost centre number.

One for 555, which consists of 3 tabs, travel, mobile and expenses.

A second for 557 which has 3 tabs travel, mobile and expenses, but only with data in the travel tab.

A third for 78689 which has 3 tabs travel, mobile and expenses, but only with data for mobile data.

The workbooks will be replicates of the contents within the tabs where column a wil be the cost centre plus 14 additional columns.

View 5 Replies View Related

Excel Macro To Create New Workbook Based On Category In List With Respective Sheets

Apr 27, 2014

I need new workbooks based on category name in the below list. Respective sheets should be copied from the existing workbook with items as sheet names. List can be updatable.

category(workbookname)Items(sheets)
Vegetables carrot
vegetables brinjal
meat chicken
meat Fish
meat Mutton
grains Wheat
grains Rice

View 4 Replies View Related

How To Create Search Macro Button That Allows To Search In Multiple Worksheets In One Workbook

Oct 11, 2011

Im am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do

Private Sub SearchButton_Click()
SearchString = InputBox("Enter Search String", "Search")
If SearchString = "" Then Exit Sub
For Each c In Range(myRange)
If InStr(LCase(CStr(c)), LCase(SearchString)) Then

[Code]....

View 1 Replies View Related

Delete Sheets And Create New Workbook In Shared Workbook

Jun 2, 2014

I wrote a code in unshared workbook and it works fine. But when i make it shared i get Run-time error '1004' Delete method of worksheet class failed.

The Deleting of worksheet only occurs once (when the new wb is created) so is there i can unshare and share it back when the process is complete?

View 3 Replies View Related

Create New Workbook With Separate Sheets From Data In Another Workbook

Jul 16, 2012

I have been tasked with creating a macro which creates a new workbook wherein each sheet contains the information for one site from the active sheet. The active sheet already has the values sorted by the site such that all information needing to be copied from the active sheet into the new workbook is together.

I.E.

ATL
ATL
ATL
ATL
CEN
CEN
JCK
JCK
etc.

There are 8 different sites on the active sheet: ATL, CEN, DAL, HAR, JAS, JCK, VIS, NOV

The macro needs to find the range for all of the data of each site and copy/paste that data into a new workbook such that ATL would have its own sheet, CEN would have its own sheet, and so on. The data ranges from A:R.

So, for example, the macro would find that the last row with ATL in the "B" column is 6095 and would then copy A2:R6095 and insert that data into the new workbook under Sheet 1.

I had some code that I had adapted to select the range for each of them, but the code loops through the entire sheet (which is 44,307 rows long) for each site making it a quite clunky and very slow step in an even longer macro. Since the data is already sorted, I know there must be a way to have the macro stop searching when it reaches data not equal to the data the row before, however, my experience with VBA is limited, and I have been unable to find a solution. Also, the data does not have to be conserved after being sent to the new workbook, if that would speed up the macro.

View 5 Replies View Related

Copy From Closed Workbook Then Create New Workbook

Jan 31, 2007

I've got a problem with this code, have been wracking my brains about. Here is the process I am trying to do:

1) Copy a range (a2:av1000) but (ideally) find the last populated row from a closed workbook (with a different password)
2) Create a new workbook and paste this data into it at A2
3) Close all the workbooks but only save the new one.

View 9 Replies View Related

Create Macro That Can Create Dynamic Copy / Paste Loop?

Feb 12, 2014

I need to create a macro that can create a dynamic copy/paste loop. So far what I have is horribly inefficient. Each row in colmn A(minus the header) has a unique number in it. For each unique number, I need to paste it based on the number of column headers in row 1(minus column A). So, if there are 20 column headers, I need to copy cell A2 and paste it 19 times in another sheet. Then, I need to move to the next number in column A and do the same thing. Here's what I have:

[Code] .........

You can see that this is not dynamic. If I add another row to my table and rerun the macro, it will not catch it. I've attached a sample file to show you the big picture of what I'm trying to do. The data that I have is in Sheet1, and I'm trying to get it into the format in Sheet3. Rows/columns will be periodically added to the table in Sheet1, so the macro needs to be dynamic to catch that. The data in Sheet3 will always remain, and the macro will add the updated data below the old data in Sheet3.

FC_Macro_Sample.xlsm

View 8 Replies View Related

Msg Box To Create New Workbook

Aug 24, 2008

way to create a msg box with if you click "yes" it will copy the data from sheet 4 on workbook 1, and paste it onto a new workbook that is a copy of a standard template of workbook 1, and rename it based on a file name you give it.

ex. Pro Shop Template

Workbook 1 is Pro Shop 9/08-10/08

New Workbook 2 is a copy of Pro Shop Template but names Pro Shop 10/08-11/08

View 9 Replies View Related

Add-In To Create A New Workbook In Same Folder

Sep 9, 2008

Is there a way of creating a new excel workbook using a macro that is stored as an Add-In.

The new workbook is to be created in the same folder. If I use the following command in macro then it saves the file in the Add-In folder.

View 14 Replies View Related

Create Summary Workbook

Jul 10, 2009

I need a macro that will copy and paste the data from the range below from every file in a directory with each file in the directory on a new row. My goal is to build a database with the information in the files in the directory. I will need to be able to put the directory in the code at a later time as it has not be set up yet. it will be on the local computer and not on a network share.....

View 8 Replies View Related

How To Create Cash Up Workbook

Apr 15, 2014

I am new to macros on excel but I am trying to create a cash up workbook. This is what I want it to do:

First Sheet should be a summary of the month with each day. From there I want it to generate 4 worksheets for every day's cash up. (which should be Daily Cash up, Credit Card Sales, Cheque Sales & Account Sales).

The Daily Cash up Sheet must pull info from the other 3 and must allow me to input cash values manually. But the cc sales, cheques sales and account sales must be pulled from the other sheets.

After completion of cash up there should be a button on the Daily cash up sheet to say Finalise, where when it is pressed it summarises all totals on the first sheet. So when I open the workbook every morning, there should be a button on the first sheet to create a new 'cash up'.

Then there should also be a button on the first worksheet for month end, so that when you press month end it finalises the month and generates a new work book for the next month.

View 7 Replies View Related

Create A Help Section In A Workbook

Aug 5, 2003

I want to include a help section in a workbook I created. I need something more than just a MsgBox ;-)

I'd like it to be like the windows' help that you have everywhere.

View 9 Replies View Related

Create A New Workbook By Vba Of Excel

May 31, 2006

I want to the following steps to be done by vba of excel.

1. copy ranges of cells in the active worksheet (sheet3)
2. add a new workbook and paste the copied ranges to sheet1
3. save the new workbook with the new file name (e.g. date)
4. close the new workbook and clear the contents the copied ranges of cells in old workbook.

following code and make it more simpler?

Worksheets("Sheet3").Activate
If Range("l1").Value <> "" Then
Range("l1").Value = CDate(Date)
End If
asdate = Range("l1").Value 'date entered by user
resp4 = MsgBox(" Case particulars of " & asdate & " will be backed up and deleted from this sheet! Proceed? (Y/N)", vbYesNo, "Deletion of Data")
If resp4 = 7 Then
Exit Sub

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved