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 RepliesI have a macro that I run every day that creates a new book - Book 1, does stuff to it and saves it with a new name.
I always leave my computer on (and logged on) overnight. when i leave for the day, I just Lock it - CTRL ALT DEL - Lock Computer.
almost always this works fine, I run that macro every day, and almost always it uses Book 1 when creating the new book. Even though I haven't logged off/on since the last time it ran...And the macro works fine referring to Book1.
But every once in a while, I can't put any schedule on it, it doesn't work. Excel remembers that I've already created book1, and moves to book2. then the macro fails because it's looking for book1. - easily remedied by debugging and replacing book1 with book2....but a pain.
I also notice that at the same time that happens, I have another wierd problem...
I have a web query that I refresh every day. When I first open the worksheet for the day, I have to go and enter the userid/password for the web query.
but any time the 1st problem is happening, I no longer have to put in the userid/password in the web query.
here's the snippet of code that copies 4 tabs from one workbook and puts it into a new workbook...
Sheets("Manager MTD").Select
Sheets("Manager MTD").Copy
ActiveWindow.ActivateNext
Sheets("Manager WTD").Select
Sheets("Manager WTD").Copy Before:=Workbooks("Book1").Sheets(1)
ActiveWindow.ActivateNext
Sheets("Agent MTD").Select
Sheets("Agent MTD").Copy Before:=Workbooks("Book1").Sheets(1)
ActiveWindow.ActivateNext
Sheets("Agent WTD").Select
Sheets("Agent WTD").Copy Before:=Workbooks("Book1").Sheets(1)
the end of the macro saves the new file with filename ACP_Temp.xls
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.
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.
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 View RelatedI'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
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?
I had made some code changes that didn't end up working as I wanted and this called for PERSONAL to be called and acted on when excel was activated. Now I have deleted the code but still on opening excel is persists in loading PERSONAL. urk. How can I go back to loading Book1 on open?
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 RelatedRight 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??
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.
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.
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...................................
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 RelatedI 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
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 RelatedI 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.................
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:............................
"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].....
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.
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
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]....
I have this code in Book1:
sub OpenCloseMacro1()
Workbooks.Open Filename:="E:closeopenBook2.xlsm"
Workbooks("Book1.xlsm").Close SaveChanges:=True
Application.Run ("Book2.xlsm!Macro1")
End Sub
It closes Book1 and saves it, it opens book2, but Macro1 does not activate. Why?
by the way. Macro1 is:
Sub Macro1()
Userform1.show
End sub
sub OpenCloseMacro1()
Workbooks.Open Filename:="E:closeopenBook2.xlsm"
Application.Run ("Book2.xlsm!Macro1")
Workbooks("Book1.xlsm").Close SaveChanges:=True
End Sub
It opens book2, and activates macro1, but Book1 is not closed
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?
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.
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.
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
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
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.
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