+ Reply to Thread
Results 1 to 4 of 4

Trying to copy and paste macro that reads paste location from a cell.

  1. #1
    Registered User
    Join Date
    11-08-2022
    Location
    Clarion, PA
    MS-Off Ver
    365 for Business
    Posts
    21

    Trying to copy and paste macro that reads paste location from a cell.

    Let me explain. I am copying a list from Sheet Models. I need to paste it in Sheet Past.
    However, each month will go to the next column. I have a formula that reads what column that will be on the page. It basically looks for the next empty cell in a certain row.

    I have a script started but i won't work. I don't know how to get the paste to acknowledge the value from the cell.

    This is the script i have...(targetCell.select is where it breaks down.

    Sub Macro2()
    '
    ' Macro2 Macro
    '
    Sheets("PAST").Select
    Range("C1").Select
    Sheets("MODELS").Select
    Range("D6:D489").Select
    Selection.Copy
    Sheets("PAST").Select
    Dim cellValue As Range
    cellAddress = "C1"
    Set targetCell = Range(cellAddress)
    targetCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks
    :=False, Transpose:=False
    End Sub

    I not very good at this so please speak slowly.
    If you have an easier method please let me know

  2. #2
    Valued Forum Contributor
    Join Date
    08-31-2007
    Location
    SW Ireland
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2311 Build 16.0.17029.20068) 32-bit
    Posts
    540

    Re: Trying to copy and paste macro that reads paste location from a cell.

    The following isn't dynamically finding where to paste the content from MODELS but it might be a start- you'd need to find the dynamic equivalent of C1 as the intended destination:

    Please Login or Register  to view this content.
    Excel 365 user. To unblock a downloaded macro-enabled workbook, go to your "Downloads" folder > right click on the workbook name > click 'Properties' > check the 'Unblock' checkbox. You can now open the workbook.

  3. #3
    Registered User
    Join Date
    11-08-2022
    Location
    Clarion, PA
    MS-Off Ver
    365 for Business
    Posts
    21

    Re: Trying to copy and paste macro that reads paste location from a cell.

    The value in C1 has the location. As the location changes so does the value in C1. This is the reason I was doing it this way.
    In the above script would the macro just paste the contents into C1?

  4. #4
    Registered User
    Join Date
    11-08-2022
    Location
    Clarion, PA
    MS-Off Ver
    365 for Business
    Posts
    21

    Re: Trying to copy and paste macro that reads paste location from a cell.

    Sub Macro1()

    Dim sourceRange As Range
    Dim destinationRange As Range
    Sheets("models").Select
    Set sourceRange = Range("i1:i66")
    Sheets("past").Select
    Set destinationRange = Range(Range("C1").Value)
    sourceRange.Copy
    destinationRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
    End Sub

    Found the answer, thanks to those who helped

+ 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. Macro to read a cell for the location to goto, copy, and then paste elsewhere
    By Kfrayne in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-30-2017, 03:45 PM
  2. [SOLVED] Copy Paste Macro Relative to Button Location
    By Lacaycer in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-21-2017, 01:21 PM
  3. Copy and paste between two work books paste into unspecified location
    By jessica88 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-22-2014, 07:28 AM
  4. Replies: 3
    Last Post: 08-15-2013, 01:22 PM
  5. VBA Copy-paste values with changing paste location
    By booost in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-16-2012, 10:39 AM
  6. Seach, Copy, select paste location, paste using macros
    By helpdave in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-16-2010, 11:36 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