Notifications

Clear all

VBA Refrenence

VBA & Macros

Last Post by Philip Treacy 2 years ago

3 Posts

2 Users

0 Reactions

85 Views

RSS

moshood bello

(@moshoodbello19gmail-com)

Posts: 2

New Member

Topic starter

hello, I'm struggling to make this code work. i need it to copy the address from the the address sheet and also some some information from the EPS sheet and then paste it to the PDF sheet A3. thank you

Sub PDFFormat()
Dim Cell As Long
Dim B As Long
Dim row1 As String
Dim row2 As String
Dim row3a As String
Dim row3 As String
Dim row4 As String
Dim row5 As String
Dim row6 As String
Dim row6b As String

Dim v As Long

Dim sheet As String

Application.ScreenUpdating = False

sheet = ActiveSheet.Name

B = 1

With Sheets(sheet)
'for each cell in range .Range("A1:A" & .Cells(.Rows.count, "A").End(x1up).row)
v = .Cells(.rows.count, "A").End(xlUp).Row

End With
For Cell = 1 To v

Sheets("PDF").Select
row1 = "A" & B & ":" & "F" & B
Range(row1).Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = False
.RowHeight = 15

End With
Selection.Merge
With Selection.Font
.Name = "Consolas"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
.Bold = True

End With
'row configure

row2 = "A" & B + 1 & ":" & "F" & B + 1
Range(row2).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = False
.RowHeight = 40

End With
'Barcodegen the long one
Selection.Merge
With Selection.Font
.Name = "BC 39 Tall HR"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
.Bold = True

End With

row3a = "A" & B + 2 & ":" & "F" & B + 2
Range(row3a).Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = False
.RowHeight = 170

End With

Selection.Merge
With Selection.Font
.Name = "Consolas"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
.Bold = False

End With
'Border setting

Selection.Borders(xlDiagonalDown).LineStyle = x1none
Selection.Borders(xlDiagonalUp).LineStyle = x1none
Selection.Borders(xlEdgeLeft).LineStyle = x1none
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

With Selection.Borders(xlEdgeBottom)
.LineStyle = x1Continuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin

End With
Selection.Borders(xlEdgeRight).LineStyle = x1none
Selection.Borders(xlInsideVertical).LineStyle = x1none
Selection.Borders(xlInsideHorizontal).LineStyle = x1none

row3 = "A" & B + 3 & ":" & "F" & B + 3
Range(row3).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = False
.RowHeight = 8

End With

With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuos
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin

End With

Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

End With

row4 = "A" & B + 4 & ":" & "F" & B + 4
Range(row4).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = True
.RowHeight = 25

End With
'barcode
Selection.Merge
With Selection.Font
.Name = "BC 39 Tall"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = x1ThemeFontNone

End With

row5add = "A" & B + 5 & ":" & "F" & B + 5
Range(row5add).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = False
.RowHeight = 15

End With
Selection.Merge
With Selection.Font
.Name = "Consolas"
.Size = 13
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
.Bold = True

End With

row6 = "D" & B + 6 & ":" & "F" & B + 6
Range(row6).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.RowHeight = 15

End With

Selection.Merge
With Selection.Font
.Name = "Consolas"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
.Bold = True

End With

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
LineStyle = xlContinuous
ColorIndex = 0
TintAndShade = 0
Weight = xlThin

End With

row6b = "A" & B + 6 & ":" & "C" & B + 6
Range(row6b).Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.RowHeight = 15

End With

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

row5 = "A" & B + 7 & ":" & "F" & B + 7
Range(row5).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.RowHeight = 27
Selection.Merge

End With

ActiveWindow.SelectedSheets.HPageBreaks.ADD Before:=ActiveCell

B = B + 7
Next Cell
Selection.Delete
FormatColPDF

Worksheets(sheet).Activate

Sheets("Start").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
Sub Pastesheet2PDF()

Dim Cell As Range
Dim bar As String
Dim one As String
Dim two As String
Dim threeadd As String
Dim three As String
Dim four As String
Dim fiveadd As String
Dim reprtNo As String
Dim sheet As String
Dim CBS As Integer

Dim A As Long
Dim B As Long

Application.ScreenUpdating = False

sheet = ActiveSheet.Name

Sheets("Address").Select
reprtNo = Mid(Range("A1"), 7, 5)

A = 1
B = 1

With Sheets(sheet)
For Each Cell In .Range("A:A" & .Cells(rows.count, "A").End(xlUp).Row)

one = "A" & B & ":" & "A" & B
two = "B" & B & ":" & "B" & B
three = "C" & B & ":" & "C" & B
four = "D" & B & ":" & "D" & B
threeadd = "E" & B & ":" & "E" & B
five = "F" & B & ":" & "F" & B
six = "G" & B & ":" & "G" & B

bar = "A" & A & ":" & "F" & A
.Range(one).Copy Destination:=Worksheets("PDF").Range(bar)
A = A + 1

bar = "A" & A & ":" & "F" & A
.Range(two).Copy Destination:=Worksheets("PDF").Range(bar)
A = A + 1

bar = "A" & A & ":" & "F" & A
.Range(five).Copy Destination:=Worksheets("PDF").Range(bar)

With Worksheets("PDF").Range("A" & A & ":" & "A" & A)
.Characters(1, 12).Font.Bold = True
.Characters(1, 12).Font.Bold = 13
End With

A = A + 1
bar = "A" & A & ":" & "F" & A
.Range(threeadd).Copy Destination:=Worksheets("PDF").Range(bar)
A = A + 1
bar = "A" & A & ":" & "F" & A
.Range(three).Copy Destination:=Worksheets("PDF").Range(bar)
A = A + 1
bar = "A" & A & ":" & "F" & A
.Range(four).Copy Destination:=Worksheets("PDF").Range(bar)
A = A + 1

bar = "A" & A & ":" & "F" & A

Sheets("Start").Select
CBS = Range("A17").Value
If CBS = 1 Then
fiveadd = "CENG/QPD/SAP/" & reprtNo
End If
If CBS = 2 Then
fiveadd = "CIE/QPD/" & reprtNo
End If
If CBS = 3 Then
fiveadd = "OCR/QPD/" & reprtNo
End If

Worksheets("PDF").Range(bar).Value = fiveadd

bar = "A" & A & ":" & "C" & A
.Range(six).Copy Destination:=Worksheets("PDF").Range(bar)

A = A + 1

B = B + 1
Next Cell

'Rows(cell.row).copy destination:=Sheets("")Rows()
'.rows(Cell.Row).ClearContents
'A = A + 1
'Next Cell

End With
Sheets("PDF").Select
Columns("A:D").EntireColumn.AutoFit
Sheets("Start").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Posted : 28/01/2023 8:19 am

moshood bello

(@moshoodbello19gmail-com)

Posts: 2

New Member

Topic starter