TechyCanuck
New Member
- Joined
- Nov 25, 2021
- Messages
- 1
- Office Version
- 2019
- Platform
- Windows
I have an existing macro that automatically exports multiple tabs to individual sheets and names them according to the tab name. I need to add print area and print to landscape information as it is not doing that when I format my original. I tried adding the code here but it didn't work. Maybe I'm putting it in the wrong spot.
Public Sub printArea()
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.printArea = "$A$1:$N$21"
.FitToPagesWide = 1
.FitToPagesTall = 0
End With
Application.PrintCommunication = True
End Sub
Following is my existing code. Any help is appreciated.
Public Sub printArea()
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.printArea = "$A$1:$N$21"
.FitToPagesWide = 1
.FitToPagesTall = 0
End With
Application.PrintCommunication = True
End Sub
Following is my existing code. Any help is appreciated.
VBA Code:
Sub split_to_seperate_sheets()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
MyFilePath$ = ActiveWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
With Application
.ScreenUpdating = False
.DisplayAlerts = False
' End With
On Error Resume Next '<< a folder exists
MkDir MyFilePath '<< create a folder
For N = 1 To Sheets.Count
Sheets(N).Activate
SheetName = ActiveSheet.Name
Cells.Copy
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
With .ActiveSheet
.Paste
.Name = SheetName
[A1].Select
End With
'save book in this folder
.SaveAs FileName:="C:\Seasonal Brochures\Price Pages\2022 May\" & SheetName & ".xlsx"
.Close SaveChanges:=True
End With
.CutCopyMode = False
Next
End With
Sheet1.Activate
End Sub