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.

Non-Reimbursable

Airfare/Baggage Fees

Fuel/Taxi/Other Transport

Lunch-Employee only

Parking/Tolls

Mileage Reimburse

Dinner-Employee only

Miscellaneous

Breakfast-Employee only

Rental Car

Lodging

Business Meals w/MCK Staff

Entertainment Events

Hotel (to be itemized)

Meal Exception – Employee Only

Business Meals w/Non MCK Staff

Phone/Internet-Airline/Airport

Gifts-Empl (to be itemized)

Phone/Internet-Lodging

Meeting Room Rent/Audio/Visual

Dues/Memberships/Subscriptions

Registration/Seminars/Training

TradeShow Exp – Mtg Dept Only

Gifts-Customers

Late Fees Non-Reimbursable

<colgroup><col></colgroup><tbody>
</tbody>

Non-Reimbursable

Airfare/Baggage Fees

Fuel/Taxi/Other Transport

Lunch-Employee only

Parking/Tolls

Mileage Reimburse

Dinner-Employee only

Miscellaneous

Breakfast-Employee only

Rental Car

Lodging

Business Meals w/MCK Staff

Entertainment Events

Hotel (to be itemized)

Meal Exception – Employee Only

Business Meals w/Non MCK Staff

Phone/Internet-Airline/Airport

Gifts-Empl (to be itemized)

Phone/Internet-Lodging

Meeting Room Rent/Audio/Visual

Dues/Memberships/Subscriptions

Registration/Seminars/Training

TradeShow Exp – Mtg Dept Only

Gifts-Customers

Late Fees Non-Reimbursable

<colgroup><col></colgroup><tbody>
</tbody>

 

[footoo]

I suggest you put a list of the text in a separate sheet (let's say named "Text Sheet" in cells A1 to A15), then:

Dim ws As Worksheet
For Each ws In Worksheets
    Sheets("Text List").[A1:A15].Copy ws.[D68]
Next
Sheets("Text List").[D:D].Delete

 

[almst791]

Is there anyway I can exclude certain sheets from this happening to? I have three data sheets that have info in those ranges and I dont want it to mess it up. Or if I have a list of tab names that this will only occur in those tabs?

 

[Joe4]

Yes, you can do that.

One way is like this to add an IF statement after your For each statement, checking the ws names. To exclude those three, it would look something like:

If (ws.Name<>"Sheet1") And (ws.Name<>"Sheet2") And (ws.Name<>"Sheet3") Then
... rest of your code here
End If

(note: replace "Sheet1", "Sheet2", and "Sheet3" with the names of the sheet you want to exclude).

 

[almst791]

Thanks man, sorry about the dup.

 

[Joe4]

You are welcome.

In this sort of situation, you can go either way. Since it is a continuation follow-up, you could keep posting in the same thread. But since it is also a new question, you could post it to a new thread.
But you just need to pick one or the other (and not do both), as we do not want two separate discussions on the same question.

 

[almst791]

No more dups from me. Okay this is what I've written and I am still getting this macro to run in the 3 sheets I dont want it to.

Sub Macro3()
Dim ws As Worksheet
For Each ws In Worksheets
Sheets("Cost Center Macro").[B2:B26].Copy ws.[D42]
Next
Sheets("Cost Center").[D:D].Delete

If (ws.Name <> "Data") And (ws.Name <> "Cost Center") And (ws.Name <> "Cost Center Macro") Then


End If
End Sub

 

[Joe4]

Why do you have your copy row twice, once above the IF statement and once below?
The one above it should not be there.

 

[almst791]

I edited my previous comment, sorry about that.

 

[Joe4]

No, it needs to be directly after the For each line:

[COLOR=#333333]Sub Macro3()
[/COLOR]
[COLOR=#333333]Dim ws As Worksheet[/COLOR]
[COLOR=#333333]For Each ws In Worksheets
[/COLOR]    [COLOR=#333333]If (ws.Name <> "Data") And (ws.Name <> "Cost Center") And (ws.Name <> "Cost Center Macro") Then[/COLOR]
[COLOR=#333333]        Sheets("Cost Center Macro").[B2:B26].Copy ws.[D42]
[/COLOR]    End if
[COLOR=#333333]Next[/COLOR]
[COLOR=#333333]
Sheets("Cost Center").[D:D].Delete[/COLOR]

[COLOR=#333333]End Sub[/COLOR]