|
Sub Macro1() ‘復(fù)制工作表到多個(gè)工作薄中 Dim MyPath$, MyName$, sh As Worksheet
' Application.ScreenUpdating = False '關(guān)閉屏幕更新 Application.DisplayAlerts = False '關(guān)閉警告信息
Set sh = Worksheets(1)
MyPath = ThisWorkbook.Path
MyName = Dir(MyPath & "\*.xls") On Error Resume Next
Do While MyName <> "" If MyName <> ThisWorkbook.Name Then
m = m + 1 Cells.Select Selection.Copy
Workbooks.Open ThisWorkbook.Path & "\" & MyName
ActiveWorkbook.Sheets("封面").Select
ActiveSheet.Paste Destination:=Worksheets("封面").Cells ActiveSheet.Name = "行政事業(yè)性項(xiàng)目和專項(xiàng)資金績(jī)效目標(biāo)表" Application.CutCopyMode = False Range(a1).selece ActiveWorkbook.Save ActiveWorkbook.Close 1 End If MyName = Dir
Loop
' Application.ScreenUpdating = True Application.DisplayAlerts = True
MsgBox "處理完畢，共處理" & m & "個(gè)工作簿"
End Sub
Sub gs() ‘復(fù)制工作表中公式到多個(gè)工作薄中
Dim MyPath$, MyName$
' Application.ScreenUpdating = False '關(guān)閉屏幕更新
Application.DisplayAlerts = False '關(guān)閉警告信息 MyPath = ThisWorkbook.Path
MyName = Dir(MyPath & "\*.xls") On Error Resume Next
Do While MyName <> "" If MyName <> ThisWorkbook.Name Then
m = m + 1 Range("c6").Select Selection.Copy
Workbooks.Open ThisWorkbook.Path & "\" & MyName
Windows(" MyName").Activate Sheets("部門(mén)一般公共預(yù)算經(jīng)濟(jì)分類支出表").Select Range("c6").Select ActiveSheet.Paste
ActiveWorkbook.Save ActiveWorksheet.Paste Application.CutCopyMode = False ActiveWorkbook.Save ActiveWorkbook.Close ' sh.Copy after:=Sheets(10)
' ThisWorkbook.Close True
End If MyName = Dir
Loop
' Application.ScreenUpdating = True Application.DisplayAlerts = True
MsgBox "處理完畢，共處理" & m & "個(gè)工作簿"
End Sub
用VBA設(shè)置列寬和行高 如果要用VBA來(lái)設(shè)置區(qū)域的行高和列寬，可以用Range.ColumnWidth 屬性和Range.RowHeight 屬性，例如下例將選定的區(qū)域中各單元格的列寬和行高調(diào)整為指定的數(shù)值： Sub SetColumnAndRow() 如果要將選定區(qū)域內(nèi)的各單元格的列寬和行高調(diào)整為最合適的值，可以用下面的代碼： Sub SetColumnAndRow() 下面的代碼將活動(dòng)工作表中的所有單元格的行高和列寬恢復(fù)為默認(rèn)值： Sub SetDefault()
Sub 批量修改多個(gè)工作薄中工作表() Dim myname$, i As Worksheet Application.DisplayAlerts = False On Error Resume Next a = Timer myname = Dir(ThisWorkbook.Path & "\*.xls") Do While myname <> "" If myname = ThisWorkbook.Name Then GoTo 123 End If Workbooks.Open ThisWorkbook.Path & "\" & myname
For Each i In Worksheets i.Activate Select Case i.Name Case Is = "Z01 收入支出決算批復(fù)表(財(cái)決批復(fù)01表)" ActiveWorkbook.Sheets("Z01 收入支出決算批復(fù)表(財(cái)決批復(fù)01表)").Select
ActiveSheet.Name = "1、收支決算總表" Range("c1") = "收支決算總表" Range("f2") = "公開(kāi)1表" [36：50] = "" Range("a36") = " 注：本表反映部門(mén)本年度的總收支和年末結(jié)轉(zhuǎn)結(jié)余情況。 "
Case Is = "Z03 收入決算批復(fù)表(財(cái)決批復(fù)02表)" ActiveWorkbook.Sheets("Z03 收入決算批復(fù)表(財(cái)決批復(fù)02表)").Select ActiveSheet.Name = "2、收入決算表" Range("g1") = "收入決算表" Range("k2") = "公開(kāi)2表" Range("a200").End(xlUp)(-2, 1).Resize(6, 7).Value = ""
Range("a200").End(xlUp)(2.1) = "注：本表反映部門(mén)本年度取得的各項(xiàng)收入情況。"
Case Is = "Z04 支出決算批復(fù)表(財(cái)決批復(fù)03表)" ActiveWorkbook.Sheets("Z04 支出決算批復(fù)表(財(cái)決批復(fù)03表)").Select ActiveSheet.Name = "3、支出決算表" Range("f1") = "支出決算表" Range("j2") = "公開(kāi)3表" Range("a200").End(xlUp)(-2, 1).Resize(6, 7).Value = "" Range("a200").End(xlUp)(2.1) = "注：1.本表反映部門(mén)本年度各項(xiàng)支出情況。"
Case Is = "Z01_1 財(cái)政撥款收入支出決算批復(fù)表(財(cái)決批復(fù)04表)"
ActiveWorkbook.Sheets("Z01_1 財(cái)政撥款收入支出決算批復(fù)表(財(cái)決批復(fù)04表)").Select ActiveSheet.Name = "4、財(cái)政撥款收入支出決算表" Range("d1") = " 財(cái)政撥款收入支出決算表" Range("h2") = "公開(kāi)4表" Range("a200").End(xlUp)(0, 1).Resize(7, 10).Select Range("a200").End(xlUp)(0, 1).Resize(6, 7).Value = "" Range("a200").End(xlUp)(3.1) = "注：本表反映部門(mén)本年度一般公共預(yù)算財(cái)政撥款和政府性基金預(yù)算財(cái)政撥款的總收支和年末結(jié)轉(zhuǎn)結(jié)余情況。" Case Is = "Z07 一般公共預(yù)算財(cái)政撥款收入支出決算批復(fù)表(財(cái)決批復(fù)05表" ActiveWorkbook.Sheets("Z07 一般公共預(yù)算財(cái)政撥款收入支出決算批復(fù)表(財(cái)決批復(fù)05表").Select ActiveSheet.Name = "5、一般公共預(yù)算財(cái)政撥款支出決算表" Range("j1") = "一般公共預(yù)算財(cái)政撥款支出決算表" Range("q2") = "公開(kāi)5表" Range("a200").End(xlUp)(-1, 1).Resize(7, 10).Select Range("a200").End(xlUp)(-1, 1).Resize(7, 10).Value = "" Range("a200").End(xlUp)(3.1) = "注：本表反映部門(mén)本年度一般公共預(yù)算財(cái)政撥款支出情況。" Case Is = "Z08_1 一般公共預(yù)算財(cái)政撥款基本支出決算批復(fù)表(財(cái)決批復(fù)0" ActiveSheet.Name = "6、一般公共預(yù)算財(cái)政撥款基本支出決算表" Range("e1") = "一般公共預(yù)算財(cái)政撥款基本支出決算表" Range("i2") = "公開(kāi)6表" Range("a200").End(xlUp)(0, 1).Resize(7, 10).Select Range("a200").End(xlUp)(0, 1).Resize(7, 10).Value = "" Range("a200").End(xlUp)(3.1) = "注：本表反映部門(mén)本年度一般公共預(yù)算財(cái)政撥款基本支出明細(xì)情況。" Case Is = "Z09 政府性基金預(yù)算財(cái)政撥款收入支出決算批復(fù)表(財(cái)決批復(fù)07" ActiveWorkbook.Sheets("Z09 政府性基金預(yù)算財(cái)政撥款收入支出決算批復(fù)表(財(cái)決批復(fù)07").Select ActiveSheet.Name = "7、政府性基金收支決算表" Range("j1") = "政府性基金收支決算表" Range("q2") = "公開(kāi)7表" Range("a200").End(xlUp)(-1, 1).Resize(7, 10).Select Range("a200").End(xlUp)(-1, 1).Resize(7, 10).Value = "" Range("a200").End(xlUp)(3.1) = "注：本表反映部門(mén)本年度政府性基金預(yù)算財(cái)政撥款收入、支出及結(jié)轉(zhuǎn)和結(jié)余情況。" Range("a200").End(xlUp)(2.1) = "說(shuō)明：本單位沒(méi)有政府性基金收入，也沒(méi)有使用政府性基金安排的支出，故本表無(wú)數(shù)據(jù)。"
End Select
Next i n = n + 1 ActiveWorkbook.Worksheets.Add after:=Worksheets("7、政府性基金收支決算表") ActiveSheet.Name = "8、一般公共預(yù)算財(cái)政撥款“三公”經(jīng)費(fèi)支出決算表" Set x = ThisWorkbook.Worksheets("8、一般公共預(yù)算財(cái)政撥款“三公”經(jīng)費(fèi)支出決算表").Range("a1:l10") Set y = ActiveWorkbook.Worksheets("8、一般公共預(yù)算財(cái)政撥款“三公”經(jīng)費(fèi)支出決算表").Range("a1") x.Copy y
'ActiveWorkbook.Save ActiveWorkbook.Close 1
123: myname = Dir
Loop Application.DisplayAlerts = True MsgBox Format(Timer - a, "0.0000")
End Sub |
|
|
