J
Jerry
My goal:
My first time on VBA. The code will copy the sheet from the workbook where
this code resides to every excel file under a xml folder, format some cells
in it , and then add footer to all the sheets(total 2). the problem is that
only the newly copied sheet in the target file is added footer to. All the
code is attached below. I'd appreciate it!
Public Sub runCleanup()
Dim fs, f, f1, fc
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("Z:\8th_warning\output\xml\")
Set fc = f.Files
Dim unit As String
Dim basebook As Workbook
Dim mybook As Workbook
Set basebook = ThisWorkbook
Application.DisplayAlerts = False
For Each f1 In fc
Set mybook = Workbooks.Open(f1)
mybook.Sheets(1).Activate
unit = Right(Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4), 4)
Call format_this_workbook1(unit)
basebook.Worksheets(1).Copy before:=mybook.Sheets(1) 'copy overview to
each school
ActiveWorkbook.SaveAs Filename:="Z:\8th_warning\output\xls\" & _
Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 3) & "xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
Next
Application.DisplayAlerts = True
End Sub
Private Function format_this_workbook1(unit As String)
Dim FinalRow, FinalCol As Integer
FinalRow = Range("B65536").End(xlUp).Row
FinalCol = Range("A:Q").End(xlToRight).Column - 1
Range("B1").Resize(FinalRow, FinalCol).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
Range("A2:A2").Select
For Each ws In Worksheets
ws.Activate
ActiveSheet.PageSetup.LeftFooterPicture.Filename = "z:\logo\rea_logo_sm2.bmp"
With ActiveSheet.PageSetup
.LeftFooter = "&G" & Chr(10) & "http://research.cps.k12.il.us"
.CenterFooter = "Page &P of &N"
.RightFooter = "Unit " & unit
.Order = xlOverThenDown
.CenterHorizontally = True
.CenterVertically = False
.Zoom = 100
End With
Next ws
Sheets(1).Activate
End Function
My first time on VBA. The code will copy the sheet from the workbook where
this code resides to every excel file under a xml folder, format some cells
in it , and then add footer to all the sheets(total 2). the problem is that
only the newly copied sheet in the target file is added footer to. All the
code is attached below. I'd appreciate it!
Public Sub runCleanup()
Dim fs, f, f1, fc
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("Z:\8th_warning\output\xml\")
Set fc = f.Files
Dim unit As String
Dim basebook As Workbook
Dim mybook As Workbook
Set basebook = ThisWorkbook
Application.DisplayAlerts = False
For Each f1 In fc
Set mybook = Workbooks.Open(f1)
mybook.Sheets(1).Activate
unit = Right(Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4), 4)
Call format_this_workbook1(unit)
basebook.Worksheets(1).Copy before:=mybook.Sheets(1) 'copy overview to
each school
ActiveWorkbook.SaveAs Filename:="Z:\8th_warning\output\xls\" & _
Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 3) & "xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
Next
Application.DisplayAlerts = True
End Sub
Private Function format_this_workbook1(unit As String)
Dim FinalRow, FinalCol As Integer
FinalRow = Range("B65536").End(xlUp).Row
FinalCol = Range("A:Q").End(xlToRight).Column - 1
Range("B1").Resize(FinalRow, FinalCol).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
Range("A2:A2").Select
For Each ws In Worksheets
ws.Activate
ActiveSheet.PageSetup.LeftFooterPicture.Filename = "z:\logo\rea_logo_sm2.bmp"
With ActiveSheet.PageSetup
.LeftFooter = "&G" & Chr(10) & "http://research.cps.k12.il.us"
.CenterFooter = "Page &P of &N"
.RightFooter = "Unit " & unit
.Order = xlOverThenDown
.CenterHorizontally = True
.CenterVertically = False
.Zoom = 100
End With
Next ws
Sheets(1).Activate
End Function