Copy Data based on region to relevant sheet name

[howard]

I have various sheets from sheet 5 onwards based on the unique region name in Col A on sheet1. Have a macro to create these sheet names


I have region names in Col A on sheet1 ("customers") from row 12 onwards and headings in row 11


I have a macro to copy all the data from row12 onwards based on the region to the relevant sheet name. for eg id "North" appears in Col A , then all the data pertaining to North to be copied to sheet "North" "East" to be copied to sheet "East" after the last row containing data on these sheets


See my code below


The code is clearing the data on sheet1. I cannot figure this out


It would be appreciated if someone could kindly amend my code

 Sub CopyDatatoRelevantSheets()
  Sheets(1).Select
  Dim ws As Worksheet
  Dim LR As Long
  LR = Cells(Rows.Count, "A").End(xlUp).Row
  For Each ws In Worksheets
    If ws.Name <> "Customers" And ws.Name <> "Report Name" And ws.Name <> "Purchases" And ws.Name <> "Email Addresses" Then
     
      Sheets("customers").Range("A11:U" & LR).Copy Destination:=ws.Range("A2")
      With ws.UsedRange
        .AutoFilter Field:=1, Criteria1:="<>" & ws.Name, Operator:=xlAnd, Criteria2:="<>"
       .Offset(1).EntireRow.Delete
        .AutoFilter
      End With
    End If
  Next ws
End Sub

Book1
	A	B	C	D	E	F	G
1
2
3
4	REGIONAL DATA
5
6	Group	ALL GROUPS
7	Branches	ALL BRANCHES
8	Start Date	01 Jan 2017
9	End Date	30 Apr 2018
10
11	Region	Client Category	Title	Initials	First Name	Last Name	ID Number
12	North	Business	MR	R R	RICHARD	Davids	07255839089
13	North	PRIVATE	MRS	A	Peter	Sam	02110152089
14	East	PRIVATE	MISS	X Z	David	Daniel	07305743088
15	South	PRIVATE	MR	V	Colin	Back	49483
16	South	PRIVATE	MRS	K	Sue	Sions	11185745083
17	Gaut	Business	MR	O J	Pam	Davids	11185745083
18	Gaut	PRIVATE	MR	K	Daniel	Noel	09115092089
Customers

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...aste-data-after-last-row-containing-data.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[howard]

My Aplologies

Clean forgot to post the link of the crosspost

https://www.excelforum.com/excel-pr...aste-data-after-last-row-containing-data.html