example: (refer help10.xls)
http://www.geocities.com/remember_it/help10.xls

Option Explicit
In my job, I often have to printer stickers which show the info of
products. However, I find the traditional way VERY INCONVENIENT and
UNEFFICIENT.

Traditional Flow (of making stickers):
1) extract product info (the format of which is unsuitable for my
company) from the details provided by the supplier
2) enter the extracted product info to my company's checklist sheet
eg. Sheets("checklist")
3) re-enter the product info into my company's sticker sheet
eg. Sheets("sticker")
--------------------------------------------------------------------------------------------------------------------------------
Sub Problem()
Since
Sheets("sticker") 's format < Sheets("checklist") 's format
Therefore
"paste all" CANNOT be used
using "copy and paste" is EXTREMELY INCONVENIENT
'actually, re-key-in the data is EVEN FASTER then "copy and paste"
!
On Error
the info in Sheets("sticker") REQUIRES DOUBLE CHECK
'which is unefficient
End Sub

--------------------------------------------------------------------------------------------------------------------------------
Sub Difficulties()
Since
the LENGTH of product info for EACH sticker in Sheets("sticker") is
DIFFERENT
Therefore
the RANGE of EACH STICKER is DIFFERENT
the sizing and positioning for EACH sticker VARIES
'especially the "content", do not know how to manage its number of
row
End Sub
--------------------------------------------------------------------------------------------------------------------------------
Sub DraftSoln()
If the product info in Sheets("checklist") can transfer to
Sheets("sticker") _
AUTOMATICALLY and INTELLIGENTLY by vba Then
It will be GREAT
End If
End Sub
--------------------------------------------------------------------------------------------------------------------------------
Sub SolnDirection()
Select Case Direction
Case by FORMULA
Sheets("sticker").Cells().value can be QUERIED from
Sheets("checklist").Cells
Difficulties
Case vba1
using vba to fill Sheets("sticker").Cells().value with
CORRESPONDING RECORDS in Sheets("checklist").Cells
Difficulties
Case vba2
using vba to MAKE a new sticker sheet (still using the SAME
format as Sheets("sticker"))
'MAKE (CREATE from zero)
'MAKE...like the path how REPORT is made in MSAccess (CREATE from
data table)
Difficulties
End Select
End Sub
--------------------------------------------------------------------------------------------------------------------------------
Sub Questions()
any good suggestions?
If Case vba2 Then
how to write the vba codes? (it seems complicated..)
***the size of Print_Area in Sheets("sticker") CANNOT be changed!!!
End If
End Sub

I don't think I'd use excel to create the stickers (labels???).

But using excel and word work very nicely:

A couple of links that may help:

http://www.mvps.org/dmcritchie/excel/mailmerg.htm
http://www.mvps.org/word/FAQs/MailMerge

The first is from David McRitchie and the second is by Beth Melton and Dave
Rado.

active_x wrote:

example: (refer help10.xls)
http://www.geocities.com/remember_it/help10.xls

Option Explicit
In my job, I often have to printer stickers which show the info of
products. However, I find the traditional way VERY INCONVENIENT and
UNEFFICIENT.

Traditional Flow (of making stickers):
1) extract product info (the format of which is unsuitable for my
company) from the details provided by the supplier
2) enter the extracted product info to my company's checklist sheet
eg. Sheets("checklist")
3) re-enter the product info into my company's sticker sheet
eg. Sheets("sticker")
--------------------------------------------------------------------------------------------------------------------------------
Sub Problem()
Since
Sheets("sticker") 's format < Sheets("checklist") 's format
Therefore
"paste all" CANNOT be used
using "copy and paste" is EXTREMELY INCONVENIENT
'actually, re-key-in the data is EVEN FASTER then "copy and paste"
!
On Error
the info in Sheets("sticker") REQUIRES DOUBLE CHECK
'which is unefficient
End Sub

--------------------------------------------------------------------------------------------------------------------------------
Sub Difficulties()
Since
the LENGTH of product info for EACH sticker in Sheets("sticker") is
DIFFERENT
Therefore
the RANGE of EACH STICKER is DIFFERENT
the sizing and positioning for EACH sticker VARIES
'especially the "content", do not know how to manage its number of
row
End Sub
--------------------------------------------------------------------------------------------------------------------------------
Sub DraftSoln()
If the product info in Sheets("checklist") can transfer to
Sheets("sticker") _
AUTOMATICALLY and INTELLIGENTLY by vba Then
It will be GREAT
End If
End Sub
--------------------------------------------------------------------------------------------------------------------------------
Sub SolnDirection()
Select Case Direction
Case by FORMULA
Sheets("sticker").Cells().value can be QUERIED from
Sheets("checklist").Cells
Difficulties
Case vba1
using vba to fill Sheets("sticker").Cells().value with
CORRESPONDING RECORDS in Sheets("checklist").Cells
Difficulties
Case vba2
using vba to MAKE a new sticker sheet (still using the SAME
format as Sheets("sticker"))
'MAKE (CREATE from zero)
'MAKE...like the path how REPORT is made in MSAccess (CREATE from
data table)
Difficulties
End Select
End Sub
--------------------------------------------------------------------------------------------------------------------------------
Sub Questions()
any good suggestions?
If Case vba2 Then
how to write the vba codes? (it seems complicated..)
***the size of Print_Area in Sheets("sticker") CANNOT be changed!!!
End If
End Sub

--

Dave Peterson

example: (refer help12.xls)
//if the hyperlink does not work,
//TYPE(NOT CLICK) this url DIRECTLY IN THE BROWSER :
http://www.geocities.com/remember_it/help12.zip

Can this macro"Sub mksticker()" be re-written in another way? (question
1)

Can this macro"Sub mksticker()" be simplified? (question 2)

File Attached: http://www.exceltip.com/forum/attach...?postid=278446 (help12.zip)

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/