Two Dynamic Named Range Copy/Paste

[SherryO]

I have two spreadsheets, each with a dynamic named range using the formula below. Both of these ranges are around 15,000 rows and 40 columns, around, I never know exactly.

=OFFSET(WeeklyRawData!$A$1,0,0,COUNTA(WeeklyRawData!$A:$A),COUNTA(WeeklyRawData!$1:$1))

What I need to do and don't know how to, is write a macro that will copy each of these two ranges into one spreadsheet, which is the source data for many pivot tables. I don't know how to copy the second range, when I don't know how many rows the first range will have. Basically, I'm stumped. Any help would be appreciated. Thanks!! SherryO

[OBP]

Have you looked at Edit>goto>special>Current Region?

[SherryO]

That won't let me select the named range and unfortunately the named range is picking up my formulas, even with paste special, values.

[SherryO]

Can anyone out there help me? I really could use some help. Thanks!!!!!

[Killian]

Hi Sherry

I just did a little test which seems to work

ActiveWorkbook.Worksheets(1).Range("A1").CurrentRegion.Columns(3).Copy
    Workbooks(2).Sheets(1).Range("A1").PasteSpecial xlPasteValues

My activeworkbook had 3 columns of data starting in A1, the third column was a simple formula (=A1*B1, etc).
Using CurrentRegion on A1 expanded the range to include all the contiguous cells, of which I copied the third column (with the formulae).
Paste into target workbook with: PasteSpecial, xlPasteValues worked fine for me.
I'm not sure why that wouldn't work for you so a good approach might be to do this test then modify as required to get the right ranges and destination

[SherryO]

I don't know why the paste special values isn't working, but if I do an xldown, then it goes to the where the formulas end even though the cell is apparently blank. I may have 2000 rows where there were formulas, no they should be blank, but they aren't. I guess I will have to work that out, but how do you find the end of the range, to copy the next range directly after with no blanks in between, assuming the paste special values works? Thanks for your response. I was starting to get despondent. :>

[OBP]

Does the xldown do what you want or not?
If not then you can use a for/next loop to scan the column looking for "".

[Bob Phillips]

Can anyone out there help me? I really could use some help. Thanks!!!!!

range("myRange").columns.count
range("myRange").rows.count

Any help?

[SherryO]

No, xldown is not working for me. thanks for your response.

[OBP]

Ok, are you looking for the last entry in a particular Column or all of the columns in the data range?
You can use this to search column a.

Dim x as integer
range("a1").select
for x= 1 to 20000
if activecell = "" then exit for
activecell.offset(1,0).select
next x

[SherryO]

This is the code I am using:

Sub test5()
Dim putithere As Range
Dim range1 As Range
Dim range2 As Range
Dim startrow As Long
'area where I want it dumped
Set putithere = ActiveSheet.Range("J1")
'First named range, include the column header row
Set range1 = Range(ActiveWorkbook.Names("dam"))
'Second named range, this range should not include the column header row
Set range2 = Range(ActiveWorkbook.Names("dam2"))
range1.Copy
putithere.PasteSpecial xlPasteValues
startrow = range1.Rows.Count
range2.Copy
putithere.Offset(startrow).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

My workbook is set up as follows:
cell a1 is a header
range a2:a15 alphabet entered it
range a16:a37 formula that basically says if cell above you is not k, which it is not, then blank, which they are.
cell f1 is a header
range f2:f20 more alphabet entered

When I run the macro excel does not see the cells with formula in them as blank. Why is xlpastevalues not working. This is driving me bonkers. Please help!!!

[BDavidson]

Does this help you out?

Dim putithere As Range
Dim range1 As Range
Dim range2 As Range
'area where I want it dumped
Set putithere = ActiveSheet.Range("J1")
'First named range, include the column header row
Set range1 = Range("dam")
'Second named range, this range should not include the column header row
Set range2 = Range("dam2")
range1.Copy
putithere.PasteSpecial xlPasteValues
Set putithere = putithere.Offset(range1.Rows.Count)
range2.Copy
putithere.PasteSpecial xlPasteValues
Application.CutCopyMode = False

One question - your note for "dam2" specifies "should not include the column header row". Does the named range include the header and you don't know how to get rid of it? If yes, use:

Set range2 = Range("dam2").Offset(1).Resize(Range("dam2").Rows.Count - 1)