Sub 稅控導(dǎo)出()
'增值稅發(fā)票稅控開票軟件（金稅盤版）V2.1.30.161225
'報(bào)稅處理->報(bào)稅管理->發(fā)票數(shù)據(jù)導(dǎo)出->發(fā)票數(shù)據(jù)導(dǎo)出
'V1.00
'刪除導(dǎo)出文件頂部標(biāo)題及空白行
'刪除不必要的列
'添加發(fā)票類別列
'添加價(jià)稅合計(jì)列，并賦值
'填充發(fā)票類別單元格：專用發(fā)票，普通發(fā)票
'設(shè)置文件字體：宋體；字號(hào)：9號(hào)；居中方式：水平靠左；
'數(shù)字類型單元格由文本格式轉(zhuǎn)換為會(huì)計(jì)專用格式
'數(shù)量：小數(shù)點(diǎn)后4位有效數(shù)字
'金額、稅額、價(jià)稅合計(jì)：小數(shù)點(diǎn)后2位有效數(shù)字
'向下填充非數(shù)字類型數(shù)據(jù)：發(fā)票類別、發(fā)票代碼、發(fā)票號(hào)碼、購(gòu)方企業(yè)名稱、開票日期
'2017-03-28第一次編輯
'
'
Cells.Select '選取A列
With Selection
.HorizontalAlignment = xlLeft '水平居左
.VerticalAlignment = xlCenter '垂直居中
.WrapText = False '取消自動(dòng)換行
.Orientation = 0 '設(shè)置文本方向0度
.AddIndent = False '取消自動(dòng)縮進(jìn)
.IndentLevel = 0 '縮進(jìn)量0
.ShrinkToFit = False '取消文本自動(dòng)收縮以適應(yīng)列寬
.ReadingOrder = xlContext '文字方向根據(jù)內(nèi)容設(shè)置
.MergeCells = False '取消合并單元格
End With
With Selection.Font
.Name = "宋體"
.Size = 9
.Bold = False
End With
Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
Range("A1").Select '選取A1單元格
Selection.EntireColumn.Insert '在A1前插入空白列
Rows("1:4").Select
Selection.Delete
'查找專用發(fā)票、普通發(fā)票
With Range("B:B")
Set c = .Find("發(fā)票類別：專用發(fā)票", LookIn:=xlValues, LookAt:=xlPart)
If Not c Is Nothing Then
c.Offset(0, -1).Value = "刪除"
c.Offset(1, -1).Value = "刪除"
c.Offset(2, -1).Value = "專用發(fā)票"
End If
End With
With Range("B:B")
Set d = .Find("發(fā)票類別：普通發(fā)票", LookIn:=xlValues, LookAt:=xlPart)
If Not d Is Nothing Then
d.Offset(0, -1).Value = "刪除"
d.Offset(1, -1).Value = "刪除"
d.Offset(2, -1).Value = "普通發(fā)票"
End If
End With
Range("b1").Select
With Range("B:B")
Set e = .Find("份數(shù)", LookIn:=xlValues, LookAt:=xlPart)
If Not e Is Nothing Then
firstaddress = e.Address
Do
e.Offset(-1, -1).Value = "刪除"
e.Offset(0, -1).Value = "刪除"
e.Offset(1, -1).Value = "刪除"
Set e = .FindNext(e)
Loop While Not e Is Nothing And e.Address <> firstaddress
End If
End With
Range("A1").Select '選取A1單元格
Selection.EntireRow.Insert
Range("a1").Value = "發(fā)票類型"
Range("b1").Value = "發(fā)票代碼"
Range("c1").Value = "發(fā)票號(hào)碼"
Range("d1").Value = "購(gòu)方企業(yè)名稱"
Range("e1").Value = "購(gòu)方稅號(hào)"
Range("f1").Value = "銀行賬號(hào)"
Range("g1").Value = "地址電話"
Range("h1").Value = "開票日期"
Range("i1").Value = "商品編碼版本號(hào)"
Range("j1").Value = "單據(jù)號(hào)"
Range("k1").Value = "商品名稱"
Range("l1").Value = "規(guī)格"
Range("m1").Value = "單位"
Range("n1").Value = "數(shù)量"
Range("o1").Value = "單價(jià)"
Range("p1").Value = "金額"
Range("q1").Value = "稅率"
Range("r1").Value = "稅額"
Range("s1").Value = "稅收分類編碼"
Columns("A:S").Select
Selection.AutoFilter
Range("$A$1:$S$" & Range("a65536").End(xlUp).Row).AutoFilter Field:=1, Criteria1:="刪除"
Rows("2:" & Range("a65536").End(xlUp).Row).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Columns("A:S").Select
Selection.AutoFilter
Range("$A$1:$S$" & Range("r65536").End(xlUp).Row).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("$A$1:$S$" & Range("a65536").End(xlUp).Row).AutoFilter Field:=11, Criteria1:="小計(jì)"
Rows("2:" & Range("a65536").End(xlUp).Row).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Range("E:G,I:J,L:M,O:O,Q:Q,S:S").Select
Selection.Delete Shift:=xlToLeft
Range("E:E").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 5), TrailingMinusNumbers:=True
Selection.NumberFormatLocal = "yyyy-mm-dd"
Range("G:I").Select
Cells.Replace What:=".", Replacement:=".", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Columns("G:G").Select
Selection.NumberFormatLocal = "_ * #,##0.0000_ ;_ * -#,##0.0000_ ;_ * ""-""????_ ;_ @_ "
Range("H:I").Select
Selection.NumberFormatLocal = "_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * ""-""??_ ;_ @_ "
'需要四舍五入時(shí)的代碼
' Dim x
' For Each x In Range("G:G")
' If VBA.IsNumeric(x) And x <> "" Then
' x.Value = Round(x, 4)
' x.NumberFormatLocal = "_ * #,##0.0000_ ;_ * -#,##0.0000_ ;_ * ""-""????_ ;_ @_ "
' End If
' Next
'
'
' Dim z
' For Each z In Range("H:I")
' If VBA.IsNumeric(z) And z <> "" Then
' z.Value = Round(z, 2)
' z.NumberFormatLocal = "_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * ""-""??_ ;_ @_ "
' End If
' Next
Range("J1").Value = "價(jià)稅合計(jì)"
Range("$J$2:$J$" & Range("a65536").End(xlUp).Row).Select
Selection.FormulaR1C1 = "=RC[-1]+RC[-2]"
Selection.NumberFormatLocal = "_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * ""-""??_ ;_ @_ "
Columns("E:E").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True
Columns("A:J").Select
Selection.AutoFilter
Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
Range("B2").Select
ActiveWindow.FreezePanes = True
End Sub
