Copying table range to another sheet Vba

[afc171]

Hello guys,

I am trying to copy a table which pulls in data so can vary in number of rows. I keep getting a memory error when copying to another sheet..

2Despatch Consumables V1.xlsm
	B	C	D	E	F	G	H
1	Despatch Consumables
2	product	long_description	lot_number	bin_number	quantity	inspection_date	expiry_date
3	GEN0023	Azowipes	15278	LC14COSH	8	20/Jun/2022	31/May/2024
4	GEN0023	Azowipes	15852	LC14COSH	19	29/Jul/2022	30/Jun/2024
5	GEN0023	Azowipes	15959	LC14COSH	20	26/Aug/2022	30/Jun/2024
6	PKG0001	BUF BOX 120 X 79 X 60MM	300421	LC/P8	298	30/Apr/2021
7	PKG0001	BUF BOX 120 X 79 X 60MM	260422	LC/P8	1821	26/Apr/2022
8	PKG0003	BUF BOX SLEEVES LIT.BUF	170566KSC	LC/P8	356	27/May/2021
9	PKG0004	BOVINE KIT SLEEVE LIT.KIT BOV	090719	LC/P8	67	09/Jul/2019
10	PKG0004	BOVINE KIT SLEEVE LIT.KIT BOV	031019	LC/P8	154	03/Oct/2019
11	PKG0005	ICT KIT SLEEVE LIT.KIT PACK	031019	LC/P8	366	03/Oct/2019
12	PKG0006	MMT/RMT KIT SLEEVE LIT.MMT	169162LC	LC/P8	949	30/Sep/2020
13	PKG0008	SINGLE WALL BROWN BOX 305X228X130MM L25	170367KSC	LC/P8	15	22/Apr/2021
14	PKG0008	SINGLE WALL BROWN BOX 305X228X130MM L25	211021	LC/P8	100	21/Oct/2021
15	PKG0009	DOUBLE WALL BROWN BOX 305X305X305MM L49	167212KSC	LC/P8	56	01/Oct/2019
16	PKG0010	A4 PLAIN DOC ENCLOSED WALLETS 1000/BOX	150720	LC/P8	3000	15/Jul/2020
17	PKG0011	A7 PLAIN DOC ENCLOSED WALLETS 1000/BOX	091019	LC/P8	5	09/Oct/2019
18	PKG0013	BROWN TAPE LOW NOISE 48MMX66M 36/BOX	161121	LC/P8	180	16/Nov/2021
19	PKG0014	BUBBLE BAG 130MMX185MM 500/BOX	081221	LC/P8	560	08/Dec/2021
20	PKG0015	SMALL BUBBLE WRAP - 3X500MMX200M	210422	LC/P8	2	21/Apr/2022
21	PKG0016	CLEAR TAPE LOW NOISE 48MMX66M 36/BOX	031019	LC/P8	102	03/Oct/2019
22	PKG0017	WHITE M/LITE ENV MLPF/3 220X330MM 50/BOX	170274KSC	LC/P8	800	26/Mar/2021
Despatch-LC-Stock

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:I67	Expression	=ROW(B3)=HighlightStock	text	NO

Pasting into Sheet1 gives 1004 runtime memory error..

2Despatch Consumables V1.xlsm
	A	B	C	D	E	F	G	H	I
1	Despatch Consumables - Static Stock
2	product	long_description	lot_number	bin_number	quantity	inspection_date	expiry_date	ID	Check?
Sheet1

Sub CopyToStatic()

    Range("B3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A3").Select
    ActiveSheet.Paste
    Range("A1:H1").Select
End Sub

 

[My Aswer Is This]

Try something like this:
Modify sheet names and Table name.
Assuming you are actually talking about a Excel Table

Sub Copy_Table()
'Modified 10/3/2022  6:04:00 PM  EDT
Sheets("Alpha").ListObjects("One").DataBodyRange.Copy Sheets("Bravo").Range("G3")
End Sub

 

[afc171]

Thanks, that works a treat

 

[My Aswer Is This]

Thanks, that works a treat

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.