Dear all,
when I need to copy and paste some range I tend to use the resize method, because it does not involve the clipboard at all. The code below is part of larger macro and worked well for some time. For given set of worksheets values from range rSource are copied to range rTarget, which is resized to match the size of rSource. But now it does not work for one of the sheets, macro stops at line starting rTarget.Resize ... and error message pops up indication Run-time error '1004'.
For I = 1 To iDim
With ThisWorkbook.Worksheets(sNewOldSheets(I, 2))
'we need to define whole used area starting in cell A1
Str = ThisWorkbook.Worksheets(sNewOldSheets(I, 1)).UsedRange.Address
Str = "A1:" & Right(Str, Len(Str) - InStr(1, Str, ":"))
Set rSource = ThisWorkbook.Worksheets(sNewOldSheets(I, 1)).Range(Str)
Set rTarget = .Range("A1")
rTarget.Resize(rSource.Rows.Count, rSource.Columns.Count).Value = rSource.Value
End With
Next I
Any experience with using Resize method this way? I'm wondering whether the size of range rSource matters, because the error appears for range with more than 900 rows and 240 columns. If I restrict the number of rows to e.g. 700, than it works. I'm using Excel 2013. Thanks for hints.
Cheers
Daniel
Hi Daniel,
There should be no other limits than sheet rows and columns limits.
When the code breaks, try to find if that specific sheet that fails has merged cells, or hidden rows, or even if the sheet is visible or protected.
Print into the immediate window the parameters, like:
?rSource.Rows.Count, rSource.Columns.Count
and press enter, to see the values for each parameter you use: ?Str
Hi Catalin,
thanks for your tips. It made me to check everything and the reason was easier than expected. Due to wrong links some cells formatted as dates were linked to negative values. That's why these values were impossible to copy
. Sorry for bothering you.
Cheers
Daniel
