Looking for faster method for Copy & Paste
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 wrote: 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 -- Dave Peterson |
Looking for faster method for Copy & Paste
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 "Dave Peterson" wrote in message ... 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 wrote: 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 -- Dave Peterson |
Looking for faster method for Copy & Paste
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 |
Looking for faster method for Copy & Paste
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" wrote in message om... 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 |
Looking for faster method for Copy & Paste
"David Copp" wrote in message news:<1xPWc.207528$J06.140086@pd7tw2no...
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 |
All times are GMT +1. The time now is 07:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com