2,462
社区成员
发帖
与我相关
我的任务
分享
Sub ForgeMulFilesToORGWithParameter(ORGid)
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
Range("a1").Select
ActiveCell.FormulaR1C1 = "机构号"
Range("B1").Select
ActiveCell.FormulaR1C1 = "其他字段"
Range("a2").Select
ActiveCell.FormulaR1C1 = ORGid
Range("A3").Select
ActiveSheet.Paste
ActiveSheet.Range("A3:B78").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
ActiveSheet.Range("A1:B2"), Unique:=False
ChDir "E:\VBA\testdir"
StrDate = Format(Date, "yyyymmdd")
StrTime = Format(Time, "hhmmss")
StrFilename = "E:\VBA\testdir\" & ORGid & ".xlsx"
ActiveWorkbook.SaveAs Filename:=StrFilename, FileFormat _
:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Save
End Sub
'用这个按钮事件过程调用以上函数对于单次调用并生成文件可以成功
Private Sub CmdBtnNewFileOrg_Click()
Call ForgeMulFilesToORGWithParameter("abc100669")
End Sub
'用这样的方法调用,则文件abc100669.xlsx的内容可以成功,但是abc988145.xlsx文件的内容无法成功:
Private Sub CmdBtnNewFileOrg_Click()
Call ForgeMulFilesToORGWithParameter("abc100669")
Call ForgeMulFilesToORGWithParameter("abc988145")
End Sub
Sub Test()
''这段代码放在目标表格的vba里
Dim Sht As Worksheet, i As Long, iStart As Long, iEnd As Long, w1 As String
Dim iLast As Long, Wbk As Workbook
Set Sht = ActiveSheet
With Sht
''对原始数据进行排序 999个数据
.Range("a2:b1000").Sort Key1:=.Range("A2")
''确定最后一行
iLast = .Range("a1").End(xlDown).Row
''开始逐渐创建工作簿
For i = 2 To iLast + 1
If w1 = "" Then
w1 = .Range("a" & i)
iStart = i
If w1 = "" Then Exit For
Else
If w1 <> .Range("a" & i) Then
''开始创建
Set Wbk = Application.Workbooks.Add
If Wbk.Worksheets.Count < 1 Then Wbk.Worksheets.Add
''复制数据
.Range("a" & iStart & ":b" & iEnd).Copy Wbk.Worksheets(1).Range("a2")
''复制标题
.Range("a1:b1").Copy Wbk.Worksheets(1).Range("a1")
''保存并退出
Wbk.SaveAs "d:\" & w1 & ".xls"
Wbk.Close
Set Wbk = Nothing
w1 = .Range("a" & i)
iStart = i
iEnd = i
Else
iEnd = i
End If
End If
Next
End With
End Sub
'
....
这是比较基本你自己看着修改吧