need macro for excel file

[ushaanu]

hi all,

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 .

Sample sheet enclosed .

thanks in advance

Anu

 

[ushaanu]

hi all,

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 .

Sample sheet enclosed .

thanks in advance

Anu

 

[chirayu]

Please check. Uses Template sheet

Sub SheetMaker()

Dim ShtName As String 'Sheet Name
Dim MySht As String 'True/False
Dim ws As Worksheet

Dim MyNum As String 'Number
Dim MyCust As String 'Customer
Dim MyAddr As String 'Address
Dim MyCity As String 'City
Dim MyMod As String 'Model No.
Dim MyAmt As String 'Amt
Dim MyDT As String 'Date
Dim MyPN As String 'Partner Name
Dim MyPA As String 'Partner Address
Dim 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

 

[ushaanu]

hi chirayu,

Thanks for you reply
but it`s not working my file
actually I have only main data sheet now I need thru the main data create number sheets in VERIFICATION format.

Anu

 

[chirayu]

Add the Template sheet in your file. Macro copies the template & renames it & uses information from main sheet to update. attached sample

 

[ushaanu]

thanks a lot

one thing I need in this file like Number
as45455656-Bn3
as45455656-Bn4
as45455656-Cn3
as45455656-Cn4

I need this data in single sheet as as45455656 is same req sheet like enclosed file

 

[ushaanu]

Add the Template sheet in your file. Macro copies the template & renames it & uses information from main sheet to update. attached sample

hi Chirayu,

it1s working fine , one thing I need I Number some 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

as45455656
CounterufgAmt
BlackN42340
N37678
Total B10018
ColorN44573
N39870