Copy range excel from Cell name dynamic by vba

[ngocanh87]

Hello all,

Can I copy range excel from Cell name dynamic to another sheet ?.
Allway Col A, name cell "Item No."

SSC-2023-145 AWAJI.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
22	ITEM No.	PO NO	DISCRIPTION OF GOODS (MM)								KG/M	QUANTITY (PC)	Net Weight (MT)	U.PRICE (USD/MT)	AMOUNT (USD)
27	1	300522-3	Φ	OD	101.6	MM x WT	4.8	MM x MM/PC	6,050	MM	11.5	610	42.441	1320	56,022.12
28		300522-3	Φ	OD	101.6	MM x WT	4.8	MM x MM/PC	6,050	MM	11.5	3	0.209	1320	275.88
29		300522-3	Φ	OD	101.6	MM x WT	4.8	MM x MM/PC	6,050	MM	11.5	100	6.958	1320	9,184.56
30		300522-3	Φ	OD	101.6	MM x WT	4.8	MM x MM/PC	6,050	MM	11.5	5	0.348	1320	459.36
31	2	300522-3	Φ	OD	114.3	MM x WT	5.3	MM x MM/PC	6,450	MM	14.2	70	6.411	1320	8,462.52
32		300522-3	Φ	OD	114.3	MM x WT	5.3	MM x MM/PC	6,450	MM	14.2	8	0.733	1320	967.56
33		300522-3	Φ	OD	114.3	MM x WT	5.3	MM x MM/PC	6,450	MM	14.2	170	15.570	1320	20,552.40
34		300522-3	Φ	OD	114.3	MM x WT	5.3	MM x MM/PC	6,450	MM	14.2	1	0.092	1320	121.44
35		300522-3	Φ	OD	114.3	MM x WT	5.3	MM x MM/PC	6,450	MM	14.2	300	27.477	1320	36,269.64
36		300522-3	Φ	OD	114.3	MM x WT	5.3	MM x MM/PC	6,450	MM	14.2	5	0.458	1320	604.56
37		300522-3	Φ	OD	114.3	MM x WT	5.3	MM x MM/PC	6,450	MM	14.2	200	18.318	1320	24,179.76
38		300522-3	Φ	OD	114.3	MM x WT	5.3	MM x MM/PC	6,450	MM	14.2	7	0.641	1320	846.12
INV

 

[bebo021999]

Not clear for me.
Do you mean, click a button then an input box appear to ask which "Item No", for example, if user input "2", then copy range A31:O38 to new sheet?

 

[ngocanh87]

This table in sheet have a lot of data, I just want copy table from the Cell name: "Item No." (row dynamic) to last row of table by vba.

 

[ngocanh87]

I see the code for lastrow "TOTAL" to A9
How can I change copy from firstrow "ITEM No." to lastrow

Public Sub Copy_Data()

Dim lastCell As Range, wrkSht As Worksheet
Dim lastRow As Integer, firstCol As Range
Dim copyRng As Range

Set wrkSht = ActiveWorkbook.Worksheets("INV")
lastRow = Application.WorksheetFunction.Match("TOTAL", wrkSht.Range("A:A"))
Set firstCol = wrkSht.Range(wrkSht.Range("A9"), wrkSht.Cells(lastRow, 1))
Set copyRng = wrkSht.Range(firstCol, firstCol.End(xlToRight))

copyRng.Copy
Sheets.Add After:=ActiveSheet
Range("A1").Select
ActiveSheet.Paste

End Sub