例如有一个汇总excel表,表里有个sheet1,我需要把另一个excel分表里面的内容复制到汇总excel表里面的指定sheet1中(那个excel分表名字不固定,最好可以弹框选择某个excel文件,且那个excel分表里面只有1个sheet),即excel分表里面sheet的内容全部复制并贴到汇总excel表的sheet1里面。
另:最好只打开汇总excel表即可,不用打开excel分表,谢谢。
温馨提示:答案为网友推荐,仅供参考
第1个回答 2017-09-28
private Sub bookMerge(nstart As Long, ncolumn As Integer)
' MsgBox "欢迎使用合并工作表工具1.0" & Chr(13) & "made by excel880工作室" & Chr(13) _
' & "本工具将合并当前目录下所有工作簿的第一个工作表到一个工作簿"
'
Dim fs, f, f1, fc, s
Dim wk As Workbook, sht As Worksheet
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(ThisWorkbook.Path)
Set fc = f.Files
Set targetWk = Workbooks.Add
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\合并.xls"
Set targetSht = ActiveWorkbook.Sheets(1)
targetSht.Name = "合并"
Application.DisplayAlerts = False
Application.ScreenUpdating = False
' Set targetWk = Workbooks.Open(ThisWorkbook.Path & "\" & "合并.xls")
' Set targetSht = targetWk.Sheets(1)
' MsgBox "欢迎使用合并工作表工具1.0" & Chr(13) & "made by excel880工作室" & Chr(13) _
' & "本工具将合并当前目录下所有工作簿的第一个工作表到一个工作簿"
'
Dim fs, f, f1, fc, s
Dim wk As Workbook, sht As Worksheet
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(ThisWorkbook.Path)
Set fc = f.Files
Set targetWk = Workbooks.Add
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\合并.xls"
Set targetSht = ActiveWorkbook.Sheets(1)
targetSht.Name = "合并"
Application.DisplayAlerts = False
Application.ScreenUpdating = False
' Set targetWk = Workbooks.Open(ThisWorkbook.Path & "\" & "合并.xls")
' Set targetSht = targetWk.Sheets(1)