Should be a simple copy statement!

[dw_73]

I am having a torrid time trying to get this line of code to work when called from two different locations.
I've been through and checked that the inputs are identical from the two places, and that the "Data" page is unprotected in both cases, so I just don't understand why it is working for one and not the other.

On closer inspection it appears that the copy is actually working - I am left with values rather than formulas - but it is throwing a "Run-time error '1004': Application-defined or Object-defined error” and preventing the process from continuing for the user. If I debug and rem out the line after the error has occurred the values are there in the table.

I'm using this copy approach rather than going via the clipboard as I've been struggling with resource issues and I think this is the most efficient and quickest way to do the copy - but please make other suggestions if I'm wrong on this. Maybe I can just suppress the error message and force the process to continue?

Here's the code ...

Sheets("Data").Range(paste_range) = Sheets("Data").Range(paste_range).Value

Thanks for your help!

 

[dmt32]

Hi,
assuming paste_range is a named range then try

   With Sheets("Data").Range("paste_range")
        .Value = .Value
    End With

Dave

 

[dw_73]

Thanks Dave, I tried that but it's doing the same thing. paste_range is actually a string variable containing the range address as text (typically "$AJ$10:$BA$191794") so I dropped the quotes from your suggestion when implementing. I'm sure I should probably be using a "range" variable for this too.

'Note: row_count is passed in as a long numeric variable

Dim topcell As String
Dim bottomrightcell As String
Dim paste_range As String

topcell = Range("Data_Formulas_AbovePaste_Start").Offset(1, 0).Address()
bottomrightcell = Range("Data_Formulas_AbovePaste_Start").Offset(row_count, 17).Address()
paste_range = topcell & ":" & bottomrightcell

I originally had it constructing the paste range within the copy statement (Sheets("Data").Range(topcell & ":" & bottomrightcell).Value) and only put the paste_range variable in because it stopped working. That's the frustrating thing, it has worked ok some of the time.

Any recommendations? Could it be something to do with the size of the range being handled? Or that the dollar signs are in the reference?

Thanks, Darren