Excel Macro VBA Tip 1 - How to Write Your Very First Macro in Microsoft Excel

My Perso.xls contains macros that pre-processes files to a specific format. I want to add a macro (lets call it MacroX) to be active in only one of the Excel files being pre-processed and not active in any other excel files including my Perso.xls.

So I think I want the MacroX code to be associated only with the newly created activesheet and not as a new module in the Excel active workbook.

Here is how I see it... ( and of course, it doesn't work !)

Code:

 
Dim MacroX_text as String
MacroX_text= "Private Sub Worksheet_SelectionChange(ByVal Target As Range) " & VbCrLf & _  
    "With ActiveCell " & vbcrlf & _
    "   If .Value = ""View in GE"" Then .....  End If " & VbCrLf & _
    "End With " & VbCrLf & _    
    " End Sub"
Sheets.add   
Activesheet.Macro.add (MacroX_text)

So... how do I access VBA project (from within a VBA macro) to insert text MacroX_text and save it in the newly create Excel workbook ?

Attached is an example of the spreadsheet I am working with. The "address and vendor id example" tab shows what I need the macro to do.

Essentially I am looking for the macro to do the following:

1) If there is only one instance of the vendor name, leave the row as-is.
lines 4,5 and 6 from the example tab are examples of one instance of a supplier name, need to leave these rows as-is
lines 3, 4 and 12, 13 are 2 instances of the same supplier name, the macro will have to modify these lines

2) If there are 2 instances of the same supplier name with the same mailing address 1, I would like to combine those 2 lines into one. The macro will need to transpose the ID's horizontally and then merge into one cell with a comma and 2 spaces separating them.
lines 3, 4 and 26, 27 from the "example" tab are a good example of this

3)If there are 2 instances of the same supplier name with different mailing addresses, I just need the macro to fill in the supplier name for the row following the first instance of the supplier name
lines 13 and 59 from the "example" tab are a good example of this

I know this is somewhat unclear but hopefully the examples i did in the "address and vendor id example" tab will help clarify.

I haven't come across a situation yet that this website hasn't solved. Thanks for all your help!

Jeff

Hello,

I was wondering if someone can help me write a macro that does the following:

there are a list of questions:

Question 1

(other info)

Question 2

(more info)

Question 3

(info about Q3)

.. etc.

I want to create a macro which will find the cell containing "Question 1", "Question 2", etc..and make the entire row be highlighted grey and bolded in white and underlined. There can b upto 30 questions or more.

Hi,

I have a strange situation that when I run the code below it will create
an Microsoft error report, but only from the 2nd time the macro is executed.

The wierd situation is that it only happens when the Macro Is Executed
By a Button Object. If I run it directly from the Macro List, There Is No
Problems, Only when I run the Code From an Object (Button).

The Code Is only suppose to delete the active sheet.

Here is the Code.. Thanks For any help on this.

HTML Code:

Sub DEL_SHT()

' DEL_SHT Macro

On Error GoTo ERR
If ActiveSheet.Name = "NEW LOAN" Then Exit Sub
    ActiveWindow.SelectedSheets.Delete
ERR:    Exit Sub
End Sub

Hello All,

I am currently working on a macro code that will reformat a workbook I have that has around 100 worksheets in it. I have no problem recording the macro and applying it one sheet at a time. The only issue I have is I would like to edit the code created so that when I push Ctrl+g it works on all 100 sheets on its own. Here is the code I currently have. Keeping in mind I am on Windows Vista and using Excel 2007. Thanks.

Sub Macro2()
'
' Macro2 Macro
' GCI macro made NOV 5th 2009
'
' Keyboard Shortcut: Ctrl+g
'
Range("B10").Select
ActiveCell.FormulaR1C1 = "=R[-9]C[-1]&R[1]C&2010"
Range("C10").Select
ActiveCell.FormulaR1C1 = "=R[-9]C[-2]&R[1]C&2010"
Range("D:D,G:G,J:J,M:M").Select
Range("M1").Activate
Selection.Delete Shift:=xlToLeft
Range("D10").Select
ActiveCell.FormulaR1C1 = "=R[-9]C[-3]&R[1]C&2009"
Range("E10").Select
ActiveCell.FormulaR1C1 = "=R[-9]C[-4]&R[1]C&2009"
Range("F10").Select
ActiveCell.FormulaR1C1 = "=R[-9]C[-5]&R[1]C&2008"
Range("G10").Select
ActiveCell.FormulaR1C1 = "=R[-9]C[-6]&R[1]C&2008"
Range("H10").Select
ActiveCell.FormulaR1C1 = "=R[-9]C[-7]&R[1]C&2007"
Range("I10").Select
ActiveCell.FormulaR1C1 = "=R[-9]C[-8]&R[1]C&2007"
Range("J10").Select
ActiveCell.FormulaR1C1 = "=R[-9]C[-9]&R[1]C&2006"
Range("K10").Select
ActiveCell.FormulaR1C1 = "=R[-9]C[-10]&R[1]C&2006"
Rows("10:10").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:9").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
ActiveCell.FormulaR1C1 = "Economy"
Rows("2:2").Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=120
Rows("136:136").Select
Selection.Delete Shift:=xlUp
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
Columns("A:K").Select
Columns("A:K").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Rows("1:1").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Rows("1:1").Select
Selection.Replace What:="1.", Replacement:="a", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Rows("1:1").Select
Selection.Replace What:="2.", Replacement:="b", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Rows("1:1").Select
Selection.Replace What:="3.", Replacement:="c", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Rows("1:1").Select
Selection.Replace What:="4.", Replacement:="d", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Rows("1:1").Select
Selection.Replace What:="5.", Replacement:="e", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Rows("1:1").Select
Selection.Replace What:="6.", Replacement:="f", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Rows("1:1").Select
Selection.Replace What:="7.", Replacement:="g", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Rows("1:1").Select
Selection.Replace What:="8.", Replacement:="h", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Rows("1:1").Select
Selection.Replace What:="9.", Replacement:="i", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Rows("1:1").Select
Selection.Replace What:="10.", Replacement:="j", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Rows("1:1").Select
Selection.Replace What:="11.", Replacement:="k", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Rows("1:1").Select
Selection.Replace What:="12.", Replacement:="l", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

I have 2 files one is query data and other is report.the query data changes
periodically.report is made from the query data. how should i write a macro
for this dynamic data.

Hi,
I need help to write a MACRO, wherein at the specified interval, system will automatically take a backup of the excel.

wanted programmer to help write macros to run in excel. freelance
project and renumeration will be awarded. Basically is to update data
values and analysis of the data, prompts to be created when certain
scenarios occur.

pls contact me personally via email. iantontan@gmail.com

thanks.

I have a macro set up in Excel that formats and deletes rows matching a citeria. Once the macro runs I then manually copy the data across into Word. I would like to automate this.

I would like the macro to copy over any cells containing data iinto a new word document. I also have standard text that I would like to include at the beginning and end of the word document. With the excel data being placed in the centre.

I have searched the web and tried a couple of macros with no luck. All the macros state "' requires a reference to the Word Object library:
' in the VBE select Tools, References and check the Microsoft Word X.X object library"

I am unsure how to reference this - but I have checked and found that the object library ticked is Microsoft Word 11.0 Object Library.

Hoping someone can help