Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

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


(!)

Miss_Chilli's Avatar
Miss_Chilli Miss_Chilli is offline
Junior Member with 2 posts.
THREAD STARTER
 
Join Date: Mar 2009
Experience: Beginner
19-Mar-2009, 08:55 AM #1
Copy Dynamic Named Range to the next available cell on another Worksheet
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
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
19-Mar-2009, 12:30 PM #2
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
File Type: xls diane.xls (27.0 KB, 243 views)
Miss_Chilli's Avatar
Miss_Chilli Miss_Chilli is offline
Junior Member with 2 posts.
THREAD STARTER
 
Join Date: Mar 2009
Experience: Beginner
24-Mar-2009, 09:02 AM #3
Thanks, that works great!

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

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
excel 03, vba

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑

Content Relevant URLs by vBSEO 3.3.2