精华内容
下载资源
问答
  • VBA拆分excel

    2015-08-06 14:02:35
    VBA拆分excel表格,最快的分表,详情请下载观看。
  • VBA拆分工具

    千次阅读 2018-07-23 22:36:55
    '输入用户想要拆分的工作表 Dim sheet_name sheet_name = Application.InputBox("请输入拆分工作表的名称") '请输入拆分工作表的名称 Worksheets(sheet_name).Select '输入获取拆分需要的条件列 Dim col_...

     

    Sub XXX_Click()
    '输入用户想要拆分的工作表
    Dim sheet_name
    sheet_name = Application.InputBox("请输入拆分工作表的名称") '请输入拆分工作表的名称
    Worksheets(sheet_name).Select
    '输入获取拆分需要的条件列
    
    Dim col_name
    
    col_name = Application.InputBox("请输入拆分依据的列号(如A):") '
    
    '输入拆分的开始行,要求输入的是数字
    
    Dim start_row As Integer
    
    start_row = Application.InputBox(prompt:="请输入拆分的开始行:", Type:=1) '
    
    '暂停屏幕更新
    
    Application.ScreenUpdating = False
    
    '工作表的总行数
    
    Dim end_row
    
    end_row = Worksheets(sheet_name).Range("A65536").End(xlUp).Row
    
    '遍历计算所有拆分表,每个拆分表的格式为"表名称,表行数"
    
    '对于二维数组,ReDim只能扩充最后一维,因此sheet_map行不变,扩充列
    
    Dim sheet_map(), sheet_index
    
    ReDim sheet_map(1, 0)
    
    sheet_map(0, 0) = Range(col_name & start_row).Value
    
    sheet_map(1, 0) = 1
    
    sheet_index = 0
    
    With Worksheets(sheet_name)
    
        Dim row_count, temp, i
    
        row_count = 0
    
        For i = start_row + 1 To end_row
    
            temp = Range(col_name & i).Value
    
            If temp = Range(col_name & (i - 1)).Value Then
    
                sheet_map(1, sheet_index) = sheet_map(1, sheet_index) + 1
    
            Else
    
                ReDim Preserve sheet_map(1, sheet_index + 1)
    
                sheet_index = sheet_index + 1
    
                sheet_map(0, sheet_index) = temp
    
                sheet_map(1, sheet_index) = 1
    
            End If
    
        Next
    
    End With
    
    '根据前面计算的拆分表,拆分成单个文件
    
    Dim row_index
    
    row_index = start_row
    
    For i = 0 To sheet_index
    
        Workbooks.Add
    
        '创建最终数据文件夹
    
        Dim dir_name
    
        dir_name = ThisWorkbook.Path & "\测试\"
    
        If Dir(dir_name, vbDirectory) = "" Then
    
            MkDir (dir_name)
    
        End If
    
        '创建新工作簿
    
        Dim workbook_path
    
        workbook_path = ThisWorkbook.Path & "\测试\" & sheet_map(0, i) & ".xls"
    
        ActiveWorkbook.SaveAs workbook_path, xlAddIn8
    
        ActiveSheet.Name = sheet_map(0, i)
    
        '激活当前工作簿,ThisWorkbook表示当前跑代码的工作簿
    
        ThisWorkbook.Activate
    
        '拷贝条目数据(即最前面不需要拆分的数据行)
    
        Dim row_range
    
        row_range = 1 & ":" & (start_row - 1)
    
        Worksheets(sheet_name).Rows(row_range).Copy
    
        Workbooks(sheet_map(0, i) & ".xls").Sheets(1).Range("A1").PasteSpecial
    
        '拷贝拆分表的专属数据
    
        row_range = row_index & ":" & (row_index + sheet_map(1, i) - 1)
    
        Worksheets(sheet_name).Rows(row_range).Copy
    
        Workbooks(sheet_map(0, i) & ".xls").Sheets(1).Range("A" & start_row).PasteSpecial
    
        row_index = row_index + sheet_map(1, i)
    
        '保存文件
    
        Workbooks(sheet_map(0, i) & ".xls").Close SaveChanges:=True
    
    Next
    
    '进行屏幕更新
    
    Application.ScreenUpdating = True
    
    MsgBox "数据分配工作表完成"
    
    End Sub
    
    

     

    展开全文
  • Excel Vba拆分并填充单元格

    千次阅读 2018-11-19 10:03:06
    Sub 拆分并填充单元格() ' ' 拆分并填充单元格 宏 ' ' 可以将选中的所有单元格都拆开并填充 ' If Application.Selection.MergeCells = True Then Set selectedRange =...

    https://www.cnblogs.com/icedream/archive/2013/03/05/2945158.html

    Sub 拆分并填充单元格()
    '
    ' 拆分并填充单元格 宏
    '
    ' 可以将选中的所有单元格都拆开并填充
    '
        If Application.Selection.MergeCells = True Then
            Set selectedRange = Application.Selection
            selectedRange.UnMerge
            selectedRange.Value = selectedRange.Cells(1, 1).Value
        Else
            For Each selectedCell In Application.Selection
                If selectedCell.MergeCells = True Then
                    Set selectedRange = selectedCell.MergeArea
                    selectedRange.UnMerge
                    selectedRange.Value = selectedRange.Cells(1, 1).Value
                End If
            Next
        End If
    End Sub

    记录一下,免得每次都找。

    展开全文
  • vba拆分excel表格

    千次阅读 2013-11-30 15:47:39
    上一个星期在做数学建模,由于要拆分和计算一个60M的excel表格,写了几个代码和大家分享 2-1拆分代码 Sub Macro2() ' ' 拆分一号机 ' '  Dim i As Integer  Dim j As Integer  Dim k As Integer  ...

    上一个星期在做数学建模,由于要拆分和计算一个60M的excel表格,写了几个代码和大家分享

    2-1拆分代码

    Sub Macro2()

    '
    ' 拆分一号机
    '


    '
        Dim i As Integer
        Dim j As Integer
        Dim k As Integer
        Dim day As Integer
        Dim counter As Integer
        Dim counter2 As Integer
        Dim filename As String
        Dim daystr As String
        For counter = 1 To 10
            counter2 = 0 + counter
            filename = "G:\电工杯\2\一号机\01"
            Application.Goto Reference:="R4C" + LTrim(Str(counter2)) + ":R51844C" + LTrim(Str(counter2))
            Selection.Copy
            Workbooks.Add
            Range("A4").Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            i = counter 
            day = i
            daystr = LTrim(day)
            Range("A5").Select
            If day < 10 Then
            daystr = "0" + daystr
            End If
            ActiveWorkbook.SaveAs filename:=filename + daystr + ".xls", _
                FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
                ReadOnlyRecommended:=False, CreateBackup:=False
            ActiveWindow.Close
           
        Next counter


    end sub




    2-2计算方差和功率代码

    Sub Macro1()
    '
    ' Macro1 Macro
    '


    '
        Dim counter As Integer
        Dim i As Long
        Dim columnnum As Integer
        Dim datanum As String
        Dim filenamepro As String
        For counter = 1 To 10


            
            filenamepro = "G:\电工杯\2\一号机\01"
            datanum = LTrim(Str(counter))
            If counter < 10 Then
            datanum = "0" + datanum
            End If
            Workbooks.Open filename:=filenamepro + datanum + ".xls"
            Cells.Select
            Selection.Copy
            Workbooks.Add
            Cells.Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
                Dim startnum As Long
                For i = 1 To 100
                    Range("B" + LTrim(Str(4 + i))).Select
                    startnum = 5 + (i - 1) * 12
                    ActiveCell.FormulaR1C1 = "=AVERAGE(R" + LTrim(Str(startnum)) + "C1:R" + LTrim(Str(startnum + 11)) + "C1)"
                Next i
            ActiveWorkbook.SaveAs filename:=filenamepro + datanum + "DATA" + ".xls", _
                FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
                ReadOnlyRecommended:=False, CreateBackup:=False


                For i = 100 To 4320
                    Range("B" + LTrim(Str(4 + i))).Select
                    startnum = 5 + (i - 1) * 12
                    ActiveCell.FormulaR1C1 = "=AVERAGE(R" + LTrim(Str(startnum)) + "C1:R" + LTrim(Str(startnum + 11)) + "C1)"
                    If (i Mod 100) = 0 Then
                        ActiveWorkbook.Save
                    End If
                Next i
                Range("C5").Select
                ActiveCell.FormulaR1C1 = "=VAR(R5C2:R4324C2)"
                ActiveWorkbook.Save
            ActiveWindow.Close
            ActiveWindow.Close
        Next counter
    End Sub












































































































































    Sub Macro1()
    '
    ' Macro1 Macro
    '


    '
        Dim counter As Integer
        Dim i As Long
        Dim columnnum As Integer
        Dim datanum As String
        Dim filenamepro As String
        For counter = 1 To 10


            
            filenamepro = "G:\电工杯\2\五号机\05"
            datanum = LTrim(Str(counter))
            If counter < 10 Then
            datanum = "0" + datanum
            End If
            Workbooks.Open filename:=filenamepro + datanum + ".xls"
            Cells.Select
            Selection.Copy
            Workbooks.Add
            Cells.Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
                Dim startnum As Long


                For i = 1 To 4320
                    Range("B" + LTrim(Str(4 + i))).Select
                    startnum = 5 + (i - 1) * 12
                    ActiveCell.FormulaR1C1 = "=AVERAGE(R" + LTrim(Str(startnum)) + "C1:R" + LTrim(Str(startnum + 11)) + "C1)"
                Next i


                ActiveWorkbook.SaveAs filename:=filenamepro + datanum + "DATA" + ".xls", _
                FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
                ReadOnlyRecommended:=False, CreateBackup:=False




                Range("C5").Select
                ActiveCell.FormulaR1C1 = "=VAR(R5C2:R4324C2)"
                ActiveWorkbook.Save
            ActiveWindow.Close
            ActiveWindow.Close
        Next counter
    End Sub



    2-3均值方差列拆分拆分代码

    Sub Macro2()
    '
    ' Macro2 Macro
        Dim counter As Integer
        Dim i As Integer
        Dim columnnum As Integer
        Dim datanum As String
        Dim filenamepro As String


        ChDir "G:\电工杯\2\一号机"
        For counter = 1 To 10
            filenamepro = "G:\电工杯\2\一号机\01"
            datanum = LTrim(Str(counter))
            If counter < 10 Then
            datanum = "0" + datanum
            End If


            Workbooks.Open filename:=filenamepro + datanum + "DATA" + ".xls"
            Columns("B:B").Select
            Selection.Copy
            Workbooks.Add
            Columns("A:A").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Range("A4").Select
            Application.CutCopyMode = False
            ActiveCell.FormulaR1C1 = "平均功率"
            Range("C4").Select
            ActiveWorkbook.SaveAs filename:=filenamepro + datanum + "DATA平均功率" + ".xls", FileFormat _
                :=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
                False, CreateBackup:=False
            ActiveWindow.Close
            Columns("C:C").Select
            Selection.Copy
            Workbooks.Add
            Columns("A:A").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Range("A4").Select
            Application.CutCopyMode = False
            ActiveCell.FormulaR1C1 = "方差"
            ActiveWorkbook.SaveAs filename:=filenamepro + datanum + "DATA方差" + ".xls", FileFormat:= _
                xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
                , CreateBackup:=False
            ActiveWindow.Close
            ActiveWindow.Close
       Next counter
    End Sub

    4-1-1分钟拆分代码

    Sub Macro1()
    '
    ' 1分钟
    '


    '
        Dim counter As Integer
        Dim i As Long
        Dim columnnum As Integer
        Dim datanum As String
        Dim filenamepro As String
        For counter = 1 To 10


            
            filenamepro = "G:\电工杯\第四题\拆分\"
            datanum = LTrim(Str(counter))
            If counter < 10 Then
            datanum = "0" + datanum
            End If
            Workbooks.Open filename:=filenamepro + "拆分"+datanum + ".xls"
            Cells.Select
            Selection.Copy
            Workbooks.Add
            Cells.Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
                Dim startnum As Long


                For i = 1 To 4320
                    Range("B" + LTrim(Str(0 + i))).Select
                    startnum = 1 + (i - 1) * 12
                    ActiveCell.FormulaR1C1 = "=R" + LTrim(Str(startnum)) + "C1"
                Next i


                ActiveWorkbook.SaveAs filename:=filenamepro + "1分钟拆分\1M"+datanum + "DATA" + ".xls", _
                FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
                ReadOnlyRecommended:=False, CreateBackup:=False




                Range("C1").Select
                ActiveCell.FormulaR1C1 = "=VAR(R1C2:R4320C2)"
                ActiveWorkbook.Save
            ActiveWindow.Close
            ActiveWindow.Close
        Next counter
    End Sub







    4-1拆分代码

    Sub Macro2()
    '
    ' 拆分
    '


    '
        Dim i As Integer
        Dim j As Integer
        Dim k As Integer
        Dim day As Integer
        Dim counter As Integer
        Dim counter2 As Integer
        Dim filename As String
        Dim daystr As String
        For counter = 1 To 10
            counter2 = 0 + counter
            filename = "G:\电工杯\第四题\拆分\拆分"
            Application.Goto Reference:="R1C" + LTrim(Str(counter2)) + ":R51840C" + LTrim(Str(counter2))
            Selection.Copy
            Workbooks.Add
            Range("A1").Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            i = counter 
            day = i
            daystr = LTrim(day)
            If day < 10 Then
            daystr = "0" + daystr
            End If
            ActiveWorkbook.SaveAs filename:=filename + daystr + ".xls", _
                FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
                ReadOnlyRecommended:=False, CreateBackup:=False
            ActiveWindow.Close
           
        Next counter


    end sub



    1000个数据变成125行

    这个代码好像删了



















































































    Sub Macro1()
    '
    ' 5分钟
    '


    '
        Dim counter As Integer
        Dim i As Long
        Dim columnnum As Integer
        Dim datanum As String
        Dim filenamepro As String
        For counter = 1 To 10


            
            filenamepro = "G:\电工杯\第四题\拆分\"
            datanum = LTrim(Str(counter))
            If counter < 10 Then
            datanum = "0" + datanum
            End If
            Workbooks.Open filename:=filenamepro + "拆分"+datanum + ".xls"
            Cells.Select
            Selection.Copy
            Workbooks.Add
            Cells.Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
                Dim startnum As Long


                For i = 1 To 864
                    Range("B" + LTrim(Str(0 + i))).Select
                    startnum = 1 + (i - 1) * 60
                    ActiveCell.FormulaR1C1 = "=R" + LTrim(Str(startnum)) + "C1"
                Next i


                ActiveWorkbook.SaveAs filename:=filenamepro + "5分钟拆分\5M"+datanum + "DATA" + ".xls", _
                FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
                ReadOnlyRecommended:=False, CreateBackup:=False




                Range("C1").Select
                ActiveCell.FormulaR1C1 = "=VAR(R1C2:R864C2)"
                ActiveWorkbook.Save
            ActiveWindow.Close
            ActiveWindow.Close
        Next counter
    End Sub














































































































    Sub Macro1()
    '
    ' 15分钟
    '


    '
        Dim counter As Integer
        Dim i As Long
        Dim columnnum As Integer
        Dim datanum As String
        Dim filenamepro As String
        For counter = 1 To 10


            
            filenamepro = "G:\电工杯\第四题\拆分\"
            datanum = LTrim(Str(counter))
            If counter < 10 Then
            datanum = "0" + datanum
            End If
            Workbooks.Open filename:=filenamepro + "拆分"+datanum + ".xls"
            Cells.Select
            Selection.Copy
            Workbooks.Add
            Cells.Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
                Dim startnum As Long


                For i = 1 To 288
                    Range("B" + LTrim(Str(0 + i))).Select
                    startnum = 1 + (i - 1) * 180
                    ActiveCell.FormulaR1C1 = "=R" + LTrim(Str(startnum)) + "C1"
                Next i


                ActiveWorkbook.SaveAs filename:=filenamepro + "15分钟拆分\15M"+datanum + "DATA" + ".xls", _
                FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
                ReadOnlyRecommended:=False, CreateBackup:=False




                Range("C1").Select
                ActiveCell.FormulaR1C1 = "=VAR(R1C2:R288C2)"
                ActiveWorkbook.Save
            ActiveWindow.Close
            ActiveWindow.Close
        Next counter
    End Sub












    展开全文
  • VBA 拆分工作簿并命名

    2019-01-14 14:57:00
    Sub 拆分工作簿并命名() Dim mypath As String Dim sh As Worksheet Dim file_name As String mypath = ThisWorkbook.Path For Each sh In Worksheets sh.Copy '这条语句会复制工作表并放在一个新建...
    Sub 拆分工作簿并命名()
    
    Dim mypath As String
    Dim sh As Worksheet
    Dim file_name As String
    
    mypath = ThisWorkbook.Path
    
    For Each sh In Worksheets
        sh.Copy '这条语句会复制工作表并放在一个新建工作簿
        file_name = ActiveWorkbook.Worksheets(1).Range("a1")
        '连接时 & 符号前面要加一个空格
        
        ActiveWorkbook.SaveAs mypath & "\" & file_name & ".xlsx"
        ActiveWorkbook.Close True
        
        
        
        
    Next
    
        
    
    End Sub
    

      

    转载于:https://www.cnblogs.com/queqiaoshui/p/10266763.html

    展开全文
  • 微信搜一搜XDataAnalysis模板:原始表如上,例如我想安装行政区拆分为多个sheetvba实现:文件以及代码如下:链接:...当前是拆分B列,自行修改下就行 拆分列数组 = .Range("b1:b" &...
  • 利用VBA拆分包含多行数据的excel表格

    千次阅读 2018-05-27 12:11:26
    即打开VBA的模块粘贴下面代码后按f5运行,会在该目录下得到拆分后的文件.Sub test()Application.ScreenUpdating = Falsep = ThisWorkbook.Path &amp; "\"With ActiveSheetFor r = 1 To .Range("a...
  • VBA拆分一个excel文件为多个excel文件

    千次阅读 2018-12-20 13:12:59
    有时候时候需要将一个大的excel表格,拆分成多个文件,这里通过VBA提供一种方法 步骤一 打开VB编辑器 步骤二 插入模块 步骤三 复制代码到模块中 步骤四 运行代码 Sub 保留表头拆分以表头命名新工作簿() Dim arr,...
  • VBA 拆分工作簿功能

    2013-09-14 14:47:29
    各位大神,小弟最近在自学VBA,碰到一个问题,就是在将某一个工作簿中的多张表格,按照表格名称自动拆分到新的工作簿中。不过不知是何原因,小弟下面的代码会在For Each循环那里报下标越界的错误,跪求高人指点。 ...
  • Option Explicit Option Base 1 Sub TestGetProvinceCollection() Dim sht As Worksheet, provinceCol As Integer Set sht = ActiveSheet provinceCol = 13 '非直邮 Dim c As Collection ...
  • Sub DetachWorkbook() On Error Resume Next Dim pathStr As String, i As Long, activeWb As String With Application.FileDialog(msoFileDialogFolderPicker) If .Show Then pat...
  • 这是POINT小数点的第339篇文章点点写在前面:之前我们有分享过一个场景1:你制作了一份总表你想要拆分成各个分公司,并且你需要对分公司的多个同事发送邮件。如果有20几个分公司,你要拆分+写邮件+选择接收者=20多次...
  • Word中Mail Merge功能之后,分别保存成独立的word文件Sub BreakOnSection() Dim a As Excel.Application, ab As Excel.Workbook Set a = CreateObject("excel.application") Set ab = a.Workbooks.Open("D:\Book2....
  • VBA筛选拆分脚本

    2014-01-14 18:47:42
    采用vba根据对excel表某一列进行筛选拆分
  • 拆分和合并excel表 VBA

    2021-04-11 17:50:15
    拆分合并excel表 VBA拆分到表合并excel表 拆分到表 说明:运行后需要输入两个值,如下为值的说明 第一个需要输入的值:如果是需要从A列分类拆分表,请输入1·,如果是B列分类拆分表,请输入2,以此类推 第二个...
  • VBA代码拆分excel

    千次阅读 2017-08-12 00:22:20
    忙活两小时,终于帮老姐实现了拆分工作excel的需求,记录下,说不定以后可以用到。。 需求:一个excel文件工作簿可能包含多个工作表(比如sheetA,sheetB,sheetC),每个sheet里每一行都有一个地市字段,现需要根据地市...
  • VBA自动拆分地址

    2018-07-29 13:07:47
    ' 省级单位拆分 For i = 1 To endRow If .Cells(i, startCol) Like Left(split_value, 2) & "*" Then addrBack(0) = .Cells(i, startCol) '省级单位 Exit For End If Next ' 市级单位拆分 For i = 8 To 1 ...
  • vba正则拆分字符串

    2019-11-11 11:09:45
    Option Explicit Sub test() Dim i As Long For i = 2 To Sheet1.Range("A1000000").End(xlUp).Row Sheet1.Cells(i, 2) = GetStr(Sheet1.Cells(i, 1).Value, "^.*[a-zA-Z](?=\d)") ...
  • 【excel vba拆分表格

    2020-07-30 23:17:06
    拆分表格并保存函数Application主程序对象Open 打开文件Add 新建工作簿练习拆分表存储成文件利用筛选分离表格内容 函数 Application主程序对象 application.displayalsert=false 表示不要弹窗 Open 打开文件 eg : ...
  • 使用 VBA 宏实现文件的合并与拆分 合并后的文件会自动存放在该文件的 MergeFiles 工作表中; 如果要拆分文件,需要将要拆分的内容放到 SplitFiles 工作表中。该宏自动会将第一列中值相同的行拆分到一个文件中,同时...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 495
精华内容 198
关键字:

vba拆分