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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top