Change size when is pasting

ABF

New Member
Joined
Oct 1, 2020
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,383
With this line of code...

VBA 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...

VBA Code:
For Each ws In OutputFile.Worksheets

Note, though, copy/paste should copy the font and font size.
 
  • Like
Reactions: ABF

ABF

New Member
Joined
Oct 1, 2020
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
With this line of code...

VBA 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...

VBA Code:
For Each ws In OutputFile.Worksheets

Note, though, copy/paste should copy the font and font size.
Thank you! I got it now!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,236
Messages
5,546,664
Members
410,753
Latest member
Ian R
Top