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


ADVERTISEMENT

VBA Code For Selecting Specific Cells Within Worksheet?

Aug 28, 2013

know the VBA script to select only specific cells within a worksheet. To be more precise, on sheet 1 - A2, A6 and A10 cells are filled with red color. I want only those cells which are highlighted in red to be selected, copied and pasted to sheet2.

View 3 Replies View Related

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 View Related

Making Existing Code Work For Specific Worksheet/s

May 30, 2013

I would like the following code to be run for all those sheet/s that has a name = "single" (Not case sensitive neither an exact match) of my active workbook.

Code:
Sub UIUIUI()
Dim LR As Long, i As Long
LR = Range("I" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With Range("I" & i)
If .Offset(, -1).Value = 1 Then .Value = .Value & "-"
End With
Next i
End Sub

View 3 Replies View Related

Macro Code Only Works If Specific Worksheet Active

Apr 30, 2008

I have searched the FAQ's but have not found a suitable answer to my problem. I have some code that works perfectly when it is run from the VB Editor but when I put it behind a command button it gives me an error almost straight away. I have read that when a command button is used the command button defaults the active sheet to the one that it is one therefore you always have to specify the active sheet but I have done this so am still confused as to why it is falling over. Below is my code, I have commented where it is tripping:

Sub FormattingAcutalReport()
Workbooks.Open Filename:="H:Risk ReportingDaily TemplatesMF Consolidated Risk DAILY LIVE DATA FROM BO.xls"
Workbooks.Open Filename:="H:Risk ReportingDaily TemplatesDaily Non Banks LIVE.xls"
Dim myBorders() As Variant, item As Variant
Set SEGNSEG = Workbooks("Todays Reports.xls").Worksheets("Seg and Non Seg Bank Summary")............................

View 2 Replies View Related

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

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

VBA Code For Adding A Specific Worksheet Condition Within Subfolder Consolidation Macro

Aug 22, 2013

The issue is the I want to put the condition here in my the code that only copies the desired cells if the sheet is named specifically such as "Jan", if not named as this the worksheet should not be used. The current code I am using was posted on this site in 2009: VBA to copy specified cells from all the files in a folder

My modified code is:

Option Explicit
Public strSourceFldr As String
Public EachFile As Object
Public objFSO As Object
Public objFolder As Object
Public objFile As Object
Public strSheetName As String

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

View 1 Replies View Related

Applying Time And Date In Worksheet After Updating

Feb 6, 2007

all i need to write is a code which syncronizes with my update button, which i don't know how to write....whenever i click the update button, all my files will be updates, and at the top elft of the screen would show the time and date of my last update.

View 6 Replies View Related

Excel 2010 :: Set For Specific Worksheet

Jul 30, 2014

I am using lesson 33 of Excel VBA and Macros with Mr Excel as my guideline. The coding is as follows:

Dim WBO As Workbook 'original workbook
Dim WBN As Workbook 'individual data workbooks
Dim WSL As Worksheet 'List of files worksheet
Dim WSN As Worksheet

Set WBO = ThisWorkbook
Set WSL = WBO.Worksheets("List")
Set WSD = WBO.Worksheets("Data")

[Code] ......

I have indicated above with ( ) the beginning of my problem.

From the workbook with the list of workbooks to open, in each case I want to refer to a worksheet in each workbook
named "Report" and grab all the rows with data exept heading (rows 1 & 2) and 9 columns.

How do I get WSN in the code above to include the worksheet named "Report" or do I need different coding. Using Windows 7 & Excel 2010.

View 1 Replies View Related

Excel 2010 :: Macro To Pull AP Within Specific Zip Code?

Jun 15, 2012

I have an excel 2010 spreadsheet that lists all of our vendors and the amount we spent with them over the last year.

I want to know how many of them were local vendors.

I have a list of all the zip codes within a 100 mile radius (there are about 500 zip codes). I would like to write a macro or sort function that searches the entire vendor list and only reports back those vendors that are included in the zip codes I specify.

The columns are as follows:

A B C D E
Vendor City StateZip CodeTotal AP
Vendor 1TROY AL36082527.37
Vendor 2PHOENIXAZ85054100
Vendor 3TUCSONAZ8571416255

I want to keep the all the columns, I just want to eliminate all of the vendors that do not fall within the zip code criteria I set.

View 5 Replies View Related

Excel 2010 :: VBA - Run Code Only When Clicking In Specific Cell?

Dec 23, 2013

how to run some VBA code (written by someone else, unfortunately) only when clicking once in cell A1, and not run in any other cell. This is being run in Excel 2010. The code I would like to run in this manner is below, and currently will run when the user clicks on any cell in the worksheet it is applied.

[
Option Explicit_________________________________________
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'If IsDate(Target.Cells(1, 1).Value) Then
Set DatePickerForm.Target = Target.Cells(1, 1)
DatePickerForm.Show vbModal
Cancel = True
'End If
End Sub
]

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

Create Button In Excel With Macros Or VBA Code In A Specific Cell Targets?

Jul 9, 2014

I have created a individual tracker spread sheet in each user system and on the spread sheet I have inserted 2 buttons in 1st sheet. when employee wants to take a break he has to click on the log out button and when he came back he needs to click on login button. I have another sheet named "timings" and I have created 2 columns, one is login and another is logout. What I need is when user click on the login button, the system time and date to be auto update under login column and when he clicks on logout button the system time and date to be auto update under logout column.

View 14 Replies View Related

Visual Basic Code - Open Local PDF At Specific Anchor From Excel?

Aug 8, 2012

I want to create a series of hyperlinks in excel to pdf's stored on a local drive/CD as a kind of database. This much I can do with a nifty bit of code I saw someone put up here as follows;

Sub Run_Local_PDF()
MyPath = "C:Program FilesAdobeReader 9.0ReaderAcroRd32.exe"
myFile = "C:Documents and SettingsmynameDesktoppdffilename.pdf"
Shell MyPath & " " & myFile, vbNormalFocus
End Sub

Which works great and is sitting snug in a module. However, the document I am linking to has various anchors in it which exisit as sectionheaders. One of which is "Contents".

Is there a quick way I can alter my exisitng VB code to open at one of these anchors?

View 2 Replies View Related

Prevent Excel From Applying Table Styles?

Dec 1, 2011

Is there a way of preventing Exel from Applying table styles, when converting a range to a table. and leave the formatting of the table intact?

View 1 Replies View Related

Open All Excel Files In Directory - Code Doesn't Work For One Specific Path

Feb 8, 2014

I have my code here:

VB:
Sub openfiles()Dim Path As String
Dim ExcelFile As String
' Path = GetFolder("C:UsersKinteshDesktop")
Path = "C:UsersKinteshDesktopVBA programmingMaps"
ExcelFile = Dir(Path & "*.xls")

[Code] ....

NextCode:
GetFolder = sitem
Set fldr = Nothing
End Function

My problem is that the code all actually works (including the function and when I use the commented part), but pointing to this one specific directory (the one I'm using right now), literally nothing happens.

View 6 Replies View Related

Excel 2010 :: VBA Code That Handles Several Specific Parameters Or Wildcard Passed To Access

Jul 17, 2013

I would like to develop a user-friendly Excel 2010 spreadsheet that would allow the user to enter from 1 to 10 (i just picked this number at random just to have an upper limit) employee numbers in cells A1-A10 and "click" on the VBA Code button that would invoke an Access Query and in the process return all the applicable data gathered from the query back into the Excel Spreadsheet. Believe it or not, I can do all this mentioned so far. BUT what i cant do is my second option for the user which is allow them to pull ALL of the employees back into the spreadsheet using the same query.

In summary, using Excel as a frontend dashboard, I know how to get a specific number of employees' information from a query and I know how to get all employees' information from a query but I dont know how to get either/or. In other words, I could do this with two queries and two "VBA-Code" buttons but I would like to do it with one query and one button.

View 1 Replies View Related

Excel 2010 :: Macro For Applying Changes To Multiple Files

Aug 16, 2012

Excel 2010, Windows 7

I have a whole ton of *.dbf files (98 to be exact), that are from ArcGIS shapefiles. These date1_date2.dbf files have point names, and values, as shown below.

NAMERASTERVALU
Point 1-9999.00000000
Point 2-9999.00000000
Point 3-9999.00000000
Point 4-4.93072701
Point 5-8.90071201

I'm trying to figure out how to transpose each of the dbf files and save them as a text file (tab deliminated). My original plan involved saving them all as text files, then using a convoluted Unix script (I'm what you call a "dirty programmer") to transpose them and then combine them all into a single file. Since there are so many files (and I plan to do this in the future as well), I thought if I could at least automate the text file creation, I'd be ahead of the game. Even better would be a macro that will transpose the data for EACH file, then create a 99 (98 files + header row) row, 5 column text file. Creating the text files (1 text file for each dbf file) automatically .

All the *.dbf files will have exactly the same # rows/columns (2 columns, 6 rows)

This is what I've tried so far.
1)open one of the dbf files in Excel
2)record my marco (using the Personal Macro Workbook option - so it will be available in any workbook)
2a) transpose data (and paste it right below the original data so that the new data is in rows 7 and 8 of the same file)
2b) save as text file (tab deliminated)
2c) stop recording, and end up with this:

NAMERASTERVALU
Point 1-9999.00000000
Point 2-9999.00000000
Point 3-9999.00000000
Point 4-4.93072701
Point 5-8.90071201
NAMEPoint 1Point 2Point 3Point 4Point 5
RASTERVALU-9999.00000000-9999.00000000-9999.00000000-4.93072701-8.90071201

So now I want to apply the macro I just created to all open workbooks (after I bring in all the bdf files), but the macro isn't listed when I click the "view macros" button. All I want to do is apply the steps I recorded in the macro to all open workbooks.

View 6 Replies View Related

Excel 2013 :: Copy Entire Row If Cell Contains Specific Text And Paste Values Into Another Worksheet

Jun 23, 2014

I am using the code below in Excel 2013.

Sub Test()
For Each Cell In Sheets(1).Range("J:J")
If Cell.Value = "131125" Then

[Code]....

This works great except that it pastes formulas. I would like to paste values only. I've tried
" PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False" and it gives me an error.

View 3 Replies View Related

Excel 2007 :: Applying Conditional Formatting To A Dynamic Named Range?

Jul 10, 2012

I have an Excel 2007 Workbook which is refreshed by a Web Query. I have dynamic named range defined as "Manager" which I've confirmed is correctly identified. This range includes only one column and is formatted as text.

When I try to reference this range in my conditional formatting "refers to" formula, all cells are recognized as blank even though the range clearly contains many cells that are not. For example, there are 90 records containing the initials "PD". If I use the formula =Manager="PD" in my conditional formatting, nothing changes. If I change it to =Manager="" then ALL records are formatted, including those that are not blank.

Stranger still, if I enter the formula =COUNTIF(Manager,"PD") into and empty cell in my worksheet those 90 records are counted correctly. Which leads me to believe it isn't about the data. Conversely, =COUNTIF(Manager,"") returns the correct count of only cells that are, in fact, blank.

I've tried using the OFFSET formula defining my range in place of the name itself for my conditional format formula to no avail.

View 2 Replies View Related

Excel 2010 :: VBA Code To Connect To SQL And Pull Records To New Worksheet

Oct 18, 2012

i found this code...

Code:
Sub Button1_Click()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String
Dim SNfound As String
'Your sqlserver 2008 connection string
Const stADO As String = "Provider=SQLOLEDB.1;" & _
"" & _

[code].....

but i dont see where to put in the Database object...

The Database it needs to connect to in SQl is called

The Server name is SQLSRV when you expand databases the database is called SWHSystem the Table is called dbo.Credential and from that i need to get SELECT All from the Name and CardNumber from dbo.Credential and put that in a New Sheet titled Personal

using Excel 2010 connecting to SQL 2008

View 4 Replies View Related

Excel 2010 :: Hide Worksheet Password In VBA Code Without Project Protection

Nov 5, 2012

I have a 2010 excel workbook with several locked worksheet (to which I manage the PW). I and another staff member manage different section of the macro but the other staff member doesn't have access to the locked areas.

Is there a way I can encrypt the password within VBA so it's not visible to the other staff member?

Locking the VBAProject doesn't work as the other staff member has to be able to edit his VBA section.

Many staff run the macro (via a button) and don't need to access the Macro and don't have access to the protected sheets.

I understand excel isn't ideal with PW protection for people wanting to bypass the protection and this isn't an issue.

View 3 Replies View Related

Change Table Filter Greater And Less Than To Specific Value In Specific Cell And In Another Worksheet?

Apr 16, 2014

I would like to change the greater than number to the value in cell 'I11' in sheet 'Linear Programming Data' and the less than number to the value in cell 'I12' in sheet 'Linear Programming Data'. The code I am using was done by recording a macro since I don't know how to code in vba.

Code:
Sub Results2()
' Results2 Macro
' copy table filter power by greater than and less than
Sheets("Finalizing Results 2").Select
Cells.Select

[Code] .......

View 2 Replies View Related

Excel 2010 :: After Applying A Data Filter And Sorting The Data / How To Revert Back To Original

Dec 20, 2012

I'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?

View 3 Replies View Related

Writing Specific Content From Excel To External File Of Specific Name

Apr 29, 2013

I have a worksheet that contains two basic columns of data, A and B. What I would like to do is based on the value in column A, I would like to copy the contents of column B to a textfile (preserving the basic line structure and hopefully without any extra characters like quotation marks attached). So for all the values in column A = 'Account', Id like to take all the corresponding values in Column E (for example) and save them to the same file, preferably where the filename itself as 'Account.xyz'. And I would like to do this for all unique values in Column A, that is do it for 'Account' and 'AccountPrivilege' etc. Note Column A is essentially presorted alphabetically already. There are actually 1000s of rows with hundreds of unique A values so Id like to find a way to automate this process.

A
B

1
Account
Company Information

2
Account
Company

[Code] ........

View 2 Replies View Related

Macro To Copy Six Specific Columns From One Worksheet To Another Worksheet

May 12, 2008

What I am trying to do is to write a macro that will automatically copy six columns from worksheet (Sheet 1) to another worksheet (Sheet 2). i.e. ‘Description of Project’, ‘WBS Code’, ‘Rate’, ‘Employee Name’, ‘Premium’, ‘Invoice’, ‘Status’, ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ from Worksheet (from Sheet 1 to Sheet 2)

The problem arises as I know the names of the columns to be copied in Sheet 1 (as details above) but they can be in any order in sheet 1.

In additional the columns ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ are total columns so when they are copied from ‘Sheet 1’ to ‘Sheet 2’ their values should be copied as opposed to the formulas

View 9 Replies View Related







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