How To Specify The Sheet Name For Applying A VB Code

Aug 22, 2009

have a code for restricting printing until some cells are filled; however the code is been applied to all the file. I need to apply it to one one sheet.
Thanks for your help.....


Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
If Application.WorksheetFunction.CountA(.Range("A11:K11"), ("A13:K13"), ("A16:K16"), ("A19:I19"), ("J18:K18"), ("A22:K22"), ("A25:K25"), ("B63:B64")) < 8 Then
MsgBox "Please Complete Information"
Cancel = True
Else
'Allow printing
End If
End With
End Sub

View 9 Replies


ADVERTISEMENT

VBA Code For Applying Borders To Ranges

Mar 12, 2009

I need to apply borders to a certain range. Is there anything I can do to shed some fat (code) off the macro below?

Range("A8:AD100").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)

View 6 Replies View Related

SaveAs Code Applying Password

Jul 29, 2007

I have a code that opens a workbook and saves it under one file name and then saves it under 2 different file names, but it keeps on saving them as password protected and I can't work out why. Would it be something to do with the code or something else? The following is the code I am using:


Workbooks.Open "C:BatteriesStore InformationStores" & StoreFile
ActiveWorkbook.SaveAs "C:BatteriesStore InformationOld Stores" _
& StoreFile & " " & Format(Now, "d-mmm-yy"), xlNormal, Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "C:BatteriesStore InformationStores" _
& StoreFile, xlNormal, Application.DisplayAlerts = False

View 2 Replies View Related

Applying Macro Code In Multiple Sheets?

May 23, 2013

i have a macro code but i don't know how to apply it to all sheets in the same workbook

my code is

VB:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("D5:D100"), Target) Is Nothing Then
Target.Offset(0, 1).Value = Now() [code]....

View 1 Replies View Related

Excel VBA - Applying Code To Specific Worksheet?

Jul 23, 2013

I have a number of different modules. Each module does something similar, but different to a specific workbook. Instead of running the macro(s) individually is it possible to have a parent-class that calls on each module, and further to have each module *know* which worksheet it should apply to?

Example below:

Code:
Sub Cost_Center_Information_File()
'What it does: applied to a worksheet (there are about 15 worksheets in the workbook) it
'will do some formatting manipulations.

[Code].....

View 4 Replies View Related

Applying Macro Code To A Range Of Cells

Dec 26, 2009

how can i do the following using VBA
making each cell in column A added to each cell in column B and the result will be in the column C
for example
c1=A1+B1
c2=A2+B2
c3=A3+B3
... etc

i know i can do that simply without vba code but I just want to use this method to implement more complex formulas .

View 9 Replies View Related

Passing Variables And Values Between Sheet Code And Module Code

May 13, 2009

I have some buttons in different sheets in an excel file, each button has its own code, that is the reason I can not move the code related to each object to another location (sheet or module).

And I have one piece of code in Module1 (Auto_load) in order to execute automatically this routine every time file is opened. Inside "auto_load" routine I initialize some values of some check buttons,options buttons and positions of some objects in diferent sheets, but I can not pass the value of variables between Module and Sheet's code even when I declare as public variables and/or function.

I have the following structure: ...

View 11 Replies View Related

Using Code To Email A Sheet But Need To Strip The Code Before Sending

Feb 2, 2009

I am using the code below that I got off of these forums to email a particular sheet in my workbook, but I need to strip all of the VBA code and the command button from the sheet being sent.

View 14 Replies View Related

VBA Code To Move Data From Entry Sheet To Historical Sheet By Date

Apr 17, 2014

In the attachment you will see an example of what I am trying to accomplish. What I am trying to do is find VBA code that will move data by date from an (Entry) sheet to a (Historical) sheet. I want to enter a date in B3 on the (Entry) sheet. I then enter data into C3. What I would like to happen is when the data is entered into C3 the sheet goes and finds the same date that I entered in B3 and copies that data from C3 into Column E of the (Historical) sheet.

Test3.xlsm

View 5 Replies View Related

Code Adds Data To Active Sheet Instead Of Specified Sheet?

Mar 12, 2014

What this code keeps adding the data to the active sheet instead of the specified "Users" sheet:

[Code]......

View 2 Replies View Related

Applying Percentages

Jun 17, 2008

I have a table of data with names across the top and number of weeks down the left. The table lists amounts of money those names earned during the respective weeks. It looks something like this:

Mike Dan Bill Carl
400 500 600 700
800 900 1000 800
700 600 500 400

In a seperate worksheet within the same workbook, I have 3 columns of data. The first column is a name. The second column is a criteria. In the third column, I am trying to look up the value of 8 specified weeks of earnings of the name I put in column 1 and multiply those earnings by a percentage based on the criteria in column 2. Then sum the products.

I have a table of the 8 percentages I am trying to apply. There are 7 different percentage applications.

So if I am looking up Mike's earnings during those 8 specified weeks and the criteria in column 2 is "B", then I want to apply the appropriate column of percentages to those 8 looked up earnings, Multply them and then Add them.

Up until now, I have been trying to use some hybrid of HLOOKUP, SUMPRODUCT, and IF.

View 9 Replies View Related

Applying IF To Multiple Cells

Jan 9, 2014

I am trying to update a project tracking sheet at work and I am having trouble. I have a list of construction projects (approx 130) along with details on the project, one detail being % completed. I am trying to create a summary of the projects at the bottom of the page, showing the total value of all projects, according to the % completed.

<10% is ANTICIPATED
= 10% is AWARDED
>10% <90% is IN PROGRESS
>90% is COMPLETED

[code]....

View 8 Replies View Related

Applying Date Range

May 18, 2009

To explain I have two sets of merged data. On one fixed column I have two different sets of peoples age

1 - by age grouip eg 25 - 34
2 - By birth year so 01/02/1983

I know that person who is born between 1975 and 1984 would fall into the age group 25 - 34

I want to convert all the birth years into age groups. Its a lot of data (30,000 rows +) in random order

Does anyone have any idea how I would set this up, as I really want to add "age group" to a pivot table I have.

View 6 Replies View Related

Auto Filter Not Applying?

Feb 25, 2013

Using VBA in a code. I was using this same code for another workbook before and it worked fine.

[Code] ...

The filter works, but it is not applied. after the macro is run, I need to click "OK" on the filter for it to apply.

View 7 Replies View Related

Applying Same Formula To Entire Row

Feb 20, 2012

I have an excel sheet where I do a simple price-discount calculation for a product. Please check the file at:

Code: [URL] ........

A2 shows the price, B2 shows the discount and C1-L1 shows the number of units sold. C2-L2 is where I want to calculate the earnings based on the formula I have in C2. I want to apply the same formula to the entire row so that I can quickly see the earning for any number of units sold.

View 3 Replies View Related

For Each Command Not Applying To Other Worksheets

Jul 8, 2013

My issue is my For Each command is not grabbing the next worksheet and applying my code. Here is my code:

Sub Test()

Dim ws As Worksheet

Worksheets("Report").Activate

For Each ws In ActiveWorkbook.Worksheets
With ActiveSheet.PageSetup
.LeftFooter = "&D"
.CenterFooter = "Test"
.RightFooter = "&P"
End With
Next

End Sub

View 5 Replies View Related

Applying One Formula To 2 Columns

Feb 8, 2014

So right now I have a formula that I'm using in column "C":

=IF(B4="","",IF(AND(B4=22),"IN RANGE","OUT OF RANGE"))

So basically column C tells you if the number in the B column in within the range of 22-30. Now I want to write the formula in column D and make it apply to columns B and C.

So cells in columns B and C will have a number value in it and I will make column D where the formula is. There will be a number in B or C, but never at the same time. I can't figure out how to alter the formula to do this.

View 3 Replies View Related

VBA: Applying The If Statement To All Cells

Mar 3, 2008

I use the If - Then statement in VBA to determine a condition which works perfectly fine for the particular cell I reference to, but i need it to apply to all the cells i.e. cells A1:A10?

Sub MACRO1()

If Range("A1") = "PAYE" Then
Range("B1").Formula = "=C2 * .128"
End If

If Range("A1") = "LTD" Then
Range("B1").Formula = "=(C2 - 100)* .128"
End If

End Sub
Sam

View 9 Replies View Related

Applying Colorname From Another Cell

May 3, 2006

I have cell A7 Merged and need info in that cell. Example of what I want: Say I click on cell A1 and it is the color Pink, but has the letters blue in it. I would like it to display the word "Pink" in cell A7 not the number. Well I would like when you click on any cell with colors, that it will display the color in Cell A7. When I try different ways all it gives me is the number in the last cell of the square not the color and when I click on other cells it will not give me that cells info.

Private Sub CommandButton1_Click()


Dim x, rng As Range, r As Range
Dim myList
Dim row1 As Integer

x = Application.InputBox("Enter size of square: 2=2 by 2, 3=3 by 3, or 4=4 by 4", Type:=1)
Set rng = Range("a1").Resize(x, x)
myList = [{1,2,3,4,5,6,7,8;6,11,3,10,13,16,38,53;"Brown","Pink","Grey","Purple","Green","Red","Blue","Yellow"}]
rng. CurrentRegion.Clear
Randomize

For Each r In rng
x = Int((8 * Rnd) + 1)
With Application.WorksheetFunction
r.Interior.ColorIndex = .HLookup(x, myList, 2, False)
r.Value = .HLookup(x, myList, 3, False)

Range("A7").Value = r.Interior.ColorIndex

End With

Next

With rng
.ColumnWidth = 10
.RowHeight = 50

With .Font
.Size = 14
.Color = vbWhite
.Bold = False
End With

.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.Weight = xlThick
End With

End Sub

View 9 Replies View Related

Applying A Mid Function To Many Rows

Jun 14, 2007

I need to cut off addresses by 30 characters. I am doing this using the MID function. However, being a newb, how can I apply this function to say D4, D5 all the way to D899, without copy and pasting for 800 times? I did search this first, and couldn't find a resolution.

View 4 Replies View Related

Applying Formula To Drop Down List?

Feb 14, 2013

Cell B1 is to display money value dependent on what item has been selected from drop down box in Cell A1

View 2 Replies View Related

Use Sumifs Function Applying 2 Criteria?

Aug 21, 2013

Excel.xlsx

Attached is the example of the data, I am trying to sum the amount applying 2 criteria, within a particular month and other category. The formula tried is

=SUMIFS(D5:D10,C5:C10,"*June",E5:E10,H1)

However it is giving a value of zero only. I have tried other combinations as well.

View 4 Replies View Related

Applying Formula To Many Cells That Already Contain Values

Jun 8, 2014

I want to apply a simple formula to a number of cells in an existing workbook that already contain values E.g.

My column contains the values
5200
1600
4376
in separate cells

I want to divide each value by 1.2 - without having to change every individual cell - can it be done?

View 2 Replies View Related

Applying Macro To Selected Range

Apr 3, 2014

I have a macro which sorts data within a cell. This is working perfectly fine but the only problem with this is that it works only for a single which is selected. How can I apply this macro to range of cells I select using a mouse.

View 3 Replies View Related

Applying Macro To Entire Spreadsheet At Once

Feb 6, 2009

For simplicity, let's assume I have three columns, "Company," "Policy," and "Balance."

Example:
Company A Life $1
Company A Med $2
Company A Dent $3
Company A Disab $4
Company B Life $9
Company B Med $5
Company B Dent $7
Company B Disab $2

According to my post preview, the columns are running together, but I do have it arranged in three distinct columns. What I want to do is program a macro to do the following:

Every time the name of the company changes, I want Excel to insert two rows after the last of the group, and have a summation of the "Balance" column in the first inserted row.

This is what it should look like:

Company A Life $1
Company A Med $2
Company A Dent $3
Company A Disab $4
$10 <- (this should be directly under the $4, but I'm not good with forum languages)

Company B Life $9
Company B Med $5
Company B Dent $7
Company B Disab $2
$23 <- (same with this - should be directly under the $4)

View 3 Replies View Related

Applying A Percentage To A Long Column

Jan 9, 2009

I have a long column of numbers that I want to apply a single percentage to all the numbers in each column. How do I do that?

View 3 Replies View Related

Applying Multiple Formatting To A Cell?

Dec 2, 2011

In Excel we can apply different formatting styles to the "TEXT" contents of a cell while we are in edit mode. For example we can apply "BOLD" to Brown Fox and "UNDERLINE" to lazy in a string "A quick brown fox jump over a lazy dog.

But when the above string is concatenated from the contents of various cells, excel is unable to apply different formatting (as exampled above) in the concatenated cell. Although we can apply bold, underline etc by selecting the concatenated cell but it is applied to whole string and not to the selected parts of string.

Sheet1 *A1Brown2Fox3Lazy4*5A quick Brown Fox jump over a Lazy DogSpreadsheet FormulasCellFormulaA5="A quick "&A1&" "&A2&" jump over a "&A3&" Dog"

Moreover, MS Word's "mail merge" is capable to apply different formatting to different "Fields" in a single paragraph.

Is there any way so we can apply multiple formatting to the contents of a single cell which is not "Text" but result of "concatenate" in Excel, like MS Word's mail merge or as it is applied in custom format within straight brackets like [Red] etc.

View 3 Replies View Related

Excel - Applying Color To Cells?

Sep 19, 2013

I am using two separate workbooks. I am tranferring dates into a new spreadsheet to track projects dates for milestones.

The formula I am using is this: =IF('[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$CQ$4="", VLOOKUP(W54, '[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$1:$1048576, 94, FALSE), VLOOKUP(W54, '[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$1:$1048576, 95, FALSE)).

I would like the cell to turn green if the last part of the formula is true and stay clear if the first part of the formula is true.

I also need to add color beyond just that. I was attempting to apply conditional formatting but am a bit stumped. I want the green to be maintained regardless of other formatting. I would also like to apply to cells that are not color coded green:

Red - if the date is overdue
Yellow - if the date is within 7 days
Otherwise, leave the cell color as clear

Is this possible with conditional formatting? I am not at all proficient in VBA...

View 9 Replies View Related

Applying IF Condition In Excel In A Cell?

Sep 21, 2013

I want to have a condition which will check if the value in cell is present or not and based on that i have to display result in a cell.

Eg: In Sheet1, in column D i have 4 values (Simple, Complex, Medium Complex, Very Complex)

In Sheet 4, we have numeric values to Simple, complex...Like below

Complexity Estimation
Simple 2hrs
Complex 4hrs
Medium Complex 2.5hrs
Very Complex 6hrs

In Sheet5, we have values defined in % like below:

Requirement Analysis
15%

Design
20%

Coding
20%

System Testing
15%

[code]....

In column G of Sheet1, we have to write a function or formula which we do below calculation.

IF (Sheet1!D3="Simple") then Sheet1!G3=Sheet5!B2(ie.15%) of Sheet4

Similarly i will have Sheet1!D3 values as Complex,Medium Complex,Very complex

View 2 Replies View Related

Applying Same Formatting Across Multiple Sheets

Aug 20, 2007

There is currently data on each sheet in my workbook. I am trying to get a macro to Copy the formatting of sheet "Statement" and paste the formatting on all other sheets in the workbook, except Sheet.actual

There is also a picture @ the top of sheet statement, that i would like copied and pasted to each sheet except sheet.actual.

Also, on all sheets except for STATEMENT & ACUTAL, i need the formula in cell C11 =vlookup(F14,cardnum,2,false) and in cell A11 = Name :

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved