Visaual Basic

[gurshish]

Hi All,

I am currently working on to write code for process where in i want to print labels which have data from sheet 2, sheet 2 have three columns one - Name Second- Roll No, Third- Hall no, this data goes upto 1000 rows in sheet 2

What i am trying to do is to get data from sheet 2 to sheet 1 in form of labels, each label contain different Name, Roll No and Hall No.

I am try do it will loop and want control number of label in each page.

Can some one help me to get it right as blown my head in this....

Labels look like below with Outline Border.....
<table style="border-collapse: collapse; width: 192pt;" border="0" cellpadding="0" cellspacing="0" width="256"><col style="width: 48pt;" span="4" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">Name </td> <td class="xl66" style="width: 48pt;" width="64"> </td> <td class="xl66" style="width: 48pt;" width="64"> </td> <td class="xl67" style="width: 48pt;" width="64"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">Roll No </td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">Hall No </td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20"> </td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl70" style="height: 15.75pt;" height="21"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl72"> </td> </tr> </tbody></table>

 

[John Davis]

Hi All,

I am currently working on to write code for process where in i want to print labels which have data from sheet 2, sheet 2 have three columns one - Name Second- Roll No, Third- Hall no, this data goes upto 1000 rows in sheet 2

What i am trying to do is to get data from sheet 2 to sheet 1 in form of labels, each label contain different Name, Roll No and Hall No.

I am try do it will loop and want control number of label in each page.

Can some one help me to get it right as blown my head in this....

Labels look like below with Outline Border.....
<table style="border-collapse: collapse; width: 192pt;" border="0" cellpadding="0" cellspacing="0" width="256"><col style="width: 48pt;" span="4" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">Name </td> <td class="xl66" style="width: 48pt;" width="64"> </td> <td class="xl66" style="width: 48pt;" width="64"> </td> <td class="xl67" style="width: 48pt;" width="64"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">Roll No </td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">Hall No </td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20"> </td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl70" style="height: 15.75pt;" height="21"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl72"> </td> </tr> </tbody></table>

Maybe this can help or give you some ideas??

Sub gurshish()
Dim i As Long
Dim lr As Long
Dim x As Long

lr = Sheets("Sheet2").Cells(Rows.Count, 1).End(3).Row

With Sheets("Sheet2")

x = 1

For i = lr To 2 Step -1

    Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2).Value = x & " " & vbCrLf & Range("A" & i).Value & vbCrLf & Range("B" & i).Value & vbCrLf & Range("C" & i).Value
     
    x = x + 1
    
Next i

End With

End Sub

 

[John Davis]

Hi All,

I am currently working on to write code for process where in i want to print labels which have data from sheet 2, sheet 2 have three columns one - Name Second- Roll No, Third- Hall no, this data goes upto 1000 rows in sheet 2

What i am trying to do is to get data from sheet 2 to sheet 1 in form of labels, each label contain different Name, Roll No and Hall No.

I am try do it will loop and want control number of label in each page.

Can some one help me to get it right as blown my head in this....

Labels look like below with Outline Border.....
<table style="border-collapse: collapse; width: 192pt;" border="0" cellpadding="0" cellspacing="0" width="256"><col style="width: 48pt;" span="4" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">Name </td> <td class="xl66" style="width: 48pt;" width="64"> </td> <td class="xl66" style="width: 48pt;" width="64"> </td> <td class="xl67" style="width: 48pt;" width="64"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">Roll No </td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">Hall No </td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20"> </td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl70" style="height: 15.75pt;" height="21"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl72"> </td> </tr> </tbody></table>

Sorry my first post was not tested, this is better:

Sub gurshish()
Dim i As Long
Dim lr As Long
Dim x As Long

lr = Sheets("Sheet2").Cells(Rows.Count, 1).End(3).Row

With Sheets("Sheet2")

x = 1

For i = lr To 2 Step -1

    Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2).Value = x & " " & vbLf & Range("A" & i).Value & vbLf & Range("B" & i).Value & vbLf & Range("C" & i).Value
     
    x = x + 1
    
Next i

End With

End Sub

 

[gurshish]

Sorry my first post was not tested, this is better:

Sub gurshish()
Dim i As Long
Dim lr As Long
Dim x As Long

lr = Sheets("Sheet2").Cells(Rows.Count, 1).End(3).Row

With Sheets("Sheet2")

x = 1

For i = lr To 2 Step -1

    Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2).Value = x & " " & vbLf & Range("A" & i).Value & vbLf & Range("B" & i).Value & vbLf & Range("C" & i).Value
     
    x = x + 1
    
Next i

End With

End Sub

Its Great help, broaden my horizon. Thanks

But what i am doing right now is

B1 , B2, B3 have Name , Roll Number and hall Number respectively
C1, C2 C3 Have respective value from Sheet 2
There is border from Range B1 :E5

Below code will help u to understand what i am doing
Dim i As Integer
Dim J As Integer
i = 3
J = 1
Z = 6
x = 3
lr = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row - 1

Do
Range(Cells(i, 2), Cells(i, 2).Offset(2, 0)).Value = Application.Transpose(Array("Name", "Roll No", "Hall No"))
Range(Cells(x, 2), Cells(Z, 4)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium

End With
i = i + 7
J = J + 1
x = x + 7
Z = Z + 7

Loop While Sheets(2).Cells(J, "A").Value <> ""
End Sub

It is doing fine what is happening i cannot control my code in Page view for example last label of my Page 1 is divided in to two pages. What i want is it happen macro should print the lable and go next page and start printing again.

 

[John Davis]

Its Great help, broaden my horizon. Thanks

But what i am doing right now is

B1 , B2, B3 have Name , Roll Number and hall Number respectively
C1, C2 C3 Have respective value from Sheet 2
There is border from Range B1 :E5

Below code will help u to understand what i am doing
Dim i As Integer
Dim J As Integer
i = 3
J = 1
Z = 6
x = 3
lr = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row - 1

Do
Range(Cells(i, 2), Cells(i, 2).Offset(2, 0)).Value = Application.Transpose(Array("Name", "Roll No", "Hall No"))
Range(Cells(x, 2), Cells(Z, 4)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium

End With
i = i + 7
J = J + 1
x = x + 7
Z = Z + 7

Loop While Sheets(2).Cells(J, "A").Value <> ""
End Sub

It is doing fine what is happening i cannot control my code in Page view for example last label of my Page 1 is divided in to two pages. What i want is it happen macro should print the lable and go next page and start printing again.

There maybe a way to code it, but the only thing I could suggest for that is to modify either your cell height or page length to accommodate the code.