vba help - merge two range data into PDF printable area

[Mallesh23]

Hi Team,

Need your help, I want to print excel data into pdf. all data is not coming in printable area. I want all data to club.

I want both the data range into PDF.

IF Possible can we increase column width of First range to match with second data set.


This is my below data and attached snapshot.

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2
3	Amounts in USD
4
5	Year	Total		Y1		Y2		Y3		Y4		Y5
6	HEADERS	AAA	xxx	AAA	xxx	AAA	xxx	AAA	xxx	AAA	xxx	AAA	xxx
7	Amount	21000	1000		2000		3000		4000		5000		6000
8
9
10
11	5 Years Data Cumulative
12
13		Total		AUD		CNY		EUR		GBP		HKD		JPY		OTH		SGD		USD
14		aaa	xxx	aaa	xxx	aaa	xxx	aaa	xxx	aaa	xxx	aaa	xxx	aaa	xxx	aaa	xxx	aaa	xxx	aaa	xxx
15			5400		200		300		400		500		600		700		800		900		1000
16
17
Sheet1

Found One code on google.. it is removing borders.

Sub PrintArea()

Application.ScreenUpdating = False

Dim ws As Worksheet


With ActiveSheet
   If .VPageBreaks.Count > 0 Then
      .VPageBreaks(1).Delete
   End If
End With


For Each ws In ActiveWorkbook.Worksheets

    Select Case ws.Index

        Case Is = 1, 2, 3, 4, 5, 6 'only perform action on first 6 sheets

            'If ws.Range("A25") = "Yes" Then 'adjust to desired range and indicator

                With ws.PageSetup
                    .PrintArea = ws.Range("A1:u17").Address
                    .Orientation = xlLandscape
                    .FitToPagesWide = 1
                    .FitToPagesTall = 1
                End With

                'set print area to only 1 page
                With ws
                    .Activate 'need to activate sheet to view Page Break Preview
                    ActiveWindow.View = xlPageBreakPreview 'need to view Page Break Preview to drag page breaks
                    .VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1 'drag page over so it's just one page
                    ActiveWindow.View = xlNormalView 'set view back to normal
                End With

           ' End If

    End Select

Next ws


End Sub

Thanks
mg

 

[Mallesh23]

Hi Team,

Below line of code is stretching , and bringing all data into printable.
.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1

But borders are not showing in printable area.

When I clicked on print preview , it is showing like this it removed border lines from middles.

Thanks
mg