当达到特定的coloumn值时,将Excel文件拖放到不同的工作表中。 [英] Slipt Excel file into different sheets, when a specific coloumn value is hit.
问题描述
你好,
我试图将一个很长的电子表格拆分成较小的电子表格。我想在每次出现字符串时拆分表格。即"客户名称"
例如,我有一个这样的点差。
Hello,
I am trying to split a very long spreadsheet into smaller ones. I want to split the sheet everytime a string a comes up. I.e. "Client Name"
So for example, I have a spread that goes something like this.
3 LMN
我想在显示"客户名称:"时拆分它们。分割后,应该有两个这样的文件。 />
第一个文件:
3 LMN
I want to split these whenever the "Client Name:" is shown. After the splitting of the sheet, there should be two files like these
First File:
第二档:
3 LMN
3 LMN
这可行吗?请告诉我。
先谢谢你了!
Is this doable? Please let me know.
Thank you in Advance!
推荐答案
您可以运行以下宏:
You could run the following macro:
Sub SplitSheet()
Const lngCol = 1 ' column A
Dim lngRow2 As Long
Dim lngRow1 As Long
Dim lngLastRow As Long
Dim wshS As Worksheet
Dim wshT As Worksheet
Application.ScreenUpdating = False
Set wshS = ActiveSheet
lngLastRow = wshS.Cells(wshS.Rows.Count, lngCol).End(xlUp).Row
For lngRow2 = 1 To lngLastRow + 1
If wshS.Cells(lngRow2, lngCol).Value = "Client Name:" Or _
lngRow2 = lngLastRow + 1 Then
If lngRow1 > 0 Then
Set wshT = Worksheets.Add(After:=wshS)
wshS.Range(lngRow1 & ":" & lngRow2 - 1).Copy _
Destination:=wshT.Range("A1")
End If
lngRow1 = lngRow2
End If
Next lngRow2
Application.ScreenUpdating = True
End Sub
这篇关于当达到特定的coloumn值时,将Excel文件拖放到不同的工作表中。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!