mperrah
11-15-2007, 01:49 AM
I am trying to speed up a copy and paste process.
currently my sub scans column A for the letter "a"
if present the sub copies some of the values in the row to another sheet.
Would the process go faster if I load the used cells to an array,
and use coordinates to transfer the data? still scanning for the "a" in the first column...
I usually only use 20 rows or so down nd the columns are always 50 accross. I use a worksheet_change and selection change sub on this data, and I think my sub to copy data might be slowing down from selection change. If I can disable a worksheet sub and re-eneable after the transfer is complete may help (don't know how to do this)
this is the sub to transfer the data. Each entry transfer takes over a minute. Screen at bottom says calculating repeatedly...
not sure what is being calculated either?
For Each Cell In sh_source.Range("C3:C" & _
sh_source.Range("C" & Rows.Count).End(xlUp).Row)
If Cell.Offset(, -2).Value = "a" Then
With sh_source
.Range("C" & Cell.Row).Copy
sh_dest.Range("C" & sh_dest.Range("C" & Rows.Count).End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("J" & Cell.Row).Copy sh_dest.Range("B" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AU" & Cell.Row).Copy
sh_dest.Range("D" & sh_dest.Range("C" & Rows.Count).End(xlUp).Row).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("O" & Cell.Row).Copy sh_dest.Range("E" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("E" & Cell.Row).Copy sh_dest.Range("F" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("P" & Cell.Row).Copy sh_dest.Range("G" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("Q" & Cell.Row).Copy sh_dest.Range("H" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("R" & Cell.Row).Copy sh_dest.Range("I" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("S" & Cell.Row).Copy sh_dest.Range("J" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("T" & Cell.Row).Copy sh_dest.Range("K" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("U" & Cell.Row).Copy sh_dest.Range("L" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("V" & Cell.Row).Copy sh_dest.Range("M" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("W" & Cell.Row).Copy sh_dest.Range("N" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("X" & Cell.Row).Copy sh_dest.Range("O" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("Y" & Cell.Row).Copy sh_dest.Range("P" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("Z" & Cell.Row).Copy sh_dest.Range("Q" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AA" & Cell.Row).Copy sh_dest.Range("R" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AB" & Cell.Row).Copy sh_dest.Range("S" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AC" & Cell.Row).Copy sh_dest.Range("T" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AD" & Cell.Row).Copy sh_dest.Range("U" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AE" & Cell.Row).Copy sh_dest.Range("V" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AF" & Cell.Row).Copy sh_dest.Range("W" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AG" & Cell.Row).Copy sh_dest.Range("X" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AH" & Cell.Row).Copy sh_dest.Range("Y" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AI" & Cell.Row).Copy sh_dest.Range("Z" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AJ" & Cell.Row).Copy sh_dest.Range("AA" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AK" & Cell.Row).Copy sh_dest.Range("AB" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AL" & Cell.Row).Copy sh_dest.Range("AC" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AM" & Cell.Row).Copy sh_dest.Range("AD" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AN" & Cell.Row).Copy sh_dest.Range("AE" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AO" & Cell.Row).Copy sh_dest.Range("AF" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AP" & Cell.Row).Copy sh_dest.Range("AG" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AQ" & Cell.Row).Copy sh_dest.Range("AH" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AR" & Cell.Row).Copy sh_dest.Range("AI" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AS" & Cell.Row).Copy sh_dest.Range("AJ" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AT" & Cell.Row).Copy sh_dest.Range("AK" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
End With
End If
Next Cell
I was thinking something like this
sub arrayCopy()
Dim arrayData() as array ' is this right?
dim i as long
dim lastRowD as long
dim lastRowQ as long
dim trgtSht as worksheet
set trgtSht = workheets("QCDetail")
set lastRowQ = trgtSht.range("B" & Rows.Count).End
set lastRow = worksheets("Data").range("C" & Rows.Count).End(xlUp).Row)
redim arrayData(1 to 50) ' as 50 columns accross?
for i = 3 to lastRowD ' data starts in 3rd row down
if i.offset(0, -3) = "a" then ' check column A for "a"
arrayData(10, i).value = trgtSht.range("B" & lastRowQ) 'first value copy to QCDetail
' duplicate process to accomidate each copy item
end if
next i
end sub
not tested, but am I on the right track?
Would this speed up the above code or is there another way to pick the source data and target location that works more quickly?
I though about formating the data to have the same column locations on both pages. The problem is I use all of the data to print a form and make an archive copy of the form as an added worksheet.
The QCDetail sheet only uses some of the data.
I do have a copy of the full data and a sheet called "Raw"
I could use match and index to make the other forms, or 2 sheets for data.
1 to setup for archive and print, and the other for QCDetail.
My goal is to speed up the process...
I already uploaded the file in a different thread, I'll link it here.
Mark
currently my sub scans column A for the letter "a"
if present the sub copies some of the values in the row to another sheet.
Would the process go faster if I load the used cells to an array,
and use coordinates to transfer the data? still scanning for the "a" in the first column...
I usually only use 20 rows or so down nd the columns are always 50 accross. I use a worksheet_change and selection change sub on this data, and I think my sub to copy data might be slowing down from selection change. If I can disable a worksheet sub and re-eneable after the transfer is complete may help (don't know how to do this)
this is the sub to transfer the data. Each entry transfer takes over a minute. Screen at bottom says calculating repeatedly...
not sure what is being calculated either?
For Each Cell In sh_source.Range("C3:C" & _
sh_source.Range("C" & Rows.Count).End(xlUp).Row)
If Cell.Offset(, -2).Value = "a" Then
With sh_source
.Range("C" & Cell.Row).Copy
sh_dest.Range("C" & sh_dest.Range("C" & Rows.Count).End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("J" & Cell.Row).Copy sh_dest.Range("B" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AU" & Cell.Row).Copy
sh_dest.Range("D" & sh_dest.Range("C" & Rows.Count).End(xlUp).Row).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("O" & Cell.Row).Copy sh_dest.Range("E" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("E" & Cell.Row).Copy sh_dest.Range("F" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("P" & Cell.Row).Copy sh_dest.Range("G" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("Q" & Cell.Row).Copy sh_dest.Range("H" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("R" & Cell.Row).Copy sh_dest.Range("I" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("S" & Cell.Row).Copy sh_dest.Range("J" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("T" & Cell.Row).Copy sh_dest.Range("K" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("U" & Cell.Row).Copy sh_dest.Range("L" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("V" & Cell.Row).Copy sh_dest.Range("M" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("W" & Cell.Row).Copy sh_dest.Range("N" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("X" & Cell.Row).Copy sh_dest.Range("O" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("Y" & Cell.Row).Copy sh_dest.Range("P" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("Z" & Cell.Row).Copy sh_dest.Range("Q" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AA" & Cell.Row).Copy sh_dest.Range("R" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AB" & Cell.Row).Copy sh_dest.Range("S" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AC" & Cell.Row).Copy sh_dest.Range("T" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AD" & Cell.Row).Copy sh_dest.Range("U" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AE" & Cell.Row).Copy sh_dest.Range("V" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AF" & Cell.Row).Copy sh_dest.Range("W" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AG" & Cell.Row).Copy sh_dest.Range("X" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AH" & Cell.Row).Copy sh_dest.Range("Y" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AI" & Cell.Row).Copy sh_dest.Range("Z" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AJ" & Cell.Row).Copy sh_dest.Range("AA" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AK" & Cell.Row).Copy sh_dest.Range("AB" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AL" & Cell.Row).Copy sh_dest.Range("AC" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AM" & Cell.Row).Copy sh_dest.Range("AD" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AN" & Cell.Row).Copy sh_dest.Range("AE" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AO" & Cell.Row).Copy sh_dest.Range("AF" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AP" & Cell.Row).Copy sh_dest.Range("AG" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AQ" & Cell.Row).Copy sh_dest.Range("AH" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AR" & Cell.Row).Copy sh_dest.Range("AI" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AS" & Cell.Row).Copy sh_dest.Range("AJ" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
.Range("AT" & Cell.Row).Copy sh_dest.Range("AK" _
& sh_dest.Range("C" & Rows.Count).End(xlUp).Row)
End With
End If
Next Cell
I was thinking something like this
sub arrayCopy()
Dim arrayData() as array ' is this right?
dim i as long
dim lastRowD as long
dim lastRowQ as long
dim trgtSht as worksheet
set trgtSht = workheets("QCDetail")
set lastRowQ = trgtSht.range("B" & Rows.Count).End
set lastRow = worksheets("Data").range("C" & Rows.Count).End(xlUp).Row)
redim arrayData(1 to 50) ' as 50 columns accross?
for i = 3 to lastRowD ' data starts in 3rd row down
if i.offset(0, -3) = "a" then ' check column A for "a"
arrayData(10, i).value = trgtSht.range("B" & lastRowQ) 'first value copy to QCDetail
' duplicate process to accomidate each copy item
end if
next i
end sub
not tested, but am I on the right track?
Would this speed up the above code or is there another way to pick the source data and target location that works more quickly?
I though about formating the data to have the same column locations on both pages. The problem is I use all of the data to print a form and make an archive copy of the form as an added worksheet.
The QCDetail sheet only uses some of the data.
I do have a copy of the full data and a sheet called "Raw"
I could use match and index to make the other forms, or 2 sheets for data.
1 to setup for archive and print, and the other for QCDetail.
My goal is to speed up the process...
I already uploaded the file in a different thread, I'll link it here.
Mark