PDA

View Full Version : [SOLVED] Two Dynamic Named Range Copy/Paste



SherryO
06-23-2005, 05:57 AM
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(WeeklyRaw Data!$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
06-23-2005, 02:55 PM
Have you looked at Edit>goto>special>Current Region?

SherryO
06-24-2005, 07:17 AM
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
07-13-2005, 11:08 AM
Can anyone out there help me? I really could use some help. Thanks!!!!!

Killian
07-13-2005, 12:27 PM
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
07-13-2005, 12:35 PM
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
07-13-2005, 12:51 PM
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
07-13-2005, 12:53 PM
Can anyone out there help me? I really could use some help. Thanks!!!!!


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

Any help?

SherryO
07-13-2005, 01:04 PM
No, xldown is not working for me. thanks for your response.

OBP
07-13-2005, 01:26 PM
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
07-21-2005, 11:18 AM
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
07-21-2005, 12:24 PM
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)