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
Bookmarks