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
ADVERTISEMENT
Mar 30, 2007
I have a Macro that is stored in a specific workbook,"Projection Modeler.xls" the last line performed in this macro is the close workbooks without saving chages(this needs to be done due to the way this macro was written, and it would take too long to rewrite). If I would then like to reopen this workbook, and then rerun this macro, can I do this from inside the current macro (in a new sub routine, for instance), or do I have to write a macro outside of the workbook and then open the workbook and call the routine?
View 4 Replies
View Related
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
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
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
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
Nov 11, 2011
Sub Macro2()
Dim stgSheets As String, Cnt As Long
Dim arrSheets()
Cnt = Sheets.Count
ReDim arrSheets(Cnt - 1)
For i = 1 To Cnt
stgSheets = stgSheets & Sheets(i).Name
arrSheets(i - 1) = stgSheets
'sSheets = sSheets & "", ""
Next i
[Code]...
View 2 Replies
View Related
Feb 12, 2010
I have a workbook with roughly 28 tabs(sheets) and i need to be able to run a macro that will create a new sheet for each tab and name it based on the tab name.
View 9 Replies
View Related
May 5, 2014
New to using Excel formula's and am trying to create an IF/LOOKUP formula to look at multiple sheets within a workbook and display the information within the 'compare' sheet.The yellow cells are where data will be entered.
What I am trying to achieve:
Type the store numbers on the compare sheet (B2 and D2). For the sheet attached I have put 190 (in B2) and 2012 (in D2)
B2 store shows the predicted and actual values of 190 in columns B & C
D2 store shows the predicted and actual values of 2012 in columns E & F
(No need to worry about variance and difference columns)
So, if I change the store numbers in B2 and D2 to any of the sheet numbers, I want it to display the correct info for that particular store within the compare sheet.
I have attempted a formula, which you can see... I have basically looked at some previous sheets that had IF and LOOKUP on it and tried to replicate that for my sheet, but with no luck.
View 3 Replies
View Related
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
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
View Related
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
May 30, 2014
Trying to create a new workbook from another open workbook, then copying all the sheets that aren't called "Summary" to that new open workbook and then saving it. I get a subscript error on this line:
[Code]....
View 3 Replies
View Related
Feb 7, 2014
I am looking for a code that would copy the data from each worksheet in a given workbook and then paste to just one worksheet within a different workbook. The Sheet names are auto generated when I run this canned report but the naming structure is always the same...the first worksheet is named Repair Details and then the next sheet is named Repair Details_1, the next sheet is named Repair Details_2 and so on for every sheet in workbook. So I would like to copy all of the data(Headers to last cell) and then paste in a worksheet(ex: Master Repair Report.xlsx and the worksheet could be titled Master Repair Details) on a different workbook, then the next sheet would copy from the one under the header to the last record and paste to the same workbook. This process would repeat for every worksheet in the Repair Details Workbook and paste to Master Repair Details worksheet in the Master Repair Report workbook.
View 4 Replies
View Related
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
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
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
Jul 3, 2013
I do a payroll workbook for my small company. Each sheet inside the workbook is a month. Each sheet totals the monthly labor categories. I would like to keep a "year to date" total on each employee and therefore would have to reference each sheet.
Setting up a formula (or tell me where to look) on how to total worksheets inside a workbook?
Example: the workbook contains a total of 12 sheets, representing the 12 months of the year. Add "cell reference" from sheet 1 to sheet 2 to sheet 3 to reach a total, then when sheet 4 is completed it contains the cumulative totals of all 4 sheets, etc., etc. Where I can look at this "year to date total" and compare to last years "year to date total".
View 2 Replies
View Related
Nov 14, 2013
Macro command to run a macro in all sheets of a workbook
View 6 Replies
View Related
Feb 3, 2013
I have a macro (see below) that simply formats a sheet for printing. I am currently clicking individually on each sheet in the workbook, and running the macro. Is there a way to have it automatically loop through all the sheets in the workbook?
My current macro is:
Sub Set_Print()
'
' Set_Print Macro
'
Application.ScreenUpdating = False
[Code] .......
I have about 10sheets / workbook, and 80 workbooks to do, all saved in the same folder.
View 2 Replies
View Related
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
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
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
Jan 24, 2014
I'd like to create a macro that will take 10 workbooks and add them to a new work book, but have each workbook as a separate sheet.
So every month from work I get a report file labeled as the current month (JAN_14). Then there about 19 folders in that folders labeled with product names; then in those folders are two excel files (they're all named MSTASCH_QUICKLOOK_1 and MSTASCH_QUICKLOOK). I figure if I can see the macro to combine 10 workbooks I can change it to do less or more workbooks. I had tried a macro that takes the workbooks from one file and puts them into two seperate sheets (although I would love to have the order changed of how they're put into the workbook).
VB:
Sub MergeSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
[Code]....
View 4 Replies
View Related
Oct 18, 2013
I have a macro listed below that I would like to use on multiple sheets within the same workbook.
Sub PrintMacro1()
'
' PrintMacro1 Macro
' To change print format from landscape to protrait
[Code].....
View 4 Replies
View Related
Jun 3, 2014
I have a workbook that I edit daily then pass along to another team. All of my modules are passwork protected and there are also several sheets in the workbook that I hide before email it to the other team. I hide the sheets by changing the sheet properties in the VBA editor to "2 - xlSheetveryhidden".
This isn't a very time consuming process but I am wondering if there is a way to automate it through VBA?
I tried the the fail safe of "see what happens when I record it" but all I got was an empty Module.
View 2 Replies
View Related
Mar 2, 2009
my code to loop through sheets in another workbook does not want to work. in This Workbook (SourceWb), I copy the current workbook using SaveCopyAs and then reopen the new workbook. from ThisWorkbook (SourceWb), I am trying to delete any sheet between "First" and "Last" where a specific criteria is not met. so starting from the For Each to get my loop values, where PMs is a list of people's initials:
For Each c In Range("PMs")
If c.Value = "" Then GoTo AfterArrays
View 9 Replies
View Related
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
Nov 9, 2009
I'm trying to use some data from other workbooks in a formula. However, the path to the other workbooks we are using will often change.
I think that the user can enter the path to the other workbook in a cell in this workbook. However, I'm getting errors in my formula and am not sure how to make the formula use the cell to reference the location of the workbook that has the data that we want.
This is what I have, but it's giving me an error.
=Countif(' & 'Instructions'!B9 & Planning'!$I$3:$I$100, "C")
'Instructions'!B9 contains the location of the other workbook in the format \path[filename] Planning'!$I$3:$I$100 is the location of the range in the other workbook.
View 7 Replies
View Related
Jan 13, 2009
Could some one amend the VBA below to allow a continous loop on moving to the previous sheet within a workbook. Currently when I move to the first sheet if I use the shortcut key I get a debug error.
View 6 Replies
View Related
