Trying to add an Inputbox in Macro

[Vikas Kumar]

Dear Experts,

I have following macro which split a mastersheet into multiple sheet based on its city,

---

Sub DataSplit_2()
Dim City As String
Dim Lr As Byte
Range("C2").Activate
Do While ActiveCell.Value <> ""
City = ActiveCell.Value
On Error Resume Next
If Sheets(City) Is Nothing Then
On Error GoTo 0
Range("A1").EntireRow.Copy
Sheets.Add
Range("A1").PasteSpecial
ActiveSheet.Name = City
Sheets("Data").Activate
ActiveCell.EntireRow.Copy
Sheets(City).Activate
ActiveCell.Offset(1, 0).PasteSpecial
Sheets("Data").Activate
Else
ActiveCell.EntireRow.Copy
Sheets(City).Activate
Lr = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & Lr).PasteSpecial
Sheets("Data").Activate
End If
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

---

What im looking now, To just add a inputbox into the code so that whenever i run macro firstly it ask to 'Enter your city name for which you want to splil' and whichever city enters only for those a new worksheet (single) to be created.

One more thing - I do not want to split "Ahmedabad & Mumbai" city so whenever I enter anyone among of them a msgbox should appear stating "You can't split this sheet".

Hope you won't be confuse by above statement.

Also for ease pasting my data's Header,


[TABLE="width: 582"]
<tbody>[TR]
[TD="class: xl63, width: 82"]Zone[/TD]
[TD="class: xl63, width: 82"]Region[/TD]
[TD="class: xl63, width: 98"]City[/TD]
[TD="class: xl63, width: 99"]Product[/TD]
[TD="class: xl63, width: 70"]Qty[/TD]
[TD="class: xl63, width: 72"]Price[/TD]
[TD="class: xl63, width: 79"]Sale (in Rs.)[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be appreciated. Thank in Advance.

 

[dmt32]

Hi Dave,

This one is doing much better but post splitting the input city, Just copy headers only does not take rest data of entered city.

Check your spelling when entering in InputBox.

Dave

 

[Vikas Kumar]

Randomly tested with multiple cities. Pls have a check with yourself also.

 

[dmt32]

Randomly tested with multiple cities. Pls have a check with yourself also.

Works ok for me - Input is case sensitive

Change the line shown in RED & see if helps

For Each Cell In RNG
        If UCase(Cell.Value) = UCase(City) Then

Dave

 

[Vikas Kumar]

Works ok for me - Input is case sensitive

Change the line shown in RED & see if helps

For Each Cell In RNG
        If UCase(Cell.Value) = UCase(City) Then

Dave

This was the expected bro........Thanks a ton for help.

Really laudable☺.