VBA for moving sections

[Leo55]

Hello,

Don't know if this is possible, I have done some playing around and it has beaten me.

I get a spreadsheet which has 2 different datasets on it, one below the other, the length of the data sets vary. So I am having trouble as the row the second data set start on changes.

What I am trying to do is write a macro to search for the header "Count" and then select the A to M (13) on the row and then all data below that to move to a second tab.

Is this possible, am I just being inexperienced here?

Thank you in advance.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Leo55]

So here is an a cut down sort of view, ( Below), so the first data set is rows 1 -11, again this varies significantly, and this is a cut down sanitized version, the second data set start on here at row 12 with new headers (the first one starting as Count), the second data base, is alway 13 columns (A-M) but varies in length. What I would like to do is move the headers on for the second database (Row 12 in this example) and move that row and all below it (can be the entire rest of the worksheet) to a second sheet.

Hope this assists.

Thanks again.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Customer	Type	Number	Date	Time	Address	Operator	District	Invoice	Provider	Latitude	Longitude	Dealing Staff
2	Mr Smith	2	123456	01/01/2020	1200	123 High Street	EE	Yorkshire	Sent	EE	12346789	4580484	Bill
3	Mr Smith	2	123456	02/01/2020	1201	124 High Street	EE	Yorkshire	Sent	EE	12346789	4580484	Bill
4	Mr Smith	2	123456	03/01/2020	1202	125 High Street	EE	Yorkshire	Sent	EE	12346789	4580484	Bill
5	Mr Smith	2	123456	04/01/2020	1203	126 High Street	EE	Yorkshire	Sent	EE	12346789	4580484	Bill
6	Mr Smith	2	123456	05/01/2020	1204	127 High Street	EE	Yorkshire	Sent	EE	12346789	4580484	Bill
7	Mr Smith	2	123456	06/01/2020	1205	128 High Street	EE	Yorkshire	Sent	EE	12346789	4580484	Bill
8	Mr Smith	2	123456	07/01/2020	1206	129 High Street	EE	Yorkshire	Sent	EE	12346789	4580484	Bill
9	Mr Smith	2	123456	08/01/2020	1207	130 High Street	EE	Yorkshire	Sent	EE	12346789	4580484	Bill
10	Mr Smith	2	123456	09/01/2020	1208	131 High Street	EE	Yorkshire	Sent	EE	12346789	4580484	Bill
11	Mr Smith	2	123456	10/01/2020	1209	132 High Street	EE	Yorkshire	Sent	EE	12346789	4580484	Bill
12	Count	Site Name	address	Operator	Logistics	Sent	Weight	First Name	Surname	Address	Employee Number	Vehicle	Checked
13	455	HQ	45 All saint street	O2	Yes	No	155g	John	Smith	123 High Street	45789	VH48AWQ	Y
14	87	Base	46 All saint street	EE	Yes	No	155g	John	Smith	124 High Street	45789	VH48AWQ	Y
15	159	WFH	47 All saint street	Vodaphone	Yes	No	155g	John	Smith	125 High Street	45789	VH48AWQ	N
16	485	HQ	48 All saint street	O3	Yes	No	155g	John	Smith	126 High Street	45789	VH48AWQ	N
17	22	Base	49 All saint street	EE	Yes	No	155g	John	Smith	127 High Street	45789	VH48AWQ	N
18	48	WFH	50 All saint street	Vodaphone	Yes	No	155g	John	Smith	128 High Street	45789	VH48AWQ	Y
19	268	HQ	51 All saint street	O4	Yes	No	155g	John	Smith	129 High Street	45789	VH48AWQ	Y
20	555	Base	52 All saint street	EE	Yes	No	155g	John	Smith	130 High Street	45789	VH48AWQ	Y
21	48	WFH	53 All saint street	Vodaphone	Yes	No	155g	John	Smith	131 High Street	45789	VH48AWQ	Y
Sheet1

 

[Leo55]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Apologies meant to reply straight to you. Please see above in response. Thanks!!

 

[mumps]

Try:

Sub MoveRange()
    Application.ScreenUpdating = False
    Dim count As Range
    Set count = Range("A:A").Find("Count", LookIn:=xlValues, lookat:=xlWhole)
    Range("A" & count.Row, Range("M" & Rows.count).End(xlUp)).Cut Sheets("Sheet2").Range("A1")
    Application.ScreenUpdating = True
End Sub

 

[Leo55]

Try:

Sub MoveRange()
    Application.ScreenUpdating = False
    Dim count As Range
    Set count = Range("A:A").Find("Count", LookIn:=xlValues, lookat:=xlWhole)
    Range("A" & count.Row, Range("M" & Rows.count).End(xlUp)).Cut Sheets("Sheet2").Range("A1")
    Application.ScreenUpdating = True
End Sub

That has worked like a dream. Thank you for that!!

 

[mumps]

You are very welcome.

 

[Leo55]

You are very welcome.

You are very welcome.

Quick question, if I wanted to extend this code to capture another 2-3 columns what would I need to change I have tried to just change "M" to O for example and it seems to then inverse the results and send the first half of my data to the first sheet

 

[mumps]

Changing "M" to O will work if column O contains data in all the rows until the last used row. Is that the case?

 

[Leo55]

Changing "M" to O will work if column O contains data in all the rows until the last used row. Is that the case?

Thank you, that was the case, I was having a bit of a moment, all fixed and working now.

Thank you!!