help for excel Template macro

[ushaanu]

Hi Excel ninjas

i hope you all are fine

I am using this macro which is create by one of member it`s working fine but I need some changes in this macro
I have some data in excel . I need in main sheet data convert to different sheet VERIFICATION format with details , details picked from main sheet . but with some condition

like

as45455656-Bn3
as45455656-Bn4
as45455656-Cn3
as45455656-Cn4
dfd343567-B
dfd343567-C

above number are same before - it`s mean number is same for the same number I need one sheet, in template filed given like below

if after number -BN4 so amt is 2340, b stand for black and c stands for color

if there is only number without -Bn4,Bn3,Cn3,Cn4 it`s mean black N4 in template

and 3rd condition if after number-b or number-c it`s stand black n4 and color N4.

Thanks
Anu


VBA Code:

Sub SheetMaker()

Dim ShtName As String 'Sheet NameDim MySht As String 'True/FalseDim ws As Worksheet

Dim MyNum As String 'NumberDim MyCust As String 'CustomerDim MyAddr As String 'AddressDim MyCity As String 'CityDim MyMod As String 'Model No.Dim MyAmt As String 'AmtDim MyDT As String 'DateDim MyPN As String 'Partner NameDim MyPA As String 'Partner AddressDim MyPC As String 'Partner City

Worksheets("main sheet").Select
Range("A2").Select

Do Until IsEmpty(ActiveCell)

  ShtName = ActiveCell.Value
 
  For Each ws In ActiveWorkbook.Worksheets
  If ws.Name = ShtName Then
  MySht = True
  Exit For
  Else
  MySht = False
  End If
  Next
 
  If MySht = False Then
  MyNum = ActiveCell.Value
  MyCust = ActiveCell.Offset(0, 1).Value
  MyAddr = ActiveCell.Offset(0, 2).Value
  MyCity = ActiveCell.Offset(0, 3).Value
  MyMod = ActiveCell.Offset(0, 4).Value
  MyAmt = ActiveCell.Offset(0, 5).Value
  MyDT = ActiveCell.Offset(0, 6).Value
  MyPN = ActiveCell.Offset(0, 7).Value
  MyPA = ActiveCell.Offset(0, 8).Value
  MyPC = ActiveCell.Offset(0, 9).Value
 
  Worksheets("Template").Copy Before:=Worksheets("Template")
  ActiveSheet.Name = MyNum
 
  Range("C3").Value = MyCust
  Range("C4").Value = MyAddr
  Range("C6").Value = MyCity
  Range("C8").Value = MyMod
  Range("F8").Value = MyNum
  Range("H3").Value = MyPN
  Range("H4").Value = MyPA
  Range("H6").Value = MyPC
  Range("D11").Value = MyAmt
  Range("F11").Value = MyDT
 
  Range("A1").Select
 
  Worksheets("main sheet").Select
  ActiveCell.Offset(1, 0).Select
  Else
  ActiveCell.Offset(1, 0).Select
  End If

Loop


End Sub

 

[Deepak]

Pls always follow forum rules to tag the code.

 

[ushaanu]

Pls always follow forum rules to tag the code.

hi Deepak

I will take care in future .

Regards
Anu

 

[ushaanu]

Pls always follow forum rules to tag the code.

Hi Deepak,

Is any solution for my file.

Regards
Anu

 

[Deepak]

Hi Deepak,

Is any solution for my file.

Regards
Anu

Pls upload the sample workbook also.

 

[ushaanu]

Pls upload the sample workbook also.

Deepak ,
base data in main sheet .

regards
Anu

 

[ushaanu]

Hi deepak ,

I have file(sample22) with macro but in macro create different sheets even number is same . I need data like req sheet

Anu

 

[Deepak]

Hi deepak ,

I have file(sample22) with macro but in macro create different sheets even number is same . I need data like req sheet

Anu

Sorry!! Previous post was for other thread!!
let me check this!!

 

[Deepak]

Hi Anu,

You want to create different sheets based on cells in col A.

But you can't create two sheets with same name, you must have to differentiate them with at least a space.

I didn't understand this part!

like

as45455656-Bn3
as45455656-Bn4
as45455656-Cn3
as45455656-Cn4
dfd343567-B
dfd343567-C

above number are same before - it`s mean number is same for the same number I need one sheet, in template filed given like below

if after number -BN4 so amt is 2340, b stand for black and c stands for color

if there is only number without -Bn4,Bn3,Cn3,Cn4 it`s mean black N4 in template

and 3rd condition if after number-b or number-c it`s stand black n4 and color N4.

 

[ushaanu]

hi Deepak

actually I have 4 unique number in col A ye I need sheets base on col A basically there is 3 types one type only the number Like

abg234567
dfd654667

2nd number then 4 category like :
as45455656-BN3
as45455656-BN4
as45455656-CN3
as45455656-CN4

3 rd category Like:

dfd343567-B
dfd343567-C
now I need 4 sheets like enclose File

Anu

 

[Deepak]

OK.
How the sheet name will be decided upon these three creteria.

 

[ushaanu]

OK.
How the sheet name will be decided upon these three creteria.

name with Number( before -)like as45455656 & Customer first name