Looking for faster method for Copy & Paste

[David Copp]

Hi,

I have a sheet with references to workbooks, sheets, cell addresses and
values whose values need to be copied to the aforementioned workbook, sheet
and address reference.

Ref_sheet looks something like the following...
workbook worksheet address value
1.xls Sheet1 $A$1 Hello
1.xls Sheet1 $A$2 World
2.xls Sheet1 $J$10 10
2.xls Sheet2 $Z$100 32,000

Current method loops through above rows and copies and pastes each value to
location (i.e. copy "Hello" to book "1.xls", sheet "sheet1" to range
"$A$1").
(Needs to be copy & paste to preserve formatting)

For i = row_min To row_max
ref_sheet.Cells(i, 4).Copy
Destination:=Workbooks(CStr(ref_sheet.Cells(i,
1))).Worksheets(CStr(ref_sheet.Cells(i, 2))).Range(ref_sheet.Cells(i, 3))
Next i

where ref_sheet references above sheet... works well but I think it's a
little slow; averaging about 588 records per second (10,000 records taking
about 17 seconds) on a P4 Celeron 2.4 in Excel 97.
Since any of workbook, sheet and address could change row by row, IMHO, need
to evaluate each book, sheet and address reference. (yuck!)

Any suggestions gratefully appreciated.

Thanks in advance,

David Copp
(e-mail address removed)

 

[Dave Peterson]

Untested, but maybe you could just plop the value in (instead of copy|pasting):

For i = row_min To row_max
Workbooks(CStr(ref_sheet.Cells(i,1))) _
.Worksheets(CStr(ref_sheet.Cells(i, 2))) _
.Range(ref_sheet.Cells(i, 3)).value = ref_sheet.Cells(i, 4).value
Next i

or even:
with ref_sheet
For i = row_min To row_max
Workbooks(.Cells(i,1).value) _
.Worksheets(.Cells(i, 2).value) _
.Range(.Cells(i, 3).value).value = .Cells(i, 4).value
Next i
end with

(why the cstr()'s)

(Watchout for typo's. I composed in the email window.)

 

[David Copp]

Many thanks but need to retain all characteristics of source cell including
formats so believe need to use copy & paste method. Think there's a line in
my original post just above loop saying something like this. Any other
thoughts?

Dave

 

[jmac]

First do a timing test to see just how much time is used in
maintaining the formatting. If it's a large enough percentage of the
overall time, consider pre-formatting your destination sheet.
To reduce the overall time of the process, precede the data collection
loop with Application.ScreenUpdating = False
Then after all the collection and pasting of data turn on
ScreenUpdating.
You could time the process with and without ScreenUpdating turned on
and with and without maintaining the cell formatting, just to identify
the time-intensive portion of your macro.

jmac

 

[David Copp]

Hi,

Could you clarify "pre-format the destination sheet?"

Each cell will have it's own formatting.... so...

workbook worksheet address value
1.xls Sheet1 $A$1 Hello
1.xls Sheet1 $A$2 World
2.xls Sheet1 $J$10 10
2.xls Sheet2 $Z$100 32,000

"Hello" may be right justified red text on green background.
"World" may be left justified, bold text and underlined.
"10" might have a comment within the cell above.
etc

I will try stripping the formats in the value column to see clock
consumption.

Thanks for your reply.


Dave

 

[jmac]

Hi,

Could you clarify "pre-format the destination sheet?"

Each cell will have it's own formatting.... so...

workbook worksheet address value
1.xls Sheet1 $A$1 Hello
1.xls Sheet1 $A$2 World
2.xls Sheet1 $J$10 10
2.xls Sheet2 $Z$100 32,000

"Hello" may be right justified red text on green background.
"World" may be left justified, bold text and underlined.
"10" might have a comment within the cell above.
etc

In the case of your sample, format the corresponding cells in the
destination sheet the same way as the source cells either before or
after the data transfer. If the destination sheet will always have
the same formatting, you might consider using a template as the source
of your new destination sheet each time you perform the operation. If
the final program will not have static formatting, as I'm assuming the
sample does, then you have more of a challenge. I think turning off
the sheet updating will provide the time difference you're after.

I hope this helps.

jmac