+ Reply to Thread
Results 1 to 20 of 20

Macro - Dynamic Copy Range

Hybrid View

Lacaycer Macro - Dynamic Copy Range 09-14-2012, 08:35 AM
Jakobshavn Re: Macro - Dynamic Copy Range 09-14-2012, 08:45 AM
Lacaycer Re: Macro - Dynamic Copy Range 09-17-2012, 02:24 PM
Jakobshavn Re: Macro - Dynamic Copy Range 09-17-2012, 02:39 PM
jaslake Re: Macro - Dynamic Copy Range 09-17-2012, 02:50 PM
Lacaycer Re: Macro - Dynamic Copy Range 09-19-2012, 01:24 PM
jaslake Re: Macro - Dynamic Copy Range 09-19-2012, 08:04 PM
Lacaycer Re: Macro - Dynamic Copy Range 09-20-2012, 08:36 AM
Lacaycer Re: Macro - Dynamic Copy Range 09-21-2012, 12:01 PM
jaslake Re: Macro - Dynamic Copy Range 09-27-2012, 11:57 AM
Lacaycer Re: Macro - Dynamic Copy Range 09-27-2012, 01:11 PM
jaslake Re: Macro - Dynamic Copy Range 09-27-2012, 04:03 PM
  1. #1
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Macro - Dynamic Copy Range

    How about:

    Sub dural()
    For i = 1 To 50
        v = Cells(i, "T").Text
        If v = "" Or v = "#NUM!" Then Exit For
    Next
    Range("T1:T" & i - 1).Copy
    End Sub
    Gary's Student

  2. #2
    Registered User
    Join Date
    03-13-2012
    Location
    Burlington Ontario
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: Macro - Dynamic Copy Range

    I had to add a column to the copy function

    so I modified the macro to:
    Sub Update()
     For i = 1 To Rows.Count
         v = Cells(i, "R").Text
         If v = "" Or v = "#NUM!" Then Exit For
     Next
        Range("R2:S" & i - 1).Copy
    and it worked, the first time. But after saving ans exiting it said Method 'Range of object global fail. Also what needs to be changed to run this macro from another sheet?
    Last edited by Cutter; 09-18-2012 at 11:41 AM. Reason: Added code tags

  3. #3
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Macro - Dynamic Copy Range

    If the For loop exited immediately, then i would equal 1, and i-1 would equal 0, and:
    Range("R2:S" & i - 1).Copy would fail.

    Say you want the macro to run on a sheet called "Special" not the sheet you happen to have active:

    Sub dural()
    set MYSHEET=ActiveSheet
    Sheets("Special").Activate
    For i = 1 To 50
        v = Cells(i, "T").Text
        If v = "" Or v = "#NUM!" Then Exit For
    Next
    Range("T1:T" & i - 1).Copy
    MYSHEET.Activate
    End Sub

  4. #4
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Macro - Dynamic Copy Range

    Hi Lacaycer

    Please wrap your code in Code Tags.
    Have you considered using AutoFilter for this exercise?
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  5. #5
    Registered User
    Join Date
    03-13-2012
    Location
    Burlington Ontario
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: Macro - Dynamic Copy Range

    Example.xlsx

    I tried entering the macro and was unable to make it work more than once. It was either returning the range global error, or pasting the column title. So I figured if you could see what I was trying to do or how it is actually applied, as well as explaining what I'm trying to achieve I might have better luck in solving this problem

  6. #6
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Macro - Dynamic Copy Range

    Hi Lacaycer

    Far as I can tell the code in the attached does this:
    • Copy the names in $Q$2:$Q$49 that are not present in $F$2:$F$49
    • Past names into the next empty cells in Column "F"
    • Also transpose names into next empty cells in row of "Master Matrix" sheet
    • But if there are no names that are different between "Q" and "F"….Do nothing.

    It does not do this as yet:
    • then sort both named ranges "StatusList" & "MasterList"

    Are we on track with what we have?
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-13-2012
    Location
    Burlington Ontario
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: Macro - Dynamic Copy Range

    It works, I'm sure that's no surprise to you. Thanks alot. I probably could have spent another 8 hours just trying to figure this out.

  8. #8
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Macro - Dynamic Copy Range

    Hi Lacaycer
    You're welcome.
    Will you be doing the Sort Code? If not please explain how you wish each Named Range ("StatusList" & "MasterList") sorted.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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