Free Microsoft Excel 2013 Quick Reference

Excel VBA to search all macro code in Excel module for specific string criteria

Hi all,

I am looking to write a Excel XP vba code which will allow me t
search for a specific search string in a Excel XP macro module for al
the "xls" spreadsheet type files in the folder location which wil
prompt me which could be "C:" .

For example..I want to search all the spreadsheets for the strin
"C:temp" hardcoded into the Excel XP macro module using an automate
solution like Excel XP VBA.

I am using Excel XP on Windows XP o/s.

Please help me out with the code as I have to present a report to m
manager in 2 days.


please don't multipost :-)

Frank Kabel
Frankfurt, Germany

Roger1947 wrote:
> Hi all,
> I am looking to write a Excel XP vba code which will allow me to
> search for a specific search string in a Excel macro module for all
> the "xls" spreadsheet type files in the folder location which will
> prompt me which could be "C:" .
> For example..I want to search all the spreadsheets for the string
> "C:temp" hardcoded into the Excel XP macro module using an automated
> solution like Excel XP VBA.
> I am using Excel XP on Windows XP o/s.
> Please help me out with the code as I have to present a report to my
> manager in 2 days.
> Thanks,
> Roger.
> E-mail:

Hi all,
I've been writing a little macro to prompt a user with a form which lets them select 2 reports to open and also lets them select a month. One report contains financial data for the month and the other report contains all data for the year. So each month someone has to copy data from report 1 into report 2.

I have so far been able to:
-create form with error handling to open files
- select values from a pivot table in report 1
- search pivot table 1 (single sheet) and copy the Name and Total $ fields

what i haven't been able to do:
When i get the data from report 1 I switch to report 2. When i get to report to i need to search all the sheets in order to find a name with the original Name field from report 1 (I then have a HUGE case statement that will tell me if name = this and month = that then paste in cell $x$y). However, whenever i try to run my search across all sheets it will only find values on the sheet that is selected at the time.

I've attached my search code below, I would appreciate any help i can get as I've spent all day trying to resolve it with no luck

    Dim ws As Worksheet 
    On Error Resume Next 
    For Each ws In ThisWorkbook.Sheets 
         ' Find Function
        Cells.Find(What:=TruncName, After:=ActiveCell, LookIn:=xlFormulas, _ 
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
        MatchCase:=False, SearchFormat:=False).Activate 
        ActiveCell.Comment.Text Text:="test" 'test to mark where the matched cells are
        On Error Goto 0 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Does anyone know of any code to close all groups on the current worksheet?

I currently have a macro that uses a range of data that contains rows which are grouped together. When the macro runs its adds, uses and removes the autofilter to find the data I want.

It all works fine but leaves all the groups "expanded" so I am hoping that there is a bit of VBA to close all groups.

Thanks in advance.

Is this Possible ?

To Search all closed workbooks in a designated folder and copy worksheets to another workbook, if combo box values are matched.

I have a userform with 2 comboboxes on it(related) where the 2nd combobox is dependant on the 1st combobox value in a smae worksheet.

I want to place this in a workbook named (FIND Sheets) and run this as a macro to search through all the closed workbooks in a particuar folder,

Then all the found worksheets that have the values in both comboboxes, to be copied and placed intot he FIND Worksheets workbook ?

There could be as many as 10 workbooks in the folder.

Can it be done ?


I am new to using VB with outlook, so please forgive me if this is something terribly obvious. Right now, this code only scans a subfolder of the inbox named "Parts". This works for me, as I am using the Rules Wizard to send all the required emails to this folder. I would like, though, to be able to scan all folder/subfolders in outlook for the appropriate mail. Any suggestions or help are appreciated.

    Dim myApp As Outlook.Application 
    Dim myNamespace As Outlook.Namespace 
    Dim myFolder As Outlook.MAPIFolder 
    Dim lngRow As Long 
    Dim myMail As MailItem 
    Dim iNames As Range, NamesRng As Range, c As Integer 
    Set myApp = New Outlook.Application 
    Set myNamespace = myApp.GetNamespace("MAPI") 
    Set myFolder = myNamespace.GetDefaultFolder(olFolderInbox) 
    Set myFolder = myFolder.Folders("Parts") 
    lngRow = 0 
    For Each myMail In myFolder.Items 
        lngRow = lngRow + 1 
        Set NamesRng = Sheets("names").Range("A1:A" & Sheets("names").[A65536].End(xlUp).Row) 
        For Each iNames In NamesRng 
            c = InStr(1, myMail.Subject, iNames) 
            If c  0 Then 
                With Sheets("rawdata") 
                    .Cells(lngRow, 1) = myMail.SentOn 
                    .Cells(lngRow, 2) = Mid(myMail.Subject, c, 10) 
                    If InStr(1, .Cells(lngRow, 2), ":") Then .Cells(lngRow, 2) = Mid(.Cells(lngRow, 2), 1, 9) 
                    .Cells(lngRow, 5) = myMail.Body 
                End With 
            End If 
        Set NamesRng = Sheets("names").Range("B1:B" & Sheets("names").[B65536].End(xlUp).Row) 
        For Each iNames In NamesRng 
            c = InStr(1, myMail.Subject, iNames) 
            If c  0 Then 
                With Sheets("rawdata") 
                    .Cells(lngRow, 3) = iNames 
                    .Cells(lngRow, 4) = Sheet2.Cells(iNames.Row, 3) 
                End With 
            End If 
        With Sheets("rawdata") 
            .Range(.Cells(lngRow, 1), .Cells(lngRow, 5)).WrapText = False 
        End With 
    Set myMail = Nothing 
    Set myFolder = Nothing 
    Set myNamespace = Nothing 
    Set myApp = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hi there!

Is it possible to use VBA to lock all cell formatting in a range of cells except for background color? I've got a form in which I need to lock down the field formats (date, etc.) but my users want to be able to "highlight" fields by changing the background color.

Thanks very much in advance!

I have attached 2 workbooks to illustrate the problem I am having.

One workbook contains a receipt. The macro is asking the user to input information into the receipt form, however one piece of information needed for the receipt, the balance due, needs to be pulled in from another workbook which contains the accounts receivable information for each family. Each sheet in this workbook is named the name of a child.

This is where I’m stuck. When the user inputs the child’s name I would like the vba code to search through the sheets in the second workbook and return the balance due for the name entered which is in cell “J42” on the sheet named the same as the child’s name the user entered.

Once it has found that sheet and entered the balance due on the receipt in cell “C10”, I would like the code to copy the amount being paid from the receipt cell “J6” to the next blank row in “Column H” of the Accounts Receivable sheet for that child.

The third workbook I have but wasn't able to include is a database I tried to create listing all the children because I was told that would be the best way to do this, but I couldn’t figure out how to get what I need from that document either. (I think trying to change how I was doing things in mid stream just made it more difficult and I don’t have adequate information in the database document to get what I need.)

The one other issue I have is making sure the user enters the name exactly as it is in the other workbooks, and I thought of having a dropdown box that pulls in the names from the database and having the user select the name from the dropdown box so it will be an exact match, but I have no idea how to have the macro allow the user to choose from a dropdown box.

Any help you can offer is very much appreciated.



Hello all,

I have no experience in Excel VBA before, that's why I tried hard to use a mixture of Excel existing formulas to calculate the accrual sum of revenue for a school's course (with a duration of less than 12 months, and 6 batches of students), and I failed several times to do so

I've heard that we can use Loop in Excel VBA to execute the task easier and more efficiently. I kept searching but my limited knowledge of coding prevented me from success.

Would you please have a look at the attached example, and teach me how to generate a Macro to implement the task. Thanks a lot for your help.

hi all,

At the moment I directly open the *pri file in our Portfolio program called Advent Axys, and manually update the ticker prices using a downloaded Excel format file from the BigCharts website which is saved as csv extension.
What I would like to do is develop a stand alone program in Visual Basic ( not in Excel VBA) to update the price file in our Portfolio system automatically using the downloaded Excel format file csv extention file from the BigCharts.
But before that, I need to export the *.pri file from our Portfolio system in to Excel which still saves as *.pri extention.
Then once it updates, I import the updated *.pri file back in our Portfolio program.
I understand that the Excel VBA code can be incorporated in Visual Basic code provided there is an object declaration for Excel file (In this case eventhough the both files are in Excel format, they don't have xls extention). Would anyone have any ideas about how to use external files and Excel VBA code in Visual Basic?

Below is the code that I currently have in Visual Basic. What I'm trying to accomplish is using the ticker (eg. msft) as a keyword search to look up in the price file. If found , the price of that ticker from the test.csv file will be copied in to the price file which is test.pri. I haven't ran it yet. Any thoughts or advice on this will be much appreciated.
Thank u so much
P.S Not able to upload the 2 files since their extention is different

Sub UpdatePrice(BigChartPath As String, BigChartName As String, AxysPricePath As String, AxysPriceName As String) 
     'Below are Excel VBA codes
     'Uses the  test.csv  to look up tickers in test.pri and update the price in it
    Dim PriceFile As Workbook, BigChartFile As Workbook 
    Dim PriceFileSheet As Worksheet, BigChartSheet As Worksheet 
    Dim MaxRows As Long 
    Dim PriceFileRow As Long 
    Dim BigChartRow As Long 
    Dim BigChartFound As Boolean 
    Call CheckBookOpen(BigChartPath & BigChartName) 
    Call CheckBookOpen(AxysPricePath & AxysPriceName) 
    Set BigChartFile = Workbooks(BigChartName) 'Big Chart website imported CSV file saved as test.csv
    Set PriceFile = Workbooks(AxysPriceName) ' pri file imported from Advent Axys saved as test.pri
    Set BigChartSheet = BigChartFile.Sheets(Sheet) 
    Set PriceFileSheet = PriceFile.Sheets(Sheet) 
    MaxRows = w1.Range("a65536").End(xlUp).Row 
    For BigChartRow = 2 To MaxRows 
         'On Error GoTo NotFound
        BigChartFound = True 
        PriceFileRow = PriceFileSheet.Range("b:b").Find(BigChartSheet.Cells(BigChartRow, 2).Value).Row 
        If BigChartFound = True Then 
            PriceFileSheet.Cells(PriceFileRow, 3).Value = BigChartSheet.Cells(BigChartRow, 4).Value 
            PriceFileSheet.Cells(PriceFileRow, 3).Value = "not found" 
             'PriceFileRow = PriceFileRow + 1
        End If 
    End If 
Next BigChartRow 
Exit Sub 
 'w3.Cells(w3Row, 1) = w1.Cells(w1Row, 1)
 'w3.Cells(w3Row, 2) = w1.Cells(w1Row, 2)
 'w3.Cells(w3Row, 3) = "not found"
 'w3Row = w3Row + 1
 'w2Found = False
Resume Next 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a list of excel (2010 version) files in a folder (eg: c:tempwork). The files in this list are all in the same format and contain 1 sheet in each file.

I need a VBA to import the data in each file, found in the folder above, into one worksheet, listing the contents of each file, and then save the new file with the combined data. However, the code needs to be dynamic in the same that the number of files and the filenames change, therefore, the code has to automatically pick up the list of files in the folder.

Can anyone help, please.


I am looking to write an automated program in VBA where it will search and
replace several variables with updated names. I also need to update
different template or VBA projects in order to do this because there are many
Excel projects that also need to be updated in the same directory. How would
I code this to automatically open up Excel worksheets, replace variables in
the VBA macro code in order to update it, and then close it when done. Then
it would open up the next Excel worksheet. Anybody have an idea?



I want to use VBA to insert an Organisation Chart in Excel 2007 (ie if I did this manually it would be Insert, SmartArt, Hierarchy), but I cannot find anything on line that shows how to do this in Excel 2007. I know the code for Excel 2003, but that does not work in 2007.

Many thanks in advance for any help anyone can provide.


I'm trying to use Excel VBA to open all text files in a fold (one at a time) as a workbook, run a macro, then close the workbook without saving it. I have found at least one way that does not work:

    Dim fs As FileSearch 
    Dim i As Integer 
    Dim wbk As Workbook 
    Set fs = Application.FileSearch 
    With fs 
        .LookIn = "C:UsersJDDocumentsHH25nl" 
        .FileName = "*.txt" 
        .FileType = msoFileTypeAllFiles 
        For i = 1 To .Execute() 
            Set wbk = Workbooks.OpenText(FileName:=.FoundFiles(i), DataType:=xlDelimited, Comma:=True) 
            Call HandRecords 
            Set wbk = wbk.Close(SaveChanges:=False) 
        Next i 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If I change the For/Next loop to:

    MsgBox .FoundFiles(i) 
Next i 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
it will message box each file in the folder. That tells me I'm not doing something right with the .OpenText part of the code.

Any ideas?

Edit: I'm using Excel 2003

hi all,
this is some sort of complicated thing for me

note: "~" means next column
"/" means Or

Lets say i have this data: (only a small number of fake sample)
Jupitor Land St 21 Blk 346 #7-459 Jupitor 869573
Jupitor Way Rd 6 Blk 73 #4-43 Jup 456421
Marssy Round Ave 8 Blk 9 #20-9785 Mars 098234

What i need is using excel vba to create a macro that:
1. identifies the pattern of address according to each heading which they belong to:

B1 ~ C1 ~ D1 ~ E1
Street Address ~ Blk ~ House # ~Postal Code
Jupitor Land St 21 ~ Blk 346 ~ #7-459 ~ Jupitor (869573)
Jupitor Way Rd 6 ~ Blk 73 ~ #4-43 ~ Jup 456421
Marssy Road Ave 8 ~ Blk 9 ~ #20-9785 ~ Mars098234

in this case, each heading that would be sorted would be identify by a unqiue key:
street address >identified by > St/Rd/Ave
Blk > identified by > Blk
House # > identified by > #
Postal Code > identified by > Jupitor/Jup/Mars

in addition, other things like: "," or "(" or ")" if they can be removed from the strings it would be greater for me as the data would be sorted more "nicer" for the user. ---> this part not necessary if its too time consuming

i have further attached a sample of what i need so that u guys wont be confused.
I have little knowledge of sorting in excel vba and hope somebody can help.
Thanks in advanced!!

ps: hope that i am not asking too much :/

I have a spreadsheet (Excel 2007) that uses vba to create a Word 2007 document. Quite a few things flow from Excel to Word, including data and tables. Pushing info in that direction works fine.

However, I would like to insert equations into the Word document similar to Word's Equation Editor, but I can't seem to find a way to control Equation Editor through vba. I'm most in need of horizontal fractions. (Word 2007 calls these "professional.")

One solution would be if there was a way to control Equation Editor through vba. Does anyone know how?

Another solution might be to use LaTeX codes in Word. I've played around with it a little, but haven't found a way to, say, enter the code in a cell in Excel and have the equation show up in Word.

Another solution would be create equations in another way. I've read some about the EQ Field Code, but online documentation seems to be for Word 2003 rather than 2007, so I'm worried that this field code isn't supported anymore.

Anybody have advice about where to proceed?
Chris Lenius

I want a macro i can run on a selected Pivot table, to set all pivot table field sub-totals to "None".
Excel has no default setting that i am aware of to set sub-totals to none, before or after creating a pivot table. Without individually selecting each field and setting to none.

The next level would be to show all feilds in a combo box, and select any or all to set to nothing etc.

Any help would be appreciated.

How can I Export and import Excel Macro from an Excel Template in Centralized location? Whenever user opens the Template this code gets imported at runtime.

All macro code in file1 kept in Shared drive.

Whenever any user opens Template it gets all the code for macro from this Shared Drive --> template.

For that I need to have some code within all the templates to get this code from shared drive.

i) Importing macros

ii) have all code in VBS format in the shared drive

But for First solution it not working. I will show you what I have worked on and could you please tell me what is wrong.

Please find the code which i have worked on however has issues with it when import line is execute. It doesn't perform any action though it executes that line.
Private Sub Workbook_Open()
'Call Sheet1.updatescreens
Call OpenSourceExcel
Call CopyMacroModule(Workbooks("Smoke_Test.xls"), "Test Script")
'Call CopyMacroModule(Workbooks("Smoke_Test.xls"), "Result")
'Call CopyMacroModule(Workbooks("Smoke_Test.xls"), "Datapool")
'Call CopyMacroModule(Workbooks("Smoke_Test.xls"), "Object Repository")
Call CloseSourceExcel
End Sub

Sub CopyMacroModule(SourceWB As Workbook, strModuleName As String)
strFolder = SourceWB.Path
arun = ThisWorkbook.Path
arun1 = ThisWorkbook.Name
Set Targetwb = ThisWorkbook
MsgBox arun1
temp = arun & "" & arun1
MsgBox temp
If Len(strFolder) = 0 Then strFolder = CurDir
strFolder = strFolder & ""
strTempFile = strFolder & "tmpexport.bas"
MsgBox strFolder
SourceWB.VBProject.VBComponents(1).Export (strTempFile)
MsgBox ActiveWorkbook.Name
Targetwb.VBProject.VBComponents.Import (strTempFile)
Kill strTempFile
End Sub

Private Sub OpenSourceExcel()
strSourceSheet = "C:Smoke_Test.xls"
Set objExcelSource = CreateObject("Excel.Application")
Set objSpreadSource = objExcelSource.Workbooks.Open(strSourceSheet)
End Sub
Private Sub CloseSourceExcel()
End Sub

Dear all,

In order to delete all define name in Excel I create a below macro:

Sub NamTest()

For i = 1 To ActiveWorkbook.Names.Count

 MyName = ActiveWorkbook.Names.Item(i).Name

End Sub
However, there is an error message 'This name is not valid'.

Could anyone can help me.



Hi Hi..... i have a problem, can any kind soul help me??
heehee thanxs!!

My problem is: i need to type vb macro codes in excel to search whether this 3 characters "SIN" is in the beginning of a string. it got to do with LIKE operator....heehee...if not ermm...then how :P??

How to use various macros available in different EXCEL sheets into every newly created EXCEL sheet...?

I have different macros in different sheets...
and every time i require any macro to be run i need to open the sheet containing the marco in it.
Can i automate for macros to be available in every new sheet i open.

Hi Experts,

I need a code to protect all the sheets in a workbook dynamically. i may add sheets or delete sheets but protection of the sheets should be dynamic.

thanks in advance.

Hi - I have a macro which is working fine when the code is pasted in a normal module. The macro however stops working when I paste the code in the sheet module. I'm trying to keep this particular macro's code in the sheet module since this sheet is meant to be transferred between files by users. If I try to run the macro using the button assigned to it, it gives a '400' error. If I run the macro directly from VB, then it gives a 'Run-time error '1004': Application-defined or object-defined error'.

I'm a VBA amateur and from some reading on the internet, it seems that I shouldn't be pasting any complex macro code in the sheet modules. I was wondering though if there's any way to modify a macro so it can be pasted in a sheet module (and hence be transferred when the sheet is moved to a different file). Or is there a different way to transfer a macro code automatically when the sheet is moved to another file? Thanks.

Following is the macro code:

    Application.ScreenUpdating = False 
    Dim tgtclmn As Integer 
    tgtclm = Worksheets("Control").Range("BX12").Value 
    Dim c1 As Integer, c2 As Integer, c3 As Integer, c4 As Integer 
    Dim cf As Integer 
    Dim sheetname As Range 
    For Each sheetname In Range("JS24:JS41") 
        c1 = Worksheets("Verify").Range("JT7").Value 
        c2 = Worksheets("Verify").Range("JT8").Value 
        c3 = Worksheets("Verify").Range("JT9").Value 
        c4 = Worksheets("Verify").Range("JT10").Value 
        cf = Worksheets("Verify").Range("JT18").Value 
        cfe = cf + 4 
        Range(Cells(5, cf), Cells(150, cfe)).ClearContents 
        Range(Cells(6, c1), Cells(1005, c4)).ClearContents 
        Range("A6:E1005").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _ 
        (Cells(2, cf), Cells(3, cf)), CopyToRange:=Cells(5, cf), Unique:=True 
        i = Worksheets("Verify").Rows.Count 
        ActiveSheet.Outline.ShowLevels columnlevels:=3 
        ActiveSheet.Range("$A$200:$BS$1600").AutoFilter 4, "Frm" 
        Worksheets("Verify").Cells(i, c1).End(xlUp).Offset(1, 0).PasteSpecial xlValues 
        Worksheets("Verify").Cells(i, c2).End(xlUp).Offset(1, 0).PasteSpecial xlValues 
        Worksheets("Verify").Cells(i, c3).End(xlUp).Offset(1, 0).PasteSpecial xlValues 
        Worksheets("Verify").Cells(i, c4).End(xlUp).Offset(1, 0).PasteSpecial xlValues 
        Application.CutCopyMode = False 
        ActiveSheet.Outline.ShowLevels columnlevels:=2 
        Range(Selection, Selection.End(xlDown)).Select 
    Next sheetname 
    Range("JS7:KM10") = Range("JS12:KM15").Value 
    Range("JS18:KM18") = Range("JS20:KM20").Value 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hi All.

I'm using the below code to update all pivot tables in a workbook based on the data selected in a validated cell which contains a list of company names. It's running very slowly (but it runs), I'd appreciate any pointers to speed it up.

Sub Refresh_PT_Partner2()

Application.ScreenUpdating = False

'Update filter on all worksheets to equal user entered value

    Dim WS As Worksheet
    Dim pt As PivotTable
    Dim pi As PivotItem
    Dim Partner As String
    Partner = Range("Selected_Partner")
    On Error Resume Next
     For Each WS In Worksheets
        For Each pt In WS.PivotTables
            pt.ManualUpdate = False
            For Each pi In pt.PivotFields("Partner").PivotItems
                pi.Visible = pi.Value = Partner
            Next pi
            pt.ManualUpdate = True
        Next pt
    Next WS
    On Error GoTo 0
        Application.ScreenUpdating = True

    End Sub
It also works intermittently - sometimes I have to run it twice to make it work.

I put this section of code in twice which remidied it, but I'm not convinced it's the most efficient method!

     For Each WS In
        For Each pt In WS.PivotTables
            pt.ManualUpdate = False
            For Each pi In pt.PivotFields("Partner").PivotItems
                pi.Visible = pi.Value = Partner
            Next pi
            pt.ManualUpdate = True
        Next pt
    Next WS
Thanks All.


I need the simplest VBA to look up zip codes for P. O. Boxes & put them in column I when the box number is entered in column L and the words P. O. Box (with that exact syntax) exists in column K.

The look up data base exists in range A1 thru D7 of a sheet tab named ZIPLGV where the beginning range number is in column C & ending range number is in column D.

For example, when the following is entered in a sheet tab named BUSINESS:
______ ___K____ __L_
row 236 P. O. Box 7039

Looked up in:
___A_____ __B__ __C__ __D___
1 P. O. Box 75606 000001 004899
2 P. O. Box 75608 005002 006964
3 P. O. Box 75607 007001 008940
4 P. O. Box 75608 009001 011996
5 P. O. Box 75607 012001 013680
6 P. O. Box 75608 016002 019176
7 P. O. Box 75615 150001 151854

returns in sheet tab named BUSINESS:
_______ ___I__
row 236 75607

If the zip code is not found within one of the ranges in the data base OR the exact word P. O. Box is not in column K, then column I should remail blank.

I really appreciate all your help. mikeburg