VBA Macro Help - Copy a single cell and paste to a single cell in all sheets titled "####"

Hello,

I'm trying to find a more optimal way to code the following:

1. Select a single cell on the first tab entitled "Landing Page"
2. Copy this cell
3. Paste this value to the same cell across all tabs that have a title format as four digit number. (e.g. "6301"; Some tabs in the workbook have a different title format such as "Division 6000"
4. Repeat steps for another cell on the first tab entitled "Landing Page"

A couple of things; I cannot use simple cell referencing in this task because I'm interacting with an Excel Add-in that will wipe out the =sheet1cell23, etc. I think using a Macro is my only option. Also, I recorded some code below that kind of accomplishes the task, but it's only for 3 departments. (I have over 70 departments, and the code would be a mile long and difficult to record and maintain. Also, I may need to add new department numbers in the future, so if I can select an all encompassing format vs an actual number it would be ideal)

The system recorded code is pasted below.

Thanks for your help!

Begin Paste:

Sub CopyValuesFromLandingPage()

'
' CopyValuesFromLandingPage Macro
'
'
Sheets("Landing Page").Select
Range("B5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("6301").Select
Range("G12").Select
ActiveSheet.Paste
Sheets("6302").Select
Range("G12").Select
ActiveSheet.Paste
Sheets("6304").Select
Range("G12").Select
ActiveSheet.Paste


Sheets("Landing Page").Select
Range("C5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("6301").Select
Range("H12").Select
ActiveSheet.Paste
Sheets("6302").Select
Range("H12").Select
ActiveSheet.Paste
Sheets("6304").Select
Range("H12").Select
ActiveSheet.Paste


Sheets("Landing Page").Select
Range("D5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("6301").Select
Range("I12").Select
ActiveSheet.Paste
Sheets("6302").Select
Range("I12").Select
ActiveSheet.Paste
Sheets("6304").Select
Range("I12").Select
ActiveSheet.Paste


End Sub

Try this:

Please Login or Register  to view this content.

Thanks for your help. One question; Where do I insert B5 (the copy cell from the landing page) and then G12? (the cell that equals the paste destination(s) on multiple worksheets)

I really appreciate your help.

Please Login or Register  to view this content.