Programming Macro To Operate Over Multiple Sheets Or Workbook

Aug 1, 2012

I'm trying to set up a way to format column widths and row heights in a macro (so that it can read a value from a cell and format a column to that width). I've used the code below which can be repeated for the number of rows and columns desired below

Worksheets("Sheet1").Range("d1").ColumnWidth = Range("d1")

However, I want to be able to do this on a workbook that could potentially have 40+ sheets. Is there a way of applying this code to operate over the whole workbook rather than just one sheet at a time?

View 7 Replies


Using Macro To Operate Filter On Variable Data?

Feb 28, 2014

I have a written a macro to populate a filter criteria with a comma separated list, but the filter fails to work as each individual item on the list needs to be surrounded by speech marks (") and I don't know how to do this.

This may not be an actual vba issue?

The list I am using is derived from =SUBSTITUTE(TRIM(BK9&" "&BK15&" "&BK21)," ",",") There are 120 different cells that this formula references that could be blank. The SUBSTITUTE TRIM functions allow me to just use the cells that have data in. But I can't seem to get the " symbols in there. (I've tried the TEXT function on the original cells, & this doesn't work.

View 3 Replies View Related

Using Macro For Multiple Sheets In Same Workbook?

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


View 4 Replies View Related

Macro Protecting Multiple Sheets In Workbook?

Sep 6, 2012

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


View 1 Replies View Related

Macro To Copy Range From Multiple Sheets And Paste In New Workbook?

Aug 3, 2012

I have a macro that takes info/data from multiple sheets in the Basin workbook and summarizes it into the Median Database workbook. I need to copy the values in range B5:EM5 in every sheet (each sheet name is site #) in the Basin workbook and paste that range into the median database workbook row with the corresponding site.

Here is what I have so far

Sub Median_Database()
'This Macro takes the median from each station tab in the current Basin workbook
'And inserts it into the Median Database workbook


how to grab the sheet name and insert it in the median database. The problem is the copy/paste of the range in each sheet. The macro locks up every time.

View 2 Replies View Related

Macro - Generating Multiple Sheets Using A Template And Source Workbook

Mar 20, 2014

I am new to the VBA editing and I am missing some steps to get my files correctly generated and then ideally saved in PDF format in the same folder.

I have two files; one is an invoice template with the following fields:

Invoice Reference: Line 8 column D
Issue date: Line 9 column D
Client name: Line 11 column E&F
Client address: Line 12 column E&F
Product name: Line 16 column E&F
Product details: Line 18 column E&F
Production date: Line 20 column E&F
Delivery date: Line 22 column E&F
Units: Line 24 column E&F
Total Units: Line 26 column E&F
Total Invoiced: Line 30 column F

Each sheet in the "template" workbook should be named after the Invoice Reference.

Secondly I have the source file in which the data is organized as follows: (Both are in the same folder)

Invoice Reference: column A
Issue date: column B
Client name: column C
Client address: column D
Product name: F
Product details: column G
Production date: column H
Delivery date: column I
Units: column J
Total Units: column J
Total Invoiced: column E

One invoice needs to be created per line in the source workbook. I tried many times the macro recording without getting the correct outputs.

View 5 Replies View Related

Macro To Find And Replace Values Across Multiple Sheets In Same Workbook?

May 27, 2014

I have an excel workbook with 60 sheets (each contain data in the same categories and in the same column locations, just different information on each sheet). What would the VBA code look like if I wanted to manually enter the find and replace values and perform the function (find and replace) across multiple sheets in the workbook?

View 5 Replies View Related

Programming Multiple Inputs With Multiple Data Fields

Feb 19, 2009

I am trying to set up a "diet log" for my nephew to track his eating habits for a school project. I am trying to make the "total" section of the attached spread sheet auto calculating depending on the information placed in the "breakfast / lunch / dinner / snack" catagories. I have two worksheets in the file. The first being the preferred method of a single cell and seperating the items using only a ",". The second sheet has a little bit different lay out which I don't think will work as nicely.

Honestly, I am not sure if this will even be possible, but I figured I would try here as a final resort. Currently there are just some sample foods in there as I will let him fill in the actual workbook for his use.

View 7 Replies View Related

Programming Multiple Conditions In Cell

Jul 6, 2009

Attached is my worksheet. I need to program column C to adjust the value in column A according to the rules set in table J2:K17, but ONLY if the value in column B = mens. If the value in B does not equal mens, then leave the C cell blank. I've manually populated column C to show the end result that I'm looking for.

I tried a simple =IF(ISNUMBER(SEARCH("Small",A2)),"S",""), etc., but I can't figure out how to incorporate the additional condition that would let the cell return a value ONLY when a condition in B is also met.

NBVC helped me with a similar request previously, but with that formula, if the condition wasn't met, the cell returned "#N/A" instead of being blank.

View 14 Replies View Related

Printing Multiple Sheets In Workbook With Hundreds Of Sheets

Feb 18, 2014

I have a work sheet named "Main_List"...In column D starting with "D2" I would like to list worksheets that I would like to have printed via VBA.

The workbook has several hundred worksheets and I would like to list in column D only worksheets that I would like to print with VBA code.

View 3 Replies View Related

Copy Data From Multiple Sheets In Workbook To Different Workbook But On One Worksheet

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

Excel 2010 :: Counting Cells With Multiple Criteria On Multiple Sheets In Workbook

Aug 5, 2012

I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---


but it returns a value of zero each time. Clearly there is an error in the formula.

Here is some background:
-- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.

View 1 Replies View Related

Programming A Macro To Select Row Ranges Based On A Formula

May 2, 2009

I'm building a macro thats copying 4 rows of data at a time from a spreadsheet (has 1500 rows of data total) and transposing it to a master spread sheet. There are blank rows in between each entry (the data source I'm using isn't clean), so how can I either have the macro jump to the first row it finds with a value, copy rows until it stops finding a value, paste+transpose into a new document and then repeat the process throughout the document.

If thats too complicated, a macro that finds rows based on a formula would work as well. E.g. Start at row 5, copy+paste+transpose 5-8, start at row 11 (5+6), copy + paste+transpose 11-14, start at row 17 (x+6) ...etc... Here's the code I have developed thus far:

View 4 Replies View Related

Combine Workbooks With Multiple Sheets Into 1 Multiple Sheet Workbook

Mar 16, 2008

I have about 20 workbooks with different file names for different projects all saved in the same folder. Each workbook has about 10 worksheets and each worksheet is named in a similar fashion in each of the 20 workbooks (eg. revenue, cost, variance etc.). I want to pull out a worksheet named ' forecast' from each workbook into a master workbook so that the master workbook would contain the 20 forecast worksheets.

View 9 Replies View Related

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:


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

How To Protect Multiple Sheets In Workbook

Jun 19, 2006

How to protect multiple sheets in a workbook and not having to protect them 1 by 1. I have copied and pasted all the different macro's/visual basic script that has been posted for that purpose, but I can't get it to work and I'm always getting an error. The error from visual basic says "compile error: invalid outside procedure" and then the visual basic screen shows the following:

Range ("AM52")
If Ans >= 0 Then

It does not like the "AM52" part because it is highlighted in the error message.

Also, the error I get from recording my own macro step by step is "Run time error 40036" from visual basic.

View 8 Replies View Related

Copy Multiple Sheets To New Workbook?

Mar 31, 2014

I am looking for some code to copy the exact values in a couple sheets over to a new workbook. There are images in the sheet that need to come over and formatting of cells including merging.

View 2 Replies View Related

Sort On Multiple Sheets In Workbook?

Nov 18, 2013

Trying to sort on multiple sheets in the workbook. The range that is to be sorted will vary on each sheet, so i would like to write the range as .end(xlDown).Row and then do the sort. So far, what I have written is giving me a type mismatch error

.Sort.SortFields.Add Key:=Range("B2:B" And Range("B2")).End(xlDown).Row, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

View 3 Replies View Related

Vlookup In A Different Workbook But Multiple Sheets

Jan 18, 2007

I have this one sheet (File 1) where I have the unique codes of people and am trying to perform a vlookup() for a value by specifying the lookup in a dfferent workbook (File 2) but multiple sheets. In other words the data (unique codes) is scattered in different sheets in that workbook (File 2). I need to perform a vlookup() wherein the codes finds a match in whichever sheet in File 2 and the value in the specified column be entered against the respective cell in File 1.

I tried selecting all the sheets in the formula but it does not return a value for all the inactive sheets in File 2. Vlookup() works fine for the Active sheet only.

View 9 Replies View Related

Searchbox On Workbook Multiple Sheets

Apr 11, 2007

I have a workbook that contains 25 worksheets and in those sheets there are common items for different equipment, all the sheets are exactly the same when it comes to lay out, except the first sheet, the data differs accordingly on the subsequent sheets. Being new to excel I do not have a clue how to set a seach box at the click of a button and copy and paste the part searched for thru all the sheets in the first sheet and at the same time deletes the latest search when a new one is carried out.

View 9 Replies View Related

Exporting Multiple Sheets To Another Workbook

Feb 12, 2010

I expect this may have been done before, I have a workbook with 6 sheets. Each sheet lists a register, each register has between say 12 & 25 column, 1 column in each sheet denotes a "y" for exporting to another spreadsheet (not the same column in each sheet though)

what I would like is some code that will open a new workbook, and copy each of the sheets (all the fields/columns etc) into the new book, but only the lines that have the "y" next to them.

I have codes that export one sheet , but its not really relevant to what i need to do.

View 9 Replies View Related

Unhide Specified Multiple Sheets In A Workbook

Jun 27, 2006

I would like to unhide specified multiple sheets in a workbook at one go. What I'm doing now is something like this:

Sheets("Source 1").Visible = True
Sheets("Source 2").Visible = True
Sheets("Source 3").Visible = True
Sheets("Source 4").Visible = True

I managed to get the array function when I want to hide the sheets:

Sheets(Array("Source 1", "Source 2", "Source 3", "Source 4")).Select
ActiveWindow.SelectedSheets.Visible = False

but I can't do this when I want to unhide them. I do not want to unhide all sheets in the workbook, only those I specify in the code. How can I use the something like the array code to unhide the sheets.

View 9 Replies View Related

Copy Records From Multiple Sheets Into New Workbook

Feb 2, 2012

Following problem:

I have a workbook containing data about some students grade on different sheets (4). The structure is almost the same on every sheet:

The first 2 row contain headers the each row contains a name and result for different test:

name - test A - test B - ...etc.
Peter - 90 - 60 -....etc.

there are 25 columns on each sheet - 4 sheets in all.

What I want is, to consolidate each students results in a separate workbook.

View 1 Replies View Related

VBA To Copy Multiple Sheets Into New Workbook And Save

Jun 23, 2014

I have managed to pull together code that does the required task - save two sheets from a work book in to a new workbook on to a dorectory each day. However I woul;d liek to paste special the values and cannot figure our how to reference that on the below:

'saves text file in day on day folder
Dim WS As Worksheet, CheminDest As String, fNAME As String
'create directories as needed
On Error Resume Next
CheminDest = "T:DMRatesReportsChecks" & Year(Date) & ""

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

View 3 Replies View Related

Total A Cell From Multiple Sheets Within A Workbook

Nov 5, 2007

I have a workbook containing 120 sheets. Each sheet contains a column labelled "Subject", and a row below labelled "Totals:" with a numeric value in the intersecting cell.

I need a formula that will total the value in all these cells on the last sheet.

The trouble is, the cell address of the intesecting cell fluctuates somewhat from sheet to sheet because the column and row for the "Subject" and "Totals:" are not always the same.

******** ******************** ************************************************************************>Microsoft Excel - Book1.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF30=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

I want to be able to total the "Subject" wages for codes 5183 & 5187 (unfortunately, the payroll amounts are located one row below) for all 120 sheets in the workbook.

View 9 Replies View Related

Excel 2010 :: Macro To Save Multiple Sheets To Multiple PDF With Cell Value As Filename

May 6, 2014

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.

I am using Excel 2010.

View 5 Replies View Related

Create IF / LOOKUP Formula To Look At Multiple Sheets Within Workbook?

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

How To Protect Cells/columns In Multiple Sheets In A Workbook

Sep 9, 2009

I'm trying to protect the contents of columns in multiple sheets in a workbook. I've tried to group the columns ( - months, i.e. sept, oct, nov 09 through to march 10) but when I go to Protection options the 'sheet' protection option is not available, all I can do is protect or unprotect the entire workbook.

I've also tried to protect the workbook, but individually set the specific columns in each sheet to be protected (repetitious but seemingly unavoidable), but this doesn't work well either as there's now a mix of a protected workbook and protected columns, both with individually set passwords to unprotect... which seems a bit silly.

There must be a way around this? - the columns are H, J, K and L, and the entire contents of the summary sheet '2009-10 Financial Year'... basically I'd like to be able to protect all of the contents of all of the cells here, ideally just with one password.

View 9 Replies View Related

Repeating Columns And Information On Multiple Sheets In Same Workbook

Nov 18, 2013

Is it possible to automatically have columns repeat through all sheets in a workbook? I keep an annual book, broken down by sheets for each month. I always have to add 5 columns to each sheet, and was wondering if there was an easier way..?

View 3 Replies View Related

Merge Multiple Sheets Of Existing Workbook Into One Sheet?

Mar 27, 2014

I want to merge different worksheet of an existing workbook into one by macro .I did not want to repeat the rows label for each worksheet data.Also I want to get at right hand side i.e in G column the data to be extracted for respective worksheet in the merged data.I have enclosed in attachment an expected solution in a sheet name "merged".However the number of worksheet is here only 3 for sample purposes.However,in reality there is more than 3 .

View 9 Replies View Related

Copyrights 2005-15, All rights reserved