Active Member
Power Pivot
Power BI
April 28, 2015
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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
Answers Post
1 Guest(s)