April 28, 2015
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
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.
November 8, 2013
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:
and press enter, to see the values for each parameter you use: ?Str
April 28, 2015
Most Users Ever Online: 57
Currently Online: lebenya monaheng
Currently Browsing this Page:
Frans Visser: 210
mey tithveasna: 71
Anders Sehlstedt: 47
Guest Posters: 1
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas