|
- Sub hanzhen2()
- 'Sheets("客户信息表").Select
- '有ThisWorkbook中的工作簿事件,此处无须再选中工作表.否则必须保证"客户信息表"为活动工作表该程序才能执行.
- Dim UserRange As Range
- On Error GoTo Canceled
- Set UserRange = Application.InputBox _
- (Prompt:="请选择客户:", _
- Title:="提示:", _
- Type:=8)
- With Sheets("客户信息表")
- TEMPbianhao = .Cells(UserRange.Row, 1).Value
- TEMPmingcheng1 = .Cells(UserRange.Row, 3).Value
- TEMPqian = .Cells(UserRange.Row, 4).Value
- TEMPshe = .Cells(UserRange.Row, 6).Value
- TEMPshe2 = .Cells(UserRange.Row, 7).Value
- TEMPshe3 = .Cells(UserRange.Row, 5).Value
- TEMPUNIT = .Cells(UserRange.Row, 9).Value
- str4 = .Cells(UserRange.Row, 4).NumberFormatLocal
- str5 = .Cells(UserRange.Row, 5).NumberFormatLocal
- str6 = .Cells(UserRange.Row, 6).NumberFormatLocal
- str7 = .Cells(UserRange.Row, 7).NumberFormatLocal
- End With
- If Sheets("客户信息表").Cells(UserRange.Row, 2).Value = "√" Then
- Set mySheet = Worksheets.Add(Type:=xlWorksheet, after:=Sheets(Sheets.Count))
- Sheets("企业询证函模板").Select
- Cells.Select
- Selection.Copy
- mySheet.Select
- Cells.Select
- Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
- False, Transpose:=False
- With mySheet
- .Name = TEMPmingcheng1
- .Range("A3").FormulaR1C1 = "致:" & TEMPmingcheng1
- .Range("E2").FormulaR1C1 = "编号:" & "OOO" & TEMPbianhao
- .Range("B13").FormulaR1C1 = "截止日期:" & "2012年12月31日"
- .Range("C14").FormulaR1C1 = TEMPqian
- .Range("C14").NumberFormatLocal = str4
- .Range("E15").FormulaR1C1 = TEMPshe
- .Range("E15").NumberFormatLocal = str6
- .Range("E14").FormulaR1C1 = TEMPshe2
- .Range("E14").NumberFormatLocal = str7
- .Range("C15").FormulaR1C1 = TEMPshe3
- .Range("C15").NumberFormatLocal = str5
- .Range("D21").FormulaR1C1 = TEMPUNIT
- End With
- Else: MsgBox Prompt:="此客户无需函证!", Title:="友情提示:"
- End If
- mySheet.PrintPreview
- Sheets("客户信息表").Select
- Canceled:
- End Sub
复制代码 |
|