Copy values from a range and paste into next open row in a predefined named range via VBA

[Mirlynn]

I have a range of cells that get updated on a weekly basis by pulling data using SUMPRODUCT. I have defined a named range for this 'weekly summary' data aptly called "SourceData". What I need to be able to do is 'archive' these weekly counts by copying and pasting (I need to transpose and paste values only) to another area of the worksheet.

The following code works for what I want to do with one caveat:

Sub x()
With Sheets("Sheet1")
    .Range("SourceData").Copy
    Sheets("Sheet1").Range("N" & Rows.Count).End(xlUp)(2).PasteSpecial Transpose:=True
End With
End Sub

The caveat is that I am unable to find the next open row in a specific named range let alone copy and paste transpose values into that next blank row.

If/when I create a named range called "DestData", I am unclear how to

1. Determine the next open/blank row within that specific range
2. Copy and paste data (transpose values only) into that row once it has been defined

Thanks in advance for any and all assistance!!

 

[Norie]

Why do you want to use a named range?

Presumably the data you are archiving is constantly increasing so you would have to constantly redefine your named range.

Also, what if there isn't a blank row in the named range?

 

[Mirlynn]

My data is dynamic. In an ideal world I would like to be able to modify the size of the destination named range based on the number of rows I have in the source. That is more complex than I wanted to try to get started with but is fundamental in why I want to use the named range. The other reason is that I might actually have to rearrange the placement of the destination named range to somewhere else on the same worksheet or a different worksheet altogether. Again, using the named range would allow me to modify the named range and not have to muck with the VBA.

So, what I have done for now is to define the named range for destination data "DestData" in this example to contain more rows than I currently have data for. Again... extending this approach, I would ideally be able to dynamically define "DestData", insert a new row at the end of the named range, and insert the new data each week. Again... I was trying to take baby steps to reach my end goal!

The end result of all this is to essentially create a dynamic and modular reporting worksheet. Ultimately, I will use "DestData" to generate charts/graphs in a Scorecard for executive review.

I hope this provides better background for what I am doing now and where I am trying to go.

 

[Norie]

I really can't see the purpose of the named range when transferring the data.

You can easily find where to put data, insert data, move data without a named range.

I can see how it could be used for some charting/reporting, though probably only for a general overview.

 

[Mirlynn]

The biggest reason for wanting to use a named range for my target is simply for charting purposes. With updates being applied each week, the data range for the chart(s) need to be updated each week.

All that said, my question is not about WHY I am trying to solve this problem. My focus is on HOW to do it. If anyone has any feedback or thoughts on a solution, it would be GREATLY appreciated!!!

 

[Norie]

You can find the next empty row in a named range like this.

If Range("NamedRange").Cells(1,1).Value <>"" Then
    Set rngDst = Range("NamedRange").Cells(1, 1).Offset(Range("NamedRange").Rows.Count - 1).End(xlUp).Offset(1)
Else
    Set rngDst = Range("NamedRange").Cells(1,1)
End If

This is for a one-column range.