Why Does My Copy/Paste Macro Copy Rows Outside Range?

[andybason]

Hello

I've put together this macro to copy and paste data to another sheet. The source data has headings in the first row, a blank second row and the data starts in the third row. The data is dynamic and the number of rows varies. An extract is shown below.

The issue is that sometimes the macro pastes the headings even though they are outside the copy range.

Does anyone know why it is doing this?

Thanks

Sub Copy()

With ThisWorkbook.Worksheets("List")
 .Range("A3:L" & .Cells(Rows.Count, "A").End(xlUp).Row).Copy Destination:=ThisWorkbook.Worksheets("Repo").Range("A" & .Rows.Count).End(xlUp).Offset(1)
End With

End Sub

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	MarketID	Date/Time	MarketName	MarketState	SelectionID	SelectionName	Back	Lay	LTP	SelectionVol	MarketVol	VWAP
2
3	171820655	03/08/2020 13:10:45	Swin 3rd Aug - 13:13 A9 476m	On		1. Cloneyross Poppy	6.2	6.6	6.2	41.95	2399.88
4	171820655	03/08/2020 13:10:45	Swin 3rd Aug - 13:13 A9 476m	On		2. Dunbar Patsy	6.6	7	6.8	141.55	2399.88
5	171820655	03/08/2020 13:10:45	Swin 3rd Aug - 13:13 A9 476m	On		3. Swift Heavily	18	22	19.5	14.8	2399.88
6	171820655	03/08/2020 13:10:45	Swin 3rd Aug - 13:13 A9 476m	On		4. Hardly Normal	9.4	9.8	9.6	222.5	2399.88
7	171820655	03/08/2020 13:10:45	Swin 3rd Aug - 13:13 A9 476m	On		5. Swift Harass	2.66	2.82	2.82	1879.28	2399.88
8	171820655	03/08/2020 13:10:45	Swin 3rd Aug - 13:13 A9 476m	On		6. Knockmant Pearly	5.4	5.7	5.8	99.8	2399.88
9	171820655	03/08/2020 13:10:45	Swin 3rd Aug - 13:13 A9 476m	On		1. Cloneyross Poppy	6.2	6.6	6.2	41.95	2399.88
10	171820655	03/08/2020 13:10:45	Swin 3rd Aug - 13:13 A9 476m	On		2. Dunbar Patsy	6.6	7	6.8	141.55	2399.88
11	171820655	03/08/2020 13:10:45	Swin 3rd Aug - 13:13 A9 476m	On		3. Swift Heavily	18	22	19.5	14.8	2399.88
12	171820655	03/08/2020 13:10:45	Swin 3rd Aug - 13:13 A9 476m	On		4. Hardly Normal	9.4	9.8	9.6	222.5	2399.88
13	171820655	03/08/2020 13:10:45	Swin 3rd Aug - 13:13 A9 476m	On		5. Swift Harass	2.66	2.82	2.82	1879.28	2399.88
14	171820655	03/08/2020 13:10:45	Swin 3rd Aug - 13:13 A9 476m	On		6. Knockmant Pearly	5.4	5.7	5.8	99.8	2399.88
15	171820655	03/08/2020 13:10:47	Swin 3rd Aug - 13:13 A9 476m	On		1. Cloneyross Poppy	6.2	6.6	6.2	41.95	2453.56
16	171820655	03/08/2020 13:10:47	Swin 3rd Aug - 13:13 A9 476m	On		2. Dunbar Patsy	6.6	7	6.8	142.64	2453.56
17	171820655	03/08/2020 13:10:47	Swin 3rd Aug - 13:13 A9 476m	On		3. Swift Heavily	19	22	19.5	14.8	2453.56
18	171820655	03/08/2020 13:10:47	Swin 3rd Aug - 13:13 A9 476m	On		4. Hardly Normal	9.6	11	9.6	229.08	2453.56
19	171820655	03/08/2020 13:10:47	Swin 3rd Aug - 13:13 A9 476m	On		5. Swift Harass	2.7	2.8	2.82	1921.29	2453.56
20	171820655	03/08/2020 13:10:47	Swin 3rd Aug - 13:13 A9 476m	On		6. Knockmant Pearly	5.3	5.6	5.4	103.8	2453.56
Store

 

[Peter_SSs]

I'm not sure at this stage but your code refers to sheets 'List' and 'Repo' but your XL2BB data is a sheet called 'Store'. What can you tell us about all that?

 

[maras]

Maybe the reason is the line of code because the last line in the "Repo" sheet is taken from the "List" sheet (colored text).

With ThisWorkbook.Worksheets("List")
.Range("A3:L" & .Cells(Rows.Count, "A").End(xlUp).Row).Copy Destination:=ThisWorkbook.Worksheets("Repo").Range("A" & .Rows.Count).End(xlUp).Offset(1)
End With
Change to:

With ThisWorkbook.Worksheets("List")
 .Range("A3:L" & .Cells(Rows.Count, "A").End(xlUp).Row).Copy Destination:=ThisWorkbook.Worksheets("Repo").Range("A" & ThisWorkbook.Worksheets("Repo").Rows.Count).End(xlUp).Offset(1)
End With

 

[andybason]

Sorry for the confusion guys. I created a duplicate of the Store sheet to test to find the issue. Store is the original name. List is the new test sheet. I copied the XL2BB from the Store sheet in error. The layout in List is the same however and the issue is the same.

Apologies!

 

[Peter_SSs]

Maybe the reason is the line of code because the last line in the "Repo" sheet is taken from the "List" sheet (colored text).

It shouldn't be that as it is only the row count that comes from 'List' (& that should be the same for all sheets in the workbook).

The only way I have found that the headings from 'List' can get copied to 'Repo' is if 'List' has no values in column A below the heading row.

 

[andybason]

Thanks Peter. I'll see if I can ensure that data is populated immediately below the headings.