Copy & Paste Named Ranges

[mattygriff]

Thought it best to start a new thread for this one.

I'm importing data from one workbook (A) into another (B) using the INDIRECT() function. I have overcome the issue of INDIRECT() only working with open workbooks (thanks to everyone who assisted with that one) but now I have another issue.

Once the data has been imported, I don't want to have to open (B) in order to populate (A) every time (A) is opened so, to preserve the data originally imported from (B), I want to copy and paste the imported data in (A) as values.

The data is stored in cells which are in a named range (PREVIOUS_PSPD) and I'm not sure how (or indeed whether) I can refer to the range name in the copy / paste actions.

My current code is

   Dim WB As Workbook
   Set WB = Workbooks.Open(Prev_PSC_Filename)
   Range("Previous_PSPD").Copy
   Range("Previous_PSPD").PasteSpecial (xlPasteValues)
   WB.Close SaveChanges:=False
   Set WB = Nothing

When I run this code, I get a "Method 'Range' of object '_Global' failed" error message.

For info, the same range name is used (and refers to the same range of cells) in both (A) and (B).

Thanks in advance.

 

[RivetHed]

Hi,

You need to tell it which workbook the names are in. Try something more like this:

Dim WB As Workbook
Set WB = Workbooks.Open(Prev_PSC_Filename)
WB.Range("Previous_PSPD").Copy
ThisWorkbook.Range("Previous_PSPD").PasteSpecial xlPasteValues
WB.Close SaveChanges:=False
Set WB = Nothing

 

[mattygriff]

OK, tried your solution but it doesn't seem to work - I get an "Object doesn't support this property or method" error on the

WB.Range("Previous_PSPD").Copy

line.

However, using a Sheet reference - i.e.

Sheet4.Range("Previous_PSPD").Copy
Sheet4.Range("Previous_PSPD").PasteSpecial (xlPasteValues)

seems to work.

 

[RivetHed]

Ah, misunderstood your question, thought you were trying to copy a range with the same name from one WB to another and paste as values rather than just pasting an existing range as values within 1 WB.

 

[mattygriff]

Well my solution seems to work but, thinking about it, it might be cleaner if I simply copied the range from one workbook and pasted it as values in the other as you suggest.

The ranges have the same names and cover the same cells in both sheets.

Presumably, from your last comment, you thought your code would achieve this but it doesn't seem to work - any thoughts?

 

[mattygriff]

OK, to clarify what I now want to do (sorry for moving the goalposts so much!).....

I've got a dynamic range defined in one workbook (A) which I want to copy and paste into another workbook (B).

However, I assume that I can't define the same range in workbook (B) because the relevant sheet will be empty until the data from (A) is imported.

Is there some way of defining the same range in workbook (B) based on its dimensions in workbook (A)? Or is there some better way of getting the data from (A) to (B)?

As usual, thanks in advance.

 

[SkipVought]

Is this TABULAR data? Copy 'n' paste is never high on my list of available tools. I get TABLUAR data for other workbooks regularly, either using MS Query or ActiveX Data Objects. EVERY time my workbook "A" opens, it gets current data from workbook "B"

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[mattygriff]

Thanks Skip - a couple more questions.

The data is largely tabular although there may be some blank cells within the table - would MS Query handle this?

If it would, can you point me in the direction of a simple FAQ that would lead me by the hand through this minefield?

Thanks.

 

[SkipVought]

"...some blank cells within the table..."

Are you referring to EMPTY ROWS or COLUMNS? That's a NO, NO!

The only other caveat could be if your "blank cells" are interspersed in a column of NUMBERS, and the "blank cells" occur in the first 8 rows of your table.

Otherwise...

faq68-5829

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[mattygriff]

Skip,

I've had a quick go at using MS Query as per the FAQ but, for some reason, when I select the workbook which I want to get the data from, my dynamic range (defined using OFFSET) isn't listed as an option - any thoughts?

Also, in response to your last post, there could be blank cells within the first 8 rows of the table and they could also appear in columns of numbers - is this going to cause problems?

 

[mattygriff]

Something to add to my above post - my dynamic range is not listed when I try to use the Edit -> Goto command to find the cells that are included.

Is this normal for dynamic ranges?

 

[SkipVought]

OFFSET could be a problem, because it probably generates a range, based on calculated values.

Is your table not in a separate sheet with proper headings? You can access the sheet...

Select *
From [Sheet1$]

where Sheet1 is the sheet name.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[RivetHed]

Yup, it's normal for dynamic ranges not to be listed in the edit>>goto box. If you reference it through VBA or if you type the range name into the cell reference box at the top left they'll work though.

 

[mattygriff]

OK I'll try those options.

One other thing - is it possible to check the size of a dynamic range? I want to use one as the source data for a pivot table and need to check that there is actually some data there to use.

Cheers.

 

[RivetHed]

Try: Range("MyRange").Rows.Count to show you how many rows are in there.

 

[SkipVought]

if OFFSET is presenting a problem in a closed workbook, try using another approch in the WorksheetChange event...

application.displayalerts=false
target.currentregion.createnames true, false, false false
application.displayalerts=true

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[mattygriff]

RivetHed - my dynamic range is called EARLY_WARNINGS but when I use

Set numRows = Range("EARLY_WARNINGS").Rows.Count

I get an "Object required" error.

Any thoughts?

 

[PHV]

Don't use the Set instruction:
numRows = Range("EARLY_WARNINGS").Rows.Count

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mattygriff]

Thanks PHV - got that working now.

However, this creates another problem. In some instances, the source data will only consist of the header row in which case I don't want the Pivot Table to refresh.

Is there some way of resetting the table instead (i.e. setting all the values to zero)?

 

[SkipVought]

Oops. Just realized that you are not just naming columns, but a multiple row/column range...

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target.Parent
      ActiveWorkbook.Names.Add _
        Name:="MyRange", _
        RefersTo:="='" & .Name & "'!" & .[A1].CurrentRegion.Address(True, True)
    End With
End Sub

assuming that your table begins in A1.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]