VBA - COPY AND PASTE RANGE OF CELLS IN MULTIPLE SHEETS

[evernaren]

Dear all,

As i am beginner to VBA coding. i found this VBA code in this website under the above mentioned title. it was working fine when i run the code, But i have some different criteria. Can some one help me in the regard please.

In the below mentioned code it was mentioned the range of cells to be copied. but in my condition it is not like that, sheet name alpha as 15 rows and sheet name beta as 100 rows. i like to copy only the exact number of rows to the summary sheet. from column A to Column H. the row starts from 24th.

and After copying the contents to the Summary sheets, i like to sum the quantity of all the repeated items to a single item in another sheet call "consolidated sheet".

i also attached the sample file for your reference. Please help.

Private Sub CommandButton1_Click()
'Modified 10/12/2021 6:36:48 PM EDT
On Error GoTo M
Application.ScreenUpdating = False
Dim i As Long
Dim ans As String
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Sheets("MASTER").Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = 6

For i = 2 To Lastrow
ans = Sheets("MASTER").Cells(i, 1).Value

With Sheets(ans)
.Range("A19:F100").Copy
Sheets("SUMMARY").Cells(Lastrowa, 1).PasteSpecial xlPasteValues
Lastrowa = Sheets("SUMMARY").Cells(Rows.Count, "A").End(xlUp).Row + 1
End With
Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "You tried to use a sheet name that does not exist" & vbNewLine & "Or we had another problem"
Application.ScreenUpdating = True

End Sub

REFERENCE.xlsx
	A	B	C	D	E	F	G	H
22							2220305110-MNT
23							REV : 10	26-May-2022
24	Sr. #	Item Code	No.	Description	Uom	QTY	Unit Price	Total Price
26	658	11050408150	1. 1	6" Gear Operated Butterfly Valve Grooved, American Standard, UL/FM Approved, W/Tamper Switch, W/P 175 Psi	Pieces	2.00	1,155.00	2,310.00
27	385	0	1. 2	Water Flow Switch 6", UL Listed - FM Approved	Pieces	2.00	505.00	1,010.00
28	365	0	1. 3	Test & Drain Valve, Size 2", Female x Female NPT, Brass, (1/2" Orifice), UL Listed	Pieces	2.00	893.00	1,786.00
29	4406	10558522531	1. 4	Pressure Gauge 0-300 Psi, 3-1/2" (90mm) Dia, 1/4" End Connection, UL Listed/FM Approved	Pieces	2.00	99.00	198.00
30	4114	11140108515	1. 5	1/2" Ball Valve, NPT Double Female Threaded, Brass Nickel Plated, Steel Lever Handle, W/P 175 Psi (12 Bar), UL Listed	Pieces	2.00	23.00	46.00
31	3251	11022008651	1. 6	Check Valve 6", Ductile Iron Body to C508, Swing Type, EDPM Seat, Flanged Ends To ANSI B16.1, Class 125, FF Flange, W/P 200 Psi, RED RAL 3000 FBE Coating, UL/FM Approved	Pieces	2.00	1,432.00	2,864.00
32			1. 7	Installation for the above	LOT	2.00	1,680.00	3,360.00
33	793	0	2. 1	Automatic Air Release Valve 1" FNPT inlet & 1/2" FNPT outlet , 175 Psi water, FM Approved	Pieces	2.00	470.00	940.00
34	4116	11140108525	2. 2	1" Ball Valve, NPT Double Female Threaded, Brass Nickel Plated, Steel Lever Handle, W/P 175 Psi (12 Bar), UL Listed	Pieces	2.00	55.00	110.00
35			2. 3	Installation for the above	LOT	2.00	160.00	320.00
36	4116	11140108525	3	1" Ball Valve, NPT Double Female Threaded, Brass Nickel Plated, Steel Lever Handle, W/P 175 Psi (12 Bar), UL Listed	Pieces	2.00	55.00	110.00
37	754	11230122105	4. 1	Fire Department Connection, Exposed Straight, 4" Female NPT Outlet and 2 x 2-1/2" Female NST Swivel Inlet, Double Drop Clapper, Brass, UL/FM Approved	Pieces	2.00	1,064.80	2,129.60
38	3258	11022008607	4. 2	Check Valve 4", Ductile Iron Body to C508, Swing Type, EDPM Seat, Flanged Ends To ANSI B16.1, Class 125, FF Flange, W/P 300 Psi, RED RAL 3000 FBE Coating, UL/FM Approved	Pieces	2.00	858.00	1,716.00
39	4181	10886475281	4. 3	Cap (Plug) 2-1/2" Male NST with Chain, Brass Finish	Pieces	4.00	104.50	418.00
40	796	0	4. 4	Wall Plate for 4" Fire Department Connection	Pieces	2.00	118.00	236.00
41	3272	0	4. 5	3/4" NPT Male Inlet & Male Outlet, Automatic Ball Drip	Pieces	2.00	72.00	144.00
42			4. 6	Installation for the above	LOT	2.00	1,200.00	2,400.00
43			5. 1	Single Door Fire Hose Reel Cabinet, Recessed Type, Solid Door, Stainless steel Brush Finish #304 (Facial) and Back Box standard E.G Steel*** Without Rail***	Pieces	8.00	1,300.00	10,400.00
44	682	11080422045	5. 2	1-1/2" Pressure Restricting Valve, NPT, Brass Finish, UL/FM Approved	Pieces	8.00	452.10	3,616.80
45	439	10621110332	5. 3	Fire Hose Reel 1" x 30 Mtr. Cabinet Mounted, Swinging Automatic Type (Steel), c/w 1" Brass Chrome Plated Rotary Nozzle and 1-1/2" x 1" Tail Hose Coupling, SLIM Type	Pieces	8.00	710.00	5,680.00
46			5. 4	Installation for the above	LOT	8.00	650.00	5,200.00
47	4847	11110108040	6	Globe Valve 1 1/2" Bronze, UL Listed	Pieces	8.00	250.00	2,000.00
48	3130	0	7. 1	OS&Y Gate Valve 2", Ductile Iron Body, Rising Stem with EPDM Coated Resilient Wedge, W/P 200 Psi, RED RAL3000 FBE Coating, UL/FM Approved	Pieces	45.00	657.00	29,565.00
49	379	0	7. 2	Tamper Switch (Supervisory), UL/FM - Single Contact	Pieces	45.00	283.00	12,735.00
50	4. 7	VALVE 2"	7. 3	Installation for the above	LOT	45.00	480.00	21,600.00
51	3133	0	8. 1	OS&Y Gate Valve 4", Ductile Iron Body, Rising Stem with EPDM Coated Resilient Wedge, W/P 200 Psi, RED RAL3000 FBE Coating, UL/FM Approved	Pieces	2.00	876.00	1,752.00
52	379	0	8. 2	Tamper Switch (Supervisory), UL/FM - Single Contact	Pieces	2.00	283.00	566.00
53	4. 4	VALVE 4"	8. 3	Installation for the above	LOT	2.00	560.00	1,120.00
65			9	ENGINEERING, TESTING & COMMISSIONING	LOT	1.00	3,900.00	3,900.00
68			TOTAL PRICE FOR THE ABOVE (IN SAR):					118,232.40
1

 

[NdNoviceHlp]

Hi evernaren. It's unclear what row numbers U actually want to transfer and where exactly U want to put them on the summary sheet? This bit of code transfers Worksheets("Alpha").Range("A24:H38") to Rows 1 to 15 on the summary sheet followed by Worksheets("Beta").Range("A24:H123") to rows 16 to 115 on the summary sheet. I have no idea what "After copying the contents to the Summary sheets, i like to sum the quantity of all the repeated items to a single item in another sheet call "consolidated sheet"." means. What repeated items? Put the values where on the consolidated sheet? Anyways, this will get U started. Adjust the range(s) to the actual rows that U want transferred. Save a copy of your wb and give this code a trial. HTH. Dave

Sub test()
Dim Rng As Range, Rng2 As Range, LastRow As Integer
'Grab Some Data and Store it in a "Range" variable
Set Rng = Worksheets("Alpha").Range("A24:H38")
Set Rng2 = Worksheets("Beta").Range("A24:H123")
'Transfer Values to same spot in another worksheet (Mimics PasteSpecial Values Only)
Worksheets("summary sheet").Range("A1").Resize(Rng.Rows.Count, _
                       Rng.Columns.Count).Cells.Value = Rng.Cells.Value
LastRow = Sheets("summary sheet").Cells(Rows.Count, "D").End(xlUp).Row
Worksheets("summary sheet").Range("A" & LastRow + 1).Resize(Rng2.Rows.Count, _
                       Rng2.Columns.Count).Cells.Value = Rng2.Cells.Value
End Sub