Copy data from multiple sheets without formatting

[sofas]

Hello.Use the following code to copy several separate columns from several sheets to one sheet.my problem is that it copies formats and colors how can I copy only values

Sub Sheets_Arrays2()
Dim LR&, LR2&
Dim wsData        As Variant
Dim Dest    As Worksheet: Set Dest = Sheets("main")


For Each wsData In Sheets(Array("op2023", "mt10", "zt15"))
LR = wsData.Cells(Rows.Count, "E").End(xlUp).Row
LR2 = Dest.Cells(Rows.Count, "C").End(xlUp).Row

Application.ScreenUpdating = False

   wsData.Range("E10:F" & LR).Copy Destination:=Dest.Range("C" & LR2 + 1)
   wsData.Range("H10:H" & LR).Copy Destination:=Dest.Range("E" & LR2 + 1)
   wsData.Range("J10:J" & LR).Copy Destination:=Dest.Range("F" & LR2 + 1)
   wsData.Range("L10:M" & LR).Copy Destination:=Dest.Range("G" & LR2 + 1)
   wsData.Range("P10:Q" & LR).Copy Destination:=Dest.Range("I" & LR2 + 1)

  Application.ScreenUpdating = True

  Next wsData

End Sub

 

[Peter_SSs]

As an example

wsData.Range("E10:F" & LR).Copy Destination:=Dest.Range("C" & LR2 + 1)
wsData.Range("E10:F" & LR).Copy
Dest.Range("C" & LR2 + 1).PasteSpecial xlPasteValues

 

[sofas]

As an example

wsData.Range("E10:F" & LR).Copy Destination:=Dest.Range("C" & LR2 + 1)
wsData.Range("E10:F" & LR).Copy
Dest.Range("C" & LR2 + 1).PasteSpecial xlPasteValues

Thank you. Can I find a faster code because the data volume is too large

 

[RudRud]

Thank you. Can I find a faster code because the data volume is too large

Kindly give a shot @sofas , based on column E:F , H , J , L:M, P:Q (starting row 10)

i run 10k code, it's taking roughly 5second

Sub Sheets_Arrays2()
Dim LR&, LR2&
Dim wsData        As Variant
Dim Dest    As Worksheet: Set Dest = Sheets("main")
Dim a()

For Each wsData In Sheets(Array("op2023", "mt10", "zt15"))
LR = wsData.Cells(Rows.Count, "E").End(xlUp).Row
LR2 = Dest.Cells(Rows.Count, "C").End(xlUp).Row

Application.ScreenUpdating = False
 
    With wsData
 
        Dest.Range("c" & LR2 + 1 & ":d" & LR2 + LR - 9).Value = wsData.Range("E10:F" & LR).Value
        Dest.Range("e" & LR2 + 1 & ":e" & LR2 + LR - 9).Value = wsData.Range("H10:H" & LR).Value
        Dest.Range("f" & LR2 + 1 & ":F" & LR2 + LR - 9).Value = wsData.Range("J10:J" & LR).Value
        Dest.Range("g" & LR2 + 1 & ":h" & LR2 + LR - 9).Value = wsData.Range("L10:M" & LR).Value
        Dest.Range("i" & LR2 + 1 & ":j" & LR2 + LR - 9).Value = wsData.Range("P10:Q" & LR).Value
 
    End With
 

  Application.ScreenUpdating = True

  Next wsData

End Sub

op2o23:

Book1
	E	F	G	H	I	J	K	L	M	N	O	P	Q
10	e10 mt10	f11		h10		j10		l10	m10			p10	q10
11	e10 mt10	f12		h10		j10		l11	m11			p10	q10
12	e10 mt10	f13		h10		j10		l12	m12			p10	q10
13	e10 mt10	f14		h10		j10		l13	m13			p10	q10
14	e10 mt10	f15		h10		j10		l14	m14			p10	q10
15	e10 mt10	f16		h10		j10		l15	m15			p10	q10
16	e10 mt10	f17		h10		j10		l16	m16			p10	q10
17	e10 mt10	f18		h10		j10		l17	m17			p10	q10
18	e10 mt10	f19		h10		j10		l18	m18			p10	q10
19	e10 mt10	f20		h10		j10		l19	m19			p10	q10
op2023

mt10

Book1
	E	F	G	H	I	J	K	L	M	N	O	P	Q
10	e10 mt10	f11		h10		j10		l10	m10			p10	q10
11	e10 mt10	f12		h10		j10		l11	m11			p10	q10
12	e10 mt10	f13		h10		j10		l12	m12			p10	q10
13	e10 mt10	f14		h10		j10		l13	m13			p10	q10
14	e10 mt10	f15		h10		j10		l14	m14			p10	q10
15	e10 mt10	f16		h10		j10		l15	m15			p10	q10
16	e10 mt10	f17		h10		j10		l16	m16			p10	q10
17	e10 mt10	f18		h10		j10		l17	m17			p10	q10
mt10

zt15

Book1
	E	F	G	H	I	J	K	L	M	N	O	P	Q
10	e10 zt15	f11		h10		j10		l10	m10			p10	q10
11	e10 zt15	f12		h10		j10		l11	m11			p10	q10
12	e10 zt15	f13		h10		j10		l12	m12			p10	q10
13	e10 zt15	f14		h10		j10		l13	m13			p10	q10
14	SHEET3	f15		h10		j10		l14	m14			p10	q10
zt15

main

 

[sofas]

Kindly give a shot @sofas , based on column E:F , H , J , L:M, P:Q (starting row 10)

i run 10k code, it's taking roughly 5second

Sub Sheets_Arrays2()
Dim LR&, LR2&
Dim wsData        As Variant
Dim Dest    As Worksheet: Set Dest = Sheets("main")
Dim a()

For Each wsData In Sheets(Array("op2023", "mt10", "zt15"))
LR = wsData.Cells(Rows.Count, "E").End(xlUp).Row
LR2 = Dest.Cells(Rows.Count, "C").End(xlUp).Row

Application.ScreenUpdating = False
 
    With wsData
 
        Dest.Range("c" & LR2 + 1 & ":d" & LR2 + LR - 9).Value = wsData.Range("E10:F" & LR).Value
        Dest.Range("e" & LR2 + 1 & ":e" & LR2 + LR - 9).Value = wsData.Range("H10:H" & LR).Value
        Dest.Range("f" & LR2 + 1 & ":F" & LR2 + LR - 9).Value = wsData.Range("J10:J" & LR).Value
        Dest.Range("g" & LR2 + 1 & ":h" & LR2 + LR - 9).Value = wsData.Range("L10:M" & LR).Value
        Dest.Range("i" & LR2 + 1 & ":j" & LR2 + LR - 9).Value = wsData.Range("P10:Q" & LR).Value
 
    End With
 

  Application.ScreenUpdating = True

  Next wsData

End Sub

op2o23:

Book1
	E	F	G	H	I	J	K	L	M	N	O	P	Q
10	e10 mt10	f11		h10		j10		l10	m10			p10	q10
11	e10 mt10	f12		h10		j10		l11	m11			p10	q10
12	e10 mt10	f13		h10		j10		l12	m12			p10	q10
13	e10 mt10	f14		h10		j10		l13	m13			p10	q10
14	e10 mt10	f15		h10		j10		l14	m14			p10	q10
15	e10 mt10	f16		h10		j10		l15	m15			p10	q10
16	e10 mt10	f17		h10		j10		l16	m16			p10	q10
17	e10 mt10	f18		h10		j10		l17	m17			p10	q10
18	e10 mt10	f19		h10		j10		l18	m18			p10	q10
19	e10 mt10	f20		h10		j10		l19	m19			p10	q10
op2023

mt10

Book1
	E	F	G	H	I	J	K	L	M	N	O	P	Q
10	e10 mt10	f11		h10		j10		l10	m10			p10	q10
11	e10 mt10	f12		h10		j10		l11	m11			p10	q10
12	e10 mt10	f13		h10		j10		l12	m12			p10	q10
13	e10 mt10	f14		h10		j10		l13	m13			p10	q10
14	e10 mt10	f15		h10		j10		l14	m14			p10	q10
15	e10 mt10	f16		h10		j10		l15	m15			p10	q10
16	e10 mt10	f17		h10		j10		l16	m16			p10	q10
17	e10 mt10	f18		h10		j10		l17	m17			p10	q10
mt10

zt15

Book1
	E	F	G	H	I	J	K	L	M	N	O	P	Q
10	e10 zt15	f11		h10		j10		l10	m10			p10	q10
11	e10 zt15	f12		h10		j10		l11	m11			p10	q10
12	e10 zt15	f13		h10		j10		l12	m12			p10	q10
13	e10 zt15	f14		h10		j10		l13	m13			p10	q10
14	SHEET3	f15		h10		j10		l14	m14			p10	q10
zt15

main
View attachment 94356

Thank you very much, it works with high efficiency

 

[RudRud]

Thank you very much, it works with high efficiency

Glad to assist, If you have any further questions, please don't hesitate to ask/share with us