Is it possible to apply changes to formatting (column width, text size) to all worksheets within a workbook? I have a workbook with 50 worksheets--1 for each state--and I want the formatting to be standard throughout, and I want to do it quickly.
View 3 Replies
I have a workbook with numerous worksheets that contain text, steps actually. I would like to select all common words and change their formatting to bold. Every time I try however, it formats the entire cell in bold. Is there a way to isolate just the word "click" for instance in all the cells in each worksheet and change the formatting to bold, and leave the other text within the cell normal?
View 9 Replies View RelatedI'm trying to perform the same process to all the worksheets in my workbook. This is the code I have now, but it will only apply to the single active worksheet:
View 2 Replies View RelatedI am trying to code a Macro so that i can take all the worsheets and save them as individual Workbooks. I wrote a macro that appeared to work, but, after it saves the first sheet as a workbook, i get a debug error.
MS VB Script error:
Runtime error '9':
Subscript out of range
Any advise would be greatly appreciated.
Thank you
Code is below..
Sub saveall()
'
'
For Each ws In ActiveWorkbook.Worksheets
ThisFN = "C:Documents and SettingsUserDesktop" & ws.Name & ".xls"
I = I + 1
Sheets(I).Select
Sheets(I).Move
ActiveWorkbook.SaveAs Filename:= _
ThisFN, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Next ws
End Sub
Is there a way in VBA to select all sheets in a workbook and format them the same? If the names and the number of the sheets are going to change everytime. For example if i wanted the page setup to fit 1 to 1 for all sheets in a workbook
View 2 Replies View RelatedI have this macro and all I want to do is apply it to every page of my worksheet with a loop and can't find what kind of loop I need:
Sub format()
' Keyboard Shortcut: Ctrl+r
'
ActiveCell. Offset(-37, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(37, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Range("A1:K37").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
I am having an excel workbook consisting of 11 sheets. Can I apply conditional formatting to multiple sheets at the same time?
View 2 Replies View RelatedI have 2 worksheets - one is a monthly update, the other is last month's data.
I want to highlight the changes on the monthly update sheet using conditional formatting.
I named the columns as ranges on the "last month" worksheet, and then used =Match(A1,Jan,0) (wheras Jan is the range on the "last month" worksheet where A1 resides)
What formula do you use to say "if A1 does NOT match a value in range "jan", then format it"?
I am trying to apply a macro to evey worksheet. When I run it, it isn't moving on to other sheets. Instead, it starts from the beginning of the sheet i ran it on.
Sub full_Format()
Dim ws As Worksheet
For Each ws In Worksheets
Range("A1:D1").Select
Range(Selection, Selection.SpecialCells(xlLastCell)).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=0"
Selection.FormatConditions(1).Interior.ColorIndex = 15
Dim LastRow As Long
Range("C1").Formula = "=VLOOKUP(RC[-2],'[Yahoo Industry List as of June 27, 2008.xlsx]Master Sheet'!R2C[-2]:R1048576C[1],4,FALSE)"
LastRow = Range("B1:B" & Range("B1").End(xlDown).Row).Rows.Count
Range("C1" & ":C" & LastRow).FillDown ....................................
I have 2 nearly identical workbooks and I need to update historical data from the old workbook into the newer one.
My current Coding Snippets that I want to use look like the following:
Code:
Sub UpdateWorkbook()
Dim ws As Worksheet
Dim r1 As String
Dim r2 As String
Dim r3 As String
Dim r4 As String
Dim r5 As String
Dim r6 As String
[code]....
Now, this code isn't working I suspect because the Copy and PasteSpecial Functions don't work the way I wish to.
Is it possible to perform conditional formatting on numerous worksheets at one time. I have about 50 worksheets that I would like to perform the same conditional format on. Is there a way to do this.
View 5 Replies View RelatedI have a workbook with three sheets.
Sheet 1 is an original 'in progress' work.
Sheet 2 is a copy of sheet 1 taken on a particular date.
Sheet 3 is information extracted from Sheet 1.
On sheet 3 what I need to do is change the font colour of a cell to red if the information it contains in relation to Sheet 2 is different i.e Sheet 1 cell and Sheet 2 cell are different
For example
Sheet 1 cell value = Deb
Sheet 3 cell value = Deb
Copy of sheet 1 taken to create sheet 2 on a certain date
Sheet 1 cell value changes to = Bed
Need Sheet 3 cell to change to = Bed as it is different from sheet 2
I did try conditional formatting but it will not work with info. from another worksheet that I can see. Maybe someone out there has another solution.
I have 2 worksheets. One has locations with numbers, example:
275 Location 1
276 Location 2
I have all my locations on the second sheet, but in 2 different columns, listed with numbers only, example:
271 275
272 300
I have 2 scenarios I need help with.
1st:
If any of the numbers on sheet 1 match the numbers in column 1 on sheet 2, highlight the number on sheet 1 in green If any of the numbers on sheet 1 match the numbers in column 2 on sheet 2, highlight the number on sheet 1 in red
2nd:
If the number on sheet 2 matches any number on sheet 1, highlight green
I want these to apply to all the cells that have numbers (it could apply to all cells I guess as it should ignore it if it doesn't match, I would assume)
I know there are many posts concerning this, but after scouring, I couldn't find one that fit my situation. I have a total of six worksheets, I am only concerned with two worksheets.
Worksheet (functions!)
This one has a list of numbers formatted as general. (Column G)
Is actually a formula/macro that outputs a number... (didn't know if this mattered?)
I have 3 worksheets in one workbook. I need to extract 10 random rows of information from about 193 rows in sheet 1, and post it in sheet 2 and I can't seem to find a way to make this happen correctly.
View 9 Replies View RelatedI need to count the frequency/occurance of strings "Manager", "Clerk", "HR" etc. that occur in the exact same column in multiple worksheets of a single workbook. COUNTIF works ok on a single sheet query but the formula is getting bigger if there are number of worksheets.
simplyfing the formula. Also is there anyway to put the formula on the column instead of row wise. I can use a single formula on the column instead on every row in Adminsheet.
To be clear my requirement is to count the occurrence of a search strings listed in Adminsheet column A across all worksheets and display count in Adminsheet column B
I use excel 2003 SP3. I've created invoices that have several (very basic), but different formulas in the last column of the worksheet. I have the same customers every month so I use the same monthly workbook for all invoices. Since each invoice has the same formulas, in the same cells, I used the SHIFT key while selecting the entire range of worksheets (invoices). After they have been selected, I entered the SUM formula in a cell so it would automatically be entered in all of the worksheets (invoices).
When I select the same cell in all the worksheets (invoices) I can see that the formula is there. However, when I enter a value that the formula should be adding, it doesn't. But if I select the cell with the formula in it, the formula shows in the formula bar. When I put my cursor at the end of the formula and hit ENTER, the formula then works. So I have to select each formula, in each cell, on every worksheet, hit ENTER, and then, if I need to change a value, I have to repeat the process. I am definitely missing something, and I'm sure it's quite obvious and simple.
Can you arrange 2 worksheets in the same workbook so they are side by side, like you can the way you can tile two different workbooks?
View 2 Replies View Relatedhow to merge data from all worksheets into one new in the same workbook, see example in attach. There are 5 worksheets in the test workbook. Each worksheet has name of “Sheetx”,X=number of worksheet. Number of Row in each worksheet is uncertain, but it is always less than 35 (I will say it is “safe” range to prevent from missing data). Requirement: Vertically merge data from A1:F35 (including the “blank”) to “TEST”.
I am using Excel 2007 at office. By the way, I am NOT a excel expert, while posting your answer, could you please also explain how to do it step by step?
I have a workbook with several worksheets in it. One worksheet for each manager. I only want the individual manager to see their individual worksheet and not those for the others. Is it possible to hide the worksheet and unhide it using an individual password, a seperate one for each manager ?
or alternativly, is it possible to hide say the columns and give each manager their own password that only allows them to unhide the columns for their worksheet. I really dont want to have to send out several seperate versions, just sending one workbook but passwording the individual worksheets within it would be ideal.
I have tried to pass the worksheets to another workbook by copying the sheets and passing it to the other workbook with Excel's own tool. Whenever I try this I get an error with the API XML request.
View 8 Replies View RelatedI have a workbook containing several worksheets I want to copy to a workbook of their own and save under a specific name/password through a macro.
However, I have five difficulties:
1) I want to new workbook to contain only 1 worksheet. So, only the worksheet copied from the original workbook, and no other worksheets.
2) I want to be able to manually select the destination file for the copied workbook, however. This should be the same file for all workbooks.
3) I want the data copied from original worksheet to be ‘pasted as value’ only, so no formulas in the new workbook.
4) I want the new workbook to be read-only. Therefore I want the workbook to be /locked and password protected. So, the password should be only for altering cells, not for opening the workbook. See also point below.
5) Regarding the naming and passwording part I have made a matrix table in a worksheet named: “Rekeningen” (this worksheet is in the original workbook). This table contains three columns:
Column A, cells 40:65, contains the names of the worksheets I want to copy
Column B, cells 40:65, contains the names of the new workbooks
Column C, cells 40:65, contains the passwords of the new workbooks
So for example ....
I have around 40 to 50 worksheets in a workbook. Is there a quick way of listing the names of all the worksheets in a single worksheet instead of typing the sheet names one by one?
View 2 Replies View RelatedIs there a way to sort the worksheets in a workbook after they have already been entered. My clerk put 200+ worksheets in one workbook....out of order.
View 7 Replies View RelatedAssume 2 Workbooks - the Active one and "Book2".
I mannaged to copy ALL the sheets from the ActiveWorkBook to "Book2" with the following code - but as you can see the code copies each of them AFTER the last sheet in "Book2".
Sub Copy_Sheets()
' Copy All sheets from ActiveWorkbook to "Book2.xls"
For Each SH In ActiveWorkbook.Sheets
SH.Copy After:=Workbooks("Book2").Sheets(Workbooks("Book2").Sheets.Count)
Next
End Sub
How can I copy them to "BEFORE", lets say Sheet(1) in "Book2" ?
I tried to change the SH.Copy command to somthing that sounds logic to me, such as:
SH.Copy Before:=Workbooks("Book2.xls").Sheets(1)
but got the: "Run-time error '9' - Subscript out of range".
Any shortcut key that allows you to switch between worksheets within the same workbook?
Found the shortcut - CTRL and Page Up / Page Down
Using Office 2011 for mac, but felt this question was best suited for general.
Here is the situation.
I have a workbook with three sheets. It's a price/invoice/labor workbook.
Sheet 1 has all the data, including my cost and loss/gain fields
Sheet 2 has labor costs and the totals are linked to Sheet1
Sheet 3 is the customer copy of the first sheet. It does not include wholesale and profit info.
I'm trying to link the cells so that when info changes in Sheet 1, it's reflected, in real time on Sheet3
Right now I'm using a formula per cell ='Sheet1'!XXX (where XXX = the cell/column location, ie D15). The first column I started has this formula in every cell, about 100 in all, copied by hand and changed to reflect. It works, but it seems like there has to be an easier way to mirror or link the data. I tried pasting special with links, but continue to get errors as the link pather includes the entire file data path from the local machine.
I still have about 200 cells left and I'm dreading doing this all by hand. Also, If I add a new row to sheet1, this will not reflect in sheet 3.
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".
Macro to filter (extract) data into different worksheets based on Status on Column G in a new workbook. The new workbook can be saved in the same folder where the existing one is saved.
I have attached the Rawdata file and the sample file for reference..
I have 16 proposals that are either hidden or visible in a proposal generator. I need to be able to copy only the visible worksheets over to a new workbook that uses a name from a cell inside the proposal generator. Then a save as prompt to force the user to save the file wherever they choose.
View 14 Replies View Related
