Extract Country Name, Create Tab in that country Name and paste the details

[Kaiser]

Dear Users, if anyone can also help me with the formula, please do advise. I have attached an Excel sheet which explains everything. Actually, I may have a list of 1000 Card No with Country and Location, I want the formula to extract the country names accordingly and create a tab with the same name of the Country by itself and then paste the Card No, Country and Location by in that specific country tab. Is it possible. Please advise.

 

[azumi]

Try this

 

[Kaiser]

Try this

THank you so much Mr Azumi and thank you for the hard work. But, I was wondering, is it possible that the formula will create a new tab by itself with the same name of the country that I want to extract from the list. Or do I have to make the Tab before running the formula. Please do advise, and once again thank you...

 

[Sathish KV]

Hi,

Your requirement can be done through a macro only
Is this a one time requirement or going to be used frequently
Are you going to update the main sheet with data frequently and you want to move it to country sheet every time you update data to main sheet?

 

[Mike H..]

THank you so much Mr Azumi and thank you for the hard work. But, I was wondering, is it possible that the formula will create a new tab by itself with the same name of the country that I want to extract from the list. Or do I have to make the Tab before running the formula. Please do advise, and once again thank you...

Hi,

See the attached workbook. This will copy the data to the correct worksheet. If the worksheet doesn't exist the code will create it with the country name.

Option Explicit
Private Sub CommandButton1_Click()
Dim r As Range, LastRow As Long, ws As Worksheet
Dim LastRow1 As Long
Dim src As Worksheet
Set src = Sheets("Sheet1")
LastRow = src.Cells(Cells.Rows.Count, "B").End(xlUp).Row
For Each r In src.Range("B2:B" & LastRow)
  On Error Resume Next
  Set ws = Sheets(CStr(r.Value))
  On Error GoTo 0
  If ws Is Nothing Then
  Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = CStr(r.Value)
  src.Rows(1).Copy ActiveSheet.Range("A1")
  LastRow1 = Sheets(CStr(r.Value)).Cells(Cells.Rows.Count, "A").End(xlUp).Row
  src.Rows(r.Row).Copy Sheets(CStr(r.Value)).Cells(LastRow1 + 1, 1)
  Set ws = Nothing
  Else
  LastRow1 = Sheets(CStr(r.Value)).Cells(Cells.Rows.Count, "A").End(xlUp).Row
  src.Rows(r.Row).Copy Sheets(CStr(r.Value)).Cells(LastRow1 + 1, 1)
  Set ws = Nothing
  End If
Next r
End Sub

 

[Kaiser]

Hi,

Dear my friend Mike, I should first thank you for the hard effort. It works really great..if I change the card no and the country, will it create any problem for the Macro. This is not a fixed data and will be changing time. Thank you once again, Mike for everything.

Kaiser

 

[Mike H..]

You're welcome and thanks for the feedback.

The code doesn't use the Card No except to copy it to the relevant worksheet when it copies the row so changing the Card No will have no effect on the code. The only column the code actually uses is the Country column.

 

[Kaiser]

Hi,

Your requirement can be done through a macro only
Is this a one time requirement or going to be used frequently
Are you going to update the main sheet with data frequently and you want to move it to country sheet every time you update data to main sheet?

You're welcome and thanks for the feedback.

The code doesn't use the Card No except to copy it to the relevant worksheet when it copies the row so changing the Card No will have no effect on the code. The only column the code actually uses is the Country column.

Dear Mike,

I got the point. But, I am trying to learn the great work you have done but cannot figure out how did you put that click button sheet 1. Please do advise Mike...

 

[Kaiser]

Hi,

Your requirement can be done through a macro only
Is this a one time requirement or going to be used frequently
Are you going to update the main sheet with data frequently and you want to move it to country sheet every time you update data to main sheet?

Yes, Satish, I have to use the sheet frequently. Actually, it is a part of my daily work. I have to put the data everyday and run the formula or macro everyday to get the result as expected. Thank you for asking.

 

[Mike H..]

Dear Mike,

I got the point. But, I am trying to learn the great work you have done but cannot figure out how did you put that click button sheet 1. Please do advise Mike...

Hi,

I used the 'Control Toolbox' like this.

View | Toolbars | Control toolbox and you should see the toolbar. Click on the button and point the cursor at the worksheet, hold down the left mouse button and drag to create the button.

If you right click the button you get all the available options such as View code and format the button.

Note I assumed that because this is a .xls file you're using Excel 2003. In later versions you do this via the developer tab. To show the developer tab follow this link.

http://msdn.microsoft.com/en-us/library/bb608625.aspx