|
本帖最后由 duquancai 于 2017-11-19 21:51 编辑
Sub shishi()
Dim sht As Worksheet, str$, c As Range, n&, r&
str = "新表中"
For Each sht In Sheets
If sht.Name = str Then
Application.DisplayAlerts = False
sht.Delete
Application.DisplayAlerts = True
End If
Next
Application.ScreenUpdating = False
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = str
Sheets(str).[a1:n1] = [{"隶属 关系","地区 代码","地区 名称","单位代码","单位名称","职位代码","职位名称","职位简介","职位类别","开考比例","招考人数","学 历","专 业","其 它"}]
Application.FindFormat.Clear
Application.FindFormat.Interior.Color = 65535
For Each sht In Sheets
If sht.Name <> str Then
Set c = sht.Range("c:c").Find(what:="", LookIn:=xlValues, SearchFormat:=True)
If Not c Is Nothing Then
asd = c.Address
Do
r = Sheets(str).Cells(Rows.Count, 1).End(3).Row + 1
Sheets(str).Range("a" & r & ":n" & r) = sht.Range("a" & c.Row & ":n" & c.Row).Value
Set c = sht.Range("c:c").Find(what:="", After:=c, SearchFormat:=True)
Loop Until c.Address = asd
End If
End If
Next
Application.ScreenUpdating = True
MsgBox "ok!"
End Sub
|
|