Separate worksheet issues

[Ryan1005]

I've used a Macro that's designed to separate data from a master worksheet to relevant sub worksheets based on the value of column 2. (vcol = 2) The initial record works - it appears in the correct worksheet. However when I attempt to enter a record with a different vcol value it won't appear in the other worksheet.

Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 2
Set ws = Sheets("Marketing Budget 2019")
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A2:C2"
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A2")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
End Sub

Any ideas?
Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
Can you please explain what you mean by

when I attempt to enter a record with a different vcol value it won't appear in the other worksheet.

 

[Ryan1005]

When I enter an initial record with a value e.g Germany in a Master Sheet and run the macro, the record will then appear in the Germany sheet too. However, if I then enter another record with the value e.g France, the Germany record will still appear, but the France sheet will appear empty.

Whatever the value is for the first record, it successfully copies into the relevant sheet, no matter how many times - however once the value changes to anything but the initial value, it won't seem to copy the record to the relevant sheet.

Apologies if its still not very clear, I'm still learning the excel ropes!

 

[Fluff]

As long as you have a header row in row 2, with the data starting in row3 and your countries are in col B. I can see no reason why those rows would not copy over.

 

[Ryan1005]

Both of which are correct unfortunately. It seems to have stumped everyone

 

[Fluff]

Does this work any better?

 

[Ryan1005]

Nope, still gives the same results

 

[Fluff]

Would you be willing to share your workbook?
If so upload it to a share site such as OneDrive, GoogleDrive, DropBox, mark for sharing & post the link to the thread