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.

 

[mattygriff]

Sorry Skip - what does that bit of code do? I assume it sets a dynamic range but I might well be wrong.....

 

[SkipVought]

Recalculates a Range, anytime a change is made on the sheet.

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

 

[mattygriff]

OK well it's probably not the most elegant solution but I've revised the OFFSET function which defines the dynamic range to add a line at the bottom.

=OFFSET(INDIRECT("'ECC Form 4'!$A$1"),0,0,COUNTA(Sheet2!$A:$A)[b]+1[/b],COUNTA(Sheet2!$1:$1))

That way, even when there's no data in the source sheet, the range will at least have a row of blanks so that the Pivot Table doesn't return an error.

I'm sure this will have some of you cringing but, hey, it works!!!