CODE 128 VBA

Public Function copyformat()
'copy the format of a1:a6
Sheets("sheet2").Activate
Range("a1:e6").Select
Dim v As Integer
For I = 1 To 499
v = I * 6 + 1
Range("a1:e6").Copy Cells(v, 1)

'insert the hpagebreaker
Cells(v, 1).Activate
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell

'insert formula to cells
''''Range(Cells(v + 2, 3), Cells(v + 5, 5)).ClearContents
Cells((v + 3), 2).Formula = "=sheet1!$D" & (I + 2)
'''Cells((v + 3), 3).Formula = "=sheet1!$d" & (i + 2)
Cells((v + 5), 2).Formula = "=sheet1!$c" & (I + 2)
'''Cells((v + 5), 3).Formula = "=sheet1!$c" & (i + 2)
Next
Cells(4, 2).Formula = "=sheet1!$D2"
Cells(6, 2).Formula = "=sheet1!$C2"
End Function

Public Function StringToCode128()
Dim PrintableString, CurrentValue, CurrentCharNum, CheckDigitValue, DataToEncode, WeightedTotal
Dim StringLength

For k = 1 To 500
Sheets("sheet1").Activate
DataToEncode = RTrim(LTrim(Cells(k + 1, 4)))

'Set check_total to the value of the start character
PrintableString = ""
  WeightedTotal = 104
  PrintableString = ChrW(204)
  StringLength = Len(DataToEncode)
'calculate the total of the datatoencode
  For I = 1 To StringLength
   
          CurrentCharNum = AscW(Mid(DataToEncode, I, 1))
          If CurrentCharNum < 135 Then CurrentValue = CurrentCharNum - 32
          If CurrentCharNum > 134 Then CurrentValue = CurrentCharNum - 100
          CurrentValue = CurrentValue * I
          WeightedTotal = WeightedTotal + CurrentValue
          If CurrentCharNum = 32 Then CurrentCharNum = 194
          PrintableString = PrintableString & ChrW(CurrentCharNum)
  Next
'calc the checkdigit of the datatoencode
  CheckDigitValue = (WeightedTotal Mod 103)
  If CheckDigitValue < 95 Then C128CheckDigit = ChrW(CheckDigitValue + 32)
  If CheckDigitValue > 94 Then C128CheckDigit = ChrW(CheckDigitValue + 100)
  PrintableString = PrintableString + C128CheckDigit + ChrW(206)

Sheets("sheet2").Activate
Cells(k * 6 - 3, 1) = PrintableString
Next k

For k = 1 To 500
Sheets("sheet1").Activate
DataToEncode = RTrim(LTrim(Cells(k + 1, 3)))
'code 128 character set B
PrintableString = ""
  WeightedTotal = 104
  PrintableString = ChrW(204)
  StringLength = Len(DataToEncode)
   
  For I = 1 To StringLength
   
          CurrentCharNum = AscW(Mid(DataToEncode, I, 1))
          If CurrentCharNum < 135 Then CurrentValue = CurrentCharNum - 32
          If CurrentCharNum > 134 Then CurrentValue = CurrentCharNum - 100
          CurrentValue = CurrentValue * I
          WeightedTotal = WeightedTotal + CurrentValue
          If CurrentCharNum = 32 Then CurrentCharNum = 194
          PrintableString = PrintableString & ChrW(CurrentCharNum)
  Next
   
  CheckDigitValue = (WeightedTotal Mod 103)
   
  If CheckDigitValue < 95 Then C128CheckDigit = ChrW(CheckDigitValue + 32)
  If CheckDigitValue > 94 Then C128CheckDigit = ChrW(CheckDigitValue + 100)
  PrintableString = PrintableString + C128CheckDigit + ChrW(206)

Sheets("sheet2").Activate
Cells(k * 6 - 1, 1) = PrintableString
Next k

End Function