I have: + three raw data sheets (needed as 3 lots of data with 25K records in each). + I also have a model that I want to take each record from each sheet, populates some cells in a seperate calculation sheet and then gets a result - which it adds to a total (array).Finally I have + a clever macro that shows me what % of the way through I am (i.e. displays the number of record processed out of the 75K (3*25K) total)
While I have got the model to work - there is some untidy code used where I start my clever % macro for x = 1 to 75K then repeat the same instructions for running the model for each section i.e. sheet 1 : 1 to 25000, then the same instructions for sheet 2, 1 to 25000 etc etc. and then end it I want to know if there is any clever coding that will let me go from one sheet to another when there is an empty cell struck on the active sheet?
I have some Excel models out in the field with some old VBA code that is now causing problems. I regularly develop 'patch' models to make updates or repairs to user models, but need the VBA code syntax to get into a Worksheet module and change one line of VBA code.
The worksheet is named 'Forecast' and I need to change line 5 of my VBA code.
The vast majority of my users are on Excel 2000. I remember the security issues with newer Excel versions and can walk then through that, but I forgot the code syntax I need to get the job done.
I want to consolidate the various employees' salaries of all months in a sheet. I enter salaries in different sheets month-wise and in each sheet, department-wise. Some employees get commission in various departments. Now, I need to see the details of an employee by giving his name. I should get month-wise his salary, commission and department in which he get commission, across all the sheets.
I have the following macro, however I am struggling to make it work on my file:
[Code] .....
I have attached my sample. essentially i have the dec, jan etc month tabs to consolidate into the summary sheet. i would like the data to be dumped from both sheets into the summary sheets. i will be adding a feb, march, april etc tabs as the year progresses.
I would also like column A in the summary sheet to repeat the name of the sheet the data is being retrieved from.
I have a worksheet (named "A") contains all data, and would like to have a macro to searching in A under condition of date,and return those data to in a cell of different worksheets (saying worksheet B, C, D). and everytime i run the macro previous data wouldn't be replaced by new one. Example: worksheet A has data
worksheetname date number B 01/01 10 B 02/01 11 B 03/01 12 C 01/01 13 C 02/01 14 C 03/01 15
We have a company and need to automating workflow.
The Master sheet contains incoming mail details by customer. Each employee is assigned a set number of customers to respond to. We want the information on the Master sheet to filter to a specific employee assigned sheet. On the employee assigned sheet, once filtered, they are to provide updates in column E
Date Reference no. Name Employee ID 16-Feb-12 S/S/1
[Code] ........
We need a macro that can filter customers' details to the specific employee assigned sheet based on the employee ID in the master sheet. For example, all customers assigned to DW (i.e with the 'DW' employee ID) filters into a sheet called 'DW'. Please note that the Master sheet is a continuous log updated daily. This macro must not duplicate information previously filtered once the employee enters a status update in column E.
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode() ' 'Sheets("Summary"). Select If Range("a7") = "complete" Then ' Sheets(Array("1", "0")).Select Sheets(ArrSh(1)).Activate
find the attached file. Book 2 there is 4 columns ie,reg no,fleet no,product & model and on book 1 there is fleet no,reg no and model and what i need is to capture fleet no & model from Book 1.
I would like a macro to be able to save 26 tabs within the one document to individual PDFs.Preferably I would like to be able to specify each time exactly which tabs get printed, because often I don't need to print all 26, just the first 10 or so.I would like each PDF to automatically be named with the value in cell E10 of each tab.E10 already has a formula to create its final value. It references cells from other tabs within the same document. Hopefully the fact that this cell has a formula in it won't affect my ability to use the resulting value as a 'save as' reference?I would like it if the PDFs save to the same location as the Excel sheet from which they're generated is located. The location of the excel sheet will change every three months, so I'd prefer not to specify a location with a specific filepath, as it will have changed by the time I run the macro again.
What I'm trying to do is grab data from the raw data sheet and paste it into the master sheet based off of certain criteria. I want to list any accounts that are not correct.
For example, I want to look through the Raw Data sheet, find the account that contains campaigns which are not correct, and then list the name of the account on the Master sheet. I only need the account name listed once on the Master sheet.
I have a macro and I want to run that macro on multiple sheets. I don't know how to select multiple sheets. I have selected for example three worksheets.
I have used the following code but the code runs only on sheet 10. what do I need to do to make it work on all the sheets ?
Code: Sub multiplemacro() Dim wsh As Worksheet Sheets(Array("Sheet10", "Sheet11", "Sheet12")).Select
I have multiple sheets and a summary sheet in the beginning. and i need to populate the min value / max value and the avg value for every sheet into the summary sheet.
Example i have a column of numbers in column G and i need the min , max and round(avg) for all sheets in the first summary sheet.
Print sheet 1, 3 and 7. Always print sheet 1, however only print sheet 3 and 7 if there in these sheets are values in the cells from row 8 and below.
(If that is to complicated it would be ok if the condition for printing sheet 3 and 7 is that there's a value in e.g. cell A8.)
I managed to create this script that allows me to print sheets 1, 3 and 7, however I can't seem to find out where to put the if-statement (I suppose that's how you do it?). Here's the script i created so far:
I have an excell spreadhseet that has more than 100 sheets and I would like to combine all these sheets into one master sheet (Sheet1 = MasterSheet) within this workbook. Each sheet has different number of rows used. I just want used ranges to be copied over to a master file appending the previous copied range.
Sub MergeSheets() Dim strSheet As Object Dim LR As Long, LC As Long Sheets("Sheet1").Name = "MasterSheet" LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row For Each strSheet In Sheets If strSheet.Index 1 Then
I am writing a macro that organizes data, but also needs to be able to conditionally copy data from other sheets in a workbook.
I have a table with values...column A has the identifiers (strings of letters) and column N has comments related to the data in each row. So, for example, row 3 column A has the value "AAPL," and row 3 column N has the related value "sells electronic goods."
Now, I want to be able to copy column N based on the value of column A into other sheets using my macros. So, say I have another sheet in the workbook and row 6 column A has the value "AAPL," I want row 6 column N to have the related value from the previous sheet. Is it possible to do this?
What would be the macro if i need to hide multiple select sheets? I'm working on a test with 12 sheets. Even number sheets contains the fields that they need to answer and the Odd number sheets contains the formula for score computing including the answers so it needs to be like this:
Sheet 2 - The test Sheet 3 - Must be hidden Sheet 4 - The test Sheet 5- Must be hidden Sheet 6- The test Sheet 7 - Must be hidden
I already have the code to unhide all sheets, just need the macro to hide specific sheets like the ones above.
I am creating a vacation calendar for all of my associates. I have 763 employees so, i want to run a macro that will react a worksheet for each of them. I will have an employee list that will create the sheets and I will have a VLOOK Up to update and pull information when we have new hires come on board.
I have to run the macro in over 75 sheets every month and they are divided in few workbooks. The workbooks have 2 summary sheets and then the sheets I need to run the macro. Can you help me to run the macro in all of these workbooks running it just once?
Sub TelcoTicketsCleaning() ' ' TelcoTicketsCleaning Macro ' Macro recorded 6/13/2007 by EQUANT ' Dim lastrow As Long lastrow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For r = lastrow To 46 Step -1 'Remove rows with DSL, ISDN and PSTN services..................
I am trying to run the following code on multile sheets in a workbook. so far this code is working fine for one worksheet. Can someone help me modify this so it runs on multiple sheets. There can be more then 1 worksheet in a workbook depending upon data ...
- I have total of 13 sheets in a workbook - 12 sheets represent 12 months with data; 13th sheet is single sheet in which i would like to get complete overview of 12 months
- each of 12 sheets has actually sales results for multiple products with following data: internal code, manufacturer part number, name, and qty sold in that month
- situation is that some products have been phased out during the year and some were introduced so each sheet is slightly different in terms of in which row certain product is located
What i would like to achive is to make 13th sheet (whole year overview) do the following: - there is a list of all the products in it, each product has unique internal code - this code (from each line) should be used to find that code in each monthly sheet, then find its monthly sales value (copy it) and paste it in sheet 13 in cell that represents this product and particular month.
In other words i would like to see for each product what was monthly sales throughout this year, but avoid manually filling in qty for each product per month.
I have a spreadsheet with over a hundred tabs, each of which need to be password protected (same password).
I found code on CFO.com that got me half way there. It protects all the spreadsheets, but it does not password protect the macro itself, meaning that anybody can step into the macro and unlock everything at the click of a button.
The code looks like this.
VB: Sub ProtectAllSheets() For Each ws In ActiveWorkbook.Worksheets ws.Protect Password:="secret123" Next ws MsgBox "All Worksheets Protected"
[Code] ....
What can I do to prevent people from stepping into the macro, or prompt a password to actually use the macro itself?
I am trying to figure out how to make a macro that can sort some numbers from Largest to Smallest within a specific range on multiple sheets. The range is only within column D starting with cell D11: (until the data ends) on all the sheets in my workbook (the number of sheets may change with time) except for Sheets: "A", "B" and "C".
In other words I want the sort to work on all sheets except the first 3 sheets which are named Sheet A, Sheet B, and Sheet C.
I have attached a spreadsheet for an example of what I am saying.
This Macro works fine to copy data from multiple sheets into one master sheet, but it is also copying the formula. How can I change this Macro so that it is copying and pasting VALUE only?
Sub Combine() Dim J As Integer On Error Resume Next ' work through sheets For J = 4 To Sheets.Count ' from sheet 3 to last sheet Sheets(J).Activate ' make the sheet active Range("A10").Select Selection.CurrentRegion.Select ' select all cells in this sheets ' select all lines except title Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select ' copy cells selected in the new sheet on last line Selection.Copy Destination:=Sheets("Combined").Range("A65536").End(xlUp)(2) Next End Sub
I'm trying to create a workbook with multiple worksheets. My first sheet to be named "Main" and each additional sheet to be named in sequence after the values in the cells between B5:B98 on !Main.
I'd like to have a macro that would take a worksheet in this workbook named "Template" and make a copy for each cell between B5:B98 and name that new worksheet "c" and then the value in each of those (B5:B98) cells.
Example: First worksheet named "Main" Second worksheet named "c01" Third worksheet named "c01.1" and so on.....
I have a large workbook with 10+ sheets i need to protect all at once. I have created a macro in VBA below. It works, but I would like another user to be able to change formatting on the sheets while they are protected. below is my macro.
Sub ProtectAll() Dim wSheet As Worksheet Dim Pwd As String
