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.
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.
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
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
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")............................
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:
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 .
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
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.
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.
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.
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 ]
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...
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.
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?
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.
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.
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.
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.
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.
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
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.
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
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?
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.
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
