1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Copy Dynamic Named Range to the next available cell on another Worksheet

Discussion in 'Business Applications' started by Miss_Chilli, Mar 19, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. Miss_Chilli

    Miss_Chilli Thread Starter

    Joined:
    Mar 19, 2009
    Messages:
    2
    Hi

    I've been trying to find a solution to this and I've done some workarounds, but keep on failing on this.

    I have a database where data is dumped into the "Input" sheet (Sheet12) Cols A:U. A macro (copyform) then copies formulas down in columns V:AR to the last used row. (I need the data in a separate sheet due to a Ranking formula I'm using - each week number needs to be ranked seperately and I couldn't find a Rank IF formula =RANK(AH2,disputeamt,0)+COUNTIF($AH$2:AH2,AH2)-1) which makes each value unique.

    I have named the range A2:AR (row number dynamic) "Inputsheet" via a macro called "namerangeinput"

    I would now like to copy (pastespecial) this named range to the "Data" sheet (Sheet9) but to the next blank row.

    I would also like to clear the data in "Input" sheet (Sheet12) with the exception of the first row of formulas in V:AR as these are used to populate Input Sheet when new data is added.

    If there is a more efficient way of doing this (currently all different macros), then your help would be gratefully received! (I have until the end of this Fiscal to do this)

    Kind regards

    Diane
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,269
    Welcome to the board.

    "I have a database where data is dumped into the "Input" sheet (Sheet12) Cols A:U."

    OK. Can we assume since you later mention "the range A2:AR" that row 1 is headers?

    "A macro (copyform) then copies formulas down in columns V:AR to the last used row. (I need the data in a separate sheet due to a Ranking formula I'm using - each week number needs to be ranked seperately and I couldn't find a Rank IF formula =RANK(AH2,disputeamt,0)+COUNTIF($AH$2:AH2,AH2)-1) which makes each value unique."

    First sentence of that, the last used row based on what? (which column?) 2nd sentence ... I haven't a clue what your formulas do, so pass on that for now. But trying to connect the first sentence to something else later, namely:

    "I would also like to clear the data in "Input" sheet (Sheet12) with the exception of the first row of formulas in V:AR as these are used to populate Input Sheet when new data is added."

    Now, "the first row of formulas in V:AR"; does the first row actually mean row 1? Because that would work best, having the formulas in V1:AR1 permanently. Then you could:

    Sheet12rows = Sheets("Sheet12").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Sheet12").Range("V1:AR1").Copy _
    Sheets("Sheet12").Range("V2:AR" & Sheet12rows)

    Then we come to "I would now like to copy (pastespecial) this named range to the "Data" sheet (Sheet9) but to the next blank row."

    Paste special ... what? (values, I'd guess) "to "Data" ... next blank row.", no problem. So altogether, something like this:

    Sub test()
    Sheet12rows = Sheets("Sheet12").Range("A" & Rows.Count).End(xlUp).Row
    Sheet9rows = Sheets("Sheet9").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Sheet12").Range("V1:AR1").Copy _
    Sheets("Sheet12").Range("V2:AR" & Sheet12rows)
    Sheets("Sheet12").Range("V2:AR" & Sheet12rows).Value = _
    Sheets("Sheet12").Range("V2:AR" & Sheet12rows).Value
    Sheets("Sheet12").Range("A2:AR" & Sheet12rows).Copy _
    Sheets("Sheet9").Range("A" & Sheet9rows + 1)
    Sheets("Sheet12").Rows("2:" & Sheet12rows).Delete
    End Sub


    in the attached (Sheet12 being in "post dump" state). Run it a few times to see.
     

    Attached Files:

  3. Miss_Chilli

    Miss_Chilli Thread Starter

    Joined:
    Mar 19, 2009
    Messages:
    2
    Thanks, that works great!

    sorry for the delay in responding, been ill :-(
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/810747