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

almst791

Board Regular
Joined
Jun 29, 2016
Messages
82
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]
 
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
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Just replace this line here:
Code:
[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.
Code:
[COLOR=#333333]If (sh.Name <> "Data") And (sh.Name <> "Cost Center") And (sh.Name <> "Cost Center Macro") Then[/COLOR]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top