How to add same text to all sheets in excel in a macro

[almst791]

Hey guys, I have some text Id like to add to the same cells in all my sheets in my report. Sheet names will change constantly is there any macros around this. I want to add text in these cells in excel. D68:D83.

[TABLE="width: 223"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Non-Reimbursable[/TD]
[/TR]
[TR]
[TD]Airfare/Baggage Fees[/TD]
[/TR]
[TR]
[TD]Fuel/Taxi/Other Transport[/TD]
[/TR]
[TR]
[TD]Lunch-Employee only[/TD]
[/TR]
[TR]
[TD]Parking/Tolls[/TD]
[/TR]
[TR]
[TD]Mileage Reimburse[/TD]
[/TR]
[TR]
[TD]Dinner-Employee only[/TD]
[/TR]
[TR]
[TD]Miscellaneous[/TD]
[/TR]
[TR]
[TD]Breakfast-Employee only[/TD]
[/TR]
[TR]
[TD]Rental Car[/TD]
[/TR]
[TR]
[TD]Lodging[/TD]
[/TR]
[TR]
[TD]Business Meals w/MCK Staff[/TD]
[/TR]
[TR]
[TD]Entertainment Events[/TD]
[/TR]
[TR]
[TD]Hotel (to be itemized)[/TD]
[/TR]
[TR]
[TD]Meal Exception – Employee Only[/TD]
[/TR]
[TR]
[TD]Business Meals w/Non MCK Staff[/TD]
[/TR]
[TR]
[TD]Phone/Internet-Airline/Airport[/TD]
[/TR]
[TR]
[TD]Gifts-Empl (to be itemized)[/TD]
[/TR]
[TR]
[TD]Phone/Internet-Lodging[/TD]
[/TR]
[TR]
[TD]Meeting Room Rent/Audio/Visual[/TD]
[/TR]
[TR]
[TD]Dues/Memberships/Subscriptions[/TD]
[/TR]
[TR]
[TD]Registration/Seminars/Training[/TD]
[/TR]
[TR]
[TD]TradeShow Exp – Mtg Dept Only[/TD]
[/TR]
[TR]
[TD]Gifts-Customers[/TD]
[/TR]
[TR]
[TD]Late Fees Non-Reimbursable[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 223"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Non-Reimbursable[/TD]
[/TR]
[TR]
[TD]Airfare/Baggage Fees[/TD]
[/TR]
[TR]
[TD]Fuel/Taxi/Other Transport[/TD]
[/TR]
[TR]
[TD]Lunch-Employee only[/TD]
[/TR]
[TR]
[TD]Parking/Tolls[/TD]
[/TR]
[TR]
[TD]Mileage Reimburse[/TD]
[/TR]
[TR]
[TD]Dinner-Employee only[/TD]
[/TR]
[TR]
[TD]Miscellaneous[/TD]
[/TR]
[TR]
[TD]Breakfast-Employee only[/TD]
[/TR]
[TR]
[TD]Rental Car[/TD]
[/TR]
[TR]
[TD]Lodging[/TD]
[/TR]
[TR]
[TD]Business Meals w/MCK Staff[/TD]
[/TR]
[TR]
[TD]Entertainment Events[/TD]
[/TR]
[TR]
[TD]Hotel (to be itemized)[/TD]
[/TR]
[TR]
[TD]Meal Exception – Employee Only[/TD]
[/TR]
[TR]
[TD]Business Meals w/Non MCK Staff[/TD]
[/TR]
[TR]
[TD]Phone/Internet-Airline/Airport[/TD]
[/TR]
[TR]
[TD]Gifts-Empl (to be itemized)[/TD]
[/TR]
[TR]
[TD]Phone/Internet-Lodging[/TD]
[/TR]
[TR]
[TD]Meeting Room Rent/Audio/Visual[/TD]
[/TR]
[TR]
[TD]Dues/Memberships/Subscriptions[/TD]
[/TR]
[TR]
[TD]Registration/Seminars/Training[/TD]
[/TR]
[TR]
[TD]TradeShow Exp – Mtg Dept Only[/TD]
[/TR]
[TR]
[TD]Gifts-Customers[/TD]
[/TR]
[TR]
[TD]Late Fees Non-Reimbursable[/TD]
[/TR]
</tbody>[/TABLE]

 

[almst791]

Thank you man I have a lot of studying to do in VBA, this works perfect!

I also have a code that lists out a table of contents for my worksheet... Is there anyway I can combine the if statement to exclude the sheets above into this code. I've been trying but I am stumped with the IF in VBA.

Sub CreateLinksToAllSheets()


Dim sh As Worksheet
Dim cell As Range
For Each sh In ActiveWorkbook.Worksheets
If ActiveSheet.Name <> sh.Name Then
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
ActiveCell.Offset(1, 0).Select
End If
Next sh
End Sub

 

[Joe4]

Just replace this line here:

[COLOR=#333333]If ActiveSheet.Name <> sh.Name Then
[/COLOR]

with the other IF line I showed you (once you adjust the object name from "ws" to "sh" to reflect how you are looping through the sheet, i.e.

[COLOR=#333333]If (sh.Name <> "Data") And (sh.Name <> "Cost Center") And (sh.Name <> "Cost Center Macro") Then[/COLOR]