I have built a tool that automates the creation of some excel models. When each one of these models is created and deployed I need it to have all VBA code removed from it.
What vba command can I run that will strip all vba code out of a chosen workbook?
I have 10 very large workbooks that are all setup in the same format. In column Z is a numerical value from 1 to 83. I have been trying to filter the sheet and then copy one at a time from 1 to 83 but that takes a LONG time especially when there is 10 workbooks to do.
Is there anyway I can run a function or macro or something that would just automatically look down the column Z and put each row into a it's own workbooks?
I have attached a sample of what the workbooks look like right now.
Sample123.xlsx
I need to separate row data from one workbook into separate workbooks based on cell data. Currently I filter the data, copy and paste it to a new workbook and save it. It's a tedious process and was hoping to find a way to automate it. I have attached a sample file. In this instance, I would like a separate workbook for the filtered data in Column 1 and then all the row data gets copied to new workbook. So all of Pennsylvania data would get copied to new workbook, then all of the Michigan data gets copied to a new workbook. I have also attached an example of the end result that I need.
View 9 Replies View RelatedI m not able to use the standard Excel Paste Special function when I copied a range of cells in another Excel workbook opened in a separate instance of Excel. Instead, Paste Special thinks that I have copied some non-Excel objects and gave me the Paste As options. This is not the case if I open both workbook within the same instance of Excel. Could you share with us if there is a trick to trigger the normal Paste Special options in such situations (without having to invoke Macro procedures)?
View 2 Replies View Relatedi have a workbook blank template for my colleagues to fill in, they fill in this blank template and save it under the serial number of the item they are creating. However a lot of errors exist when they input the serial number inside the workbook where the serial number is a duplicate of a former serial number.
Anyhow i was wondering is there any way when they input the serial number into the worksheet that i could get it to save in a separate workbook and then have conditional formatting to check those serial numbers in the workbook to see if they match?
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.
Actual
Result
london#123;new york#34;
london;
new york;
delhi#145;chennai#54;
delhi;
chennai;
hyderabad#748;
hyderabad;
[code]....
removing the # and numbers from text and add new line after ; symbol. So it would save me a lot of time in preparing status.
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 RelatedI have a spreadsheet containing thousands of addresses, and I need to separate the zip code from the address. Each address is in its own cell, and the only divider is spaces (sometimes two spaces). I attached an example of what some entries look like. Is it possible single out the zip code? addresslist example.xlsx
View 12 Replies View RelatedI need a VBA code to take everything after a certain word and put it in a new cell below it. The cell would have View More in it most of the time, and sometimes spaces will be messed up, so it will have a name after it but it should actually be in the cell under it. So if the name is A Maple, it will say View MoreA Maple. So the A Maple need to go in a cell below it.
View 6 Replies View RelatedI'm trying to find out how to put my database in a separate workbook and still be able to access it with a listbox.
View 9 Replies View Relatedbelow is a VB code to email separate sheets based on sheet anme and email address on a sheet called " mail".
Code seems to fail at the last line " Next a" any ideas why this is not working
Sub Mail_sheets()
Dim MyArr As Variant
Dim last As Long
Dim shname As Long
Dim a As Integer
Dim Arr() As String
Dim N As Integer
Dim strdate As String
For a = 1 To 253 Step 3
If ThisWorkbook.Sheets("mail").Cells(1, a).Value = "" Then
Exit Sub
I have a list of .xls filenames contained in a sheet that I would like to utilize in separate formulas in the same workbook.
I can't seem to write a formula to utilize a filename in a separate cell to gather data from said filename.
In other words, I would like to do something like the following:
Cell A1: '[filename.xls]'
Cell A2: =A1Sheet1!A1
to gather the data from Sheet1 cell A1 in filename.xls, but it doesn't work no matter how I have tried. It seems the only way is to actually manually transcribe the filename into cell A2, but this doesn't seem right. There must be a way to do this.
Our department receives weekly and fortnighly updated workbooks from a number of different departments. Each workbook contains 10s of worksheets. Our department analyzes the data and generates reports from those worksheets.
I have developed a few macros to automate the report generation for making the computing of my colleagues easier.
Now, the problem is that all the macros are developed behind individual sheets and some modules. For this reason, my colleagues have to copy each updated sheet and paste over the older ones individually, each and every time. It is really quite time-consuming task - deleting older data from all the worksheets and then copying fresh data on each of them, one by one.
They want to make their life easier by just replacing the entire workbooks with the new ones and still want the macros functioning, as usual.
My concern is how I can put all the code in a separate workbook and how to reference data from the other workbook.
-I've been trying to get an answer to this problem for some time and I would like to try again from a different approach.
I may have 5 workbooks open in 5 different Instances of Excel. The number of files open varies. When the below Function is called, it checks to see if the file, trying to be opened, is already open or not.
Currently, if the file is already open, a mesage is displayed stating that it is open and the user has to search through tabs looking for the file so that it can be displayed.
What I want to do in place of a message, is activate the file that is already open and display it. No message necessary.
I have tried "Application.Activate" and can not make it work.
I am running a Macro to fix my hyperlinks in excel, and I have several worksheets that I want to preform the same function for but with different new and old strings. The code I'm running is the hyperlink fix code:
Sub Fix192Hyperlinks()
Dim OldStr As String, NewStr As String
Dim hyp As Hyperlink
[Code].....
is it possible to place sheet names beside the data? What I mean is that, I'm currently working on a report which extracts data from the current workbook (many sheets) and paste it into a new workbook. As there would be duplicates due to many different sheets. I would like to have the duplicates removed but have the sheets name which they are found in the columns beside.
View 9 Replies View RelatedJust wondering if there is a way to indirectly reference a workbook in a formula?
For example, suppose A1 has the path/filename of the workbook I want to reference.
So in another cell I want to do, say, a VLOOKUP(A2,A1,5), where A2 is the lookup value. This way, if I have multiple workbooks I want to look at, I can specify them all in various cells, and simply have the formula look at the right cell to perform the lookup.
I've played with INDIRECT and get something close if I do
=INDIRECT("'"&A1&"'!A1:AJ1500")
but that just returns the contents of A1 in the target sheet.
I have a table of data with three columns. In the first column I have different values, but they can also repeat. How can I have VB look at my table and for every distinct value in the first column create a sheet with the value name and paste all the data into that same sheet?
View 9 Replies View Relatedi have a large table, columns A:F, would like a code which would look at the text in column F and create a new row for each bit of text separated by comma
probably easier to show what i mean, here is a section of the table, in this example store 13121 needs multiple rows.
start with this:
HTML Code:
table.tableizer-table { border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif font-size: 12px;} .tableizer-table td { padding: 4px; margin: 3px; border: 1px solid #ccc;}.tableizer-table th { background-color: #104E8B;
[code] ......
and turn it into this:
HTML Code:
table.tableizer-table { border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif font-size: 12px;} .tableizer-table td { padding: 4px; margin: 3px; border: 1px solid #ccc;}.tableizer-table th {
[Code] .....
I am trying to make a "master" sheet where employees total calls for the day are recorded.
Each employee will be marking their calls each day which will be on an excel spreadsheet. i would like to then create a MASTER sheet that links to all the employees specific sheets so when they exit the sheet it updates the master, or it can be a live update if that is possible...
I have a large workbook with many worksheets which are all grouped into pairs - i.e. Sheet1 & Sheet2 go together, Sheet3 & Sheet4 go together; Sheet5 & Sheet6 go together, etc. etc.
I need a macro to divide this workbook into separate workbooks where each group of worksheets has a separate file of its own and I want to name the new workbooks after the second sheet in each group:
i.e. A new workbook for Sheet1 & Sheet2 called Sheet2.xls; a new workbook for Sheet3 & Sheet4 called Sheet4.xls; a new workbook for Sheet5 & Sheet6 called Sheet6.xls; etc. etc. etc.
Im working with a huge amount of data in BOOK1.xls and once or twice a week I received an update of the list.
1. I need to compare and find out from the new updated worksheet if there is a new revision, and if there are new documents added to the list. I have tried Vlookup..but it didnt work for me. Ex. Doc1 has revision 1, 2 in my BOOK1 that I am working on. Note that there is a repetition of the DocName, Doc Number except for the revision.
I have been working on this 2 weeks and have finally come to the conclusion. I am trying to use index/match to find an article number in any 1 of 3 attribute files workbooks. I have been am using Vlookup, but the Master attribute file workbooks DO NOT list the article number in column A (but list them in F) and therefore I have been creating an altered copy every month to place the article numbers in column A so VLOOKUP would work. Index/Match would be a more efficient look up formula as I could link directly to the Master Attribute files without altering the workbooks.
Below is the formula I have been using that looks in 2 attribute files workbooks
=IF(ISNA(VLOOKUP(A8,'C:Recon Workbook FilesService FilesAttributeFiles[TireAttributes.xlsx]TireAttributes'!A:C,2,FALSE)),
IF(ISNA(VLOOKUP(A8,'C:Recon Workbook FilesService FilesAttributeFiles[PartAttributes.xls]PartAttributes'!A:D,2,FALSE)),"",
(VLOOKUP(A8,'C:Recon Workbook FilesService FilesAttributeFiles[PartAttributes.xls]PartAttributes'!A:D,2,FALSE))),
(VLOOKUP(A8,'C:Recon Workbook FilesService FilesAttributeFiles[TireAttributes.xlsx]TireAttributes'!A:C,2,FALSE)))
Below are the three (the new formula would incorporate a 3rd attributes workbook "Other" to the search criteria) Index/Match formulas I would like to "Tie" together to find the article number in any of the 3 attribute files workbooks and return column A if the article number is present or nothing if the article number is not present in any of the 3.
=INDEX('C:JDAAttributeFiles[OtherAttributes.xls]OtherAttributes'!A:A,MATCH(A8,
'C:JDAAttributeFiles[OtherAttributes.xls]OtherAttributes'!F:F,0))
=INDEX('C:JDAAttributeFiles[PartAttributes.xls]PartAttributes
[Code] .....
I have read a number of posts and other sites and can create the hyperlink in the desired cell but get an error message to the effect, "Cannot open the workbook."
The current macro follows. I believe its description is made in the comment statements below the date-author comment.
Sub DecomposeCTQ()
'Decompose CTQ Macro
'14Oct13 Charles T. Carroll'
'This macro copies the template to a new worksheet and takes data from the'
' active cell CTQ and makes it the parent CTQ on the new worksheet. Then'
' it renames the new worksheet with the active cell CTQ ID.'
If ActiveCell.Column 2 Then
MsgBox "You must be in the CTQ ID Column to run this program"
GoTo Leave
[code]......
So I have a workbook (Workbook1) that opens a userform on launch. I have another workbook (Workbook2) that has a button to open Workbook1. Upon opening Workbook1 (and as a result the userform) I'd like to input data from Workbook2 into the fields of the userform. I'm not exactly sure how to reference the userform fields to input data from Workbook2.
I'm trying the following code:
Workbooks.Open (Workbook1)
With Workbooks(Workbook1).Userform1
UserformField1 = "Billy"
UserformField2 = "John"
End With
The macro is opening Workbook1 and the userform but the fields return blank.
I have a form which creates a budget based on user input. I want to save that user input in a separate workbook. My goal is to reduce the file size. I have several modules which perform evaluations/ calculations, and then format the output in a worksheet, but the size is pretty large 450KB.
I was thinking that I could set the control source, to different cells on the worksheet, and then just copy the worksheet to a new workbook, and save the data by itself. Then if I wanted to change the data for a given budget, I would copy the sheet back into the workbook that contains the modules, and load the form again. Is this a good solution? Is there a better way? Please let me know if I can provide more information.
Excel 2003: I need code that, when an "x" is entered in a cell in the "Activity" worksheet to assign a temporary unit #, it will look for the next available Temporary Unit # in the "Assign" worksheet. Then mark that unit # as "assigned" (by placing an "X" in the column next to it) and copy it to a cell in the "Activity" sheet.
I will be doing the same thing with assigning different types of PO numbers. I figure if I have the code for the Unit #, I can use the same logic for the other assignments, with some modifications, of course.
I've attached a sample workbook.
If I am not considering the most effective way to accomplish what I am trying to do here, I have no ego at all about someone suggesting a better solution.
I need to copy a range of cells from various columns/rows from 1 sheet into 6 other sheets, but into a specfic range of cells(in the same workbook). I am looking for a code which would copy the cells, then allow me to save the sheets it has copied them to as seperate workbooks without loosing the values it copied. I would also like to make amendments to the cells which have been copied onto the other sheets, without having an error message if I type anything into the cells, also having any blank cells left blank rather than placing the 'o' value in the cell.
View 2 Replies View RelatedI am looking for the VBA code to copy worksheets (with formatting) and save to a specific folder. The steps I am need to follow are:
Create a copy of the first worksheet
Save it to a specified folder and name it with the worksheet name
Repeat with all worksheets until the end of the workbook