Change size when is pasting

[ABF]

I'm trying to copy info from one file and paste to another file, but when it pastes, it changes the font size. I'm still learning VBA so I'm probably missing a something...

'''
Sub Sites()
Application.ScreenUpdating = False

Dim InputFile As Workbook
Dim OutputFile As Workbook
Dim FormulasFile As Workbook
Dim Inputpath As String
Dim Outputpath As String
Dim Formulaspath As String
Dim lRow As Long
Dim ws As Worksheet

'Set path for files
fileInputpath = "C:\Users\Workbooks\"
Outputpath = "C:\Users\Workbooks\"
Formulaspath = "C:\Users\Workbooks\"

'Open workbooks first
Set InputFile = Workbooks.Open(Inputpath & "C:\Users\Workbooks\Weekly data.xlsx")
Set OutputFile = Workbooks.Open(Outputpath & "Compiled data.xlsx")
Set FormulasFile = Workbooks.Open(Formulaspath & "Formulas Pivot data.xlsx", UpdateLinks:=False)

'Now, copy what you want from InputFile and paste to OutputFile/FormulasFile worksheet
With InputFile.Sheets("Report")
lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Range("C3:O" & lRow).Copy OutputFile.Sheets("Sheet1").Cells(OutputFile.Sheets("Sheet1").Rows.Count, "A").End(xlUp).Offset(1)

'****This part is not working
For Each ws In Worksheets
With ws
.Cells.Font.Name = "Calibri"
.Cells.Font.Size = 9
End With
Next ws

End With


'this part is working fine
With InputFile.Sheets("Report")
lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Range("C3:O" & lRow).Copy FormulasFile.Sheets("Site").Cells(FormulasFile.Sheets("Site").Rows.Count, "O").End(xlUp).Offset(1)

For Each ws In Worksheets
With ws
.Cells.Font.Name = "Calibri"
.Cells.Font.Size = 9
End With
Next ws

End With

'Close all files without display alerts
Application.DisplayAlerts = False
InputFile.Close False
OutputFile.Close True
FormulasFile.Close True
Application.ScreenUpdating = True

End Sub

 

[Domenic]

With this line of code...

For Each ws In Worksheets

... Worksheets refers to the active workbook, since you haven't qualified your reference. To refer to OutputFile, change it as follows...

For Each ws In OutputFile.Worksheets

Note, though, copy/paste should copy the font and font size.

 

[ABF]

With this line of code...

For Each ws In Worksheets

... Worksheets refers to the active workbook, since you haven't qualified your reference. To refer to OutputFile, change it as follows...

For Each ws In OutputFile.Worksheets

Note, though, copy/paste should copy the font and font size.

Thank you! I got it now!