+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    04-03-2014
    Location
    Chicago, US
    MS-Off Ver
    Excel 2010
    Posts
    2

    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

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

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

    Try this:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-03-2014
    Location
    Chicago, US
    MS-Off Ver
    Excel 2010
    Posts
    2

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

    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.

  4. #4
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

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

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 11-04-2013, 11:29 AM
  2. [SOLVED] Copy From a "Object" to a single cell
    By fblaze in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-06-2013, 09:14 AM
  3. [SOLVED] Count "A" and "V" as separate characters in a single cell
    By sportboy712 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-04-2013, 12:26 PM
  4. Copy+Paste Special Single Cell on all sheets
    By eg1 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-23-2011, 01:30 PM
  5. [SOLVED] Copy column range of "single word" cells with spaces to a single c
    By nastech in forum Excel General
    Replies: 3
    Last Post: 02-15-2006, 01:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1