可以直接使用的VBA代码,真香!
今天我们为大家总结几个常用的VBA代码,大家复制后,简单修改即可使用! 想了想,还是先写吧……
我是小白如何使用代码?
代码详细使用说明:
添加相同的前缀或者后缀
Dim rng As Range
Dim add_str As String
add_str = Application.InputBox("请输入要添加的前缀内容:")
For Each rng In Selection
rng.Value = add_str & rng.Value
Next
End Sub
如何修改?
rng.Value = add_str & rng.Value
rng.Value = rng.Value & add_str
功能说明:
'功能 :根据提供的单元格地址,提取内容
'公众号:Excel办公实战
'日期 :20210430
'--------------------------------------------
Sub getDataByAddr()
Dim i As Long, n As Long
Dim iStart As Integer: iStart = 3
Dim maxRow As Long
'根据需要修改【汇总】这个汇总表名称
Const totalShtName As String = "汇总"
Dim totalSht As Worksheet
Set totalSht = ThisWorkbook.Worksheets(totalShtName)
'清空历史数据
totalSht.Range("A3").CurrentRegion.Offset(1, 0).ClearContents
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
If Sh.Name <> totalShtName Then
With totalSht
maxRow = .Cells(Rows.Count, 1).End(3).Row + 1
'复制粘贴内容
Sh.Range(.[B1]).Copy
.Cells(maxRow, 2).PasteSpecial xlPasteValues
'写入表名
.Cells(maxRow, 1).Resize(Range(.[B1]).Rows.Count, 1) = Sh.Name
End With
End If
Next
End Sub
功能说明:
Private Sub Worksheet_Activate()
Dim Sh As Worksheet
Dim n As Long: n = 1
'清空内容
Cells.ClearContents
[A1:b1] = [{"序号","目录"}]
For Each Sh In ThisWorkbook.Worksheets
If Sh.Name <> "目录" Then
n = n + 1
Cells(n, 1) = n - 1
Cells(n, 2) = Sh.Name
End If
Next
With Range("A1").CurrentRegion
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
End Sub
'双击单元格事件
Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
If Target.CountLarge > 1 Then End
On Error Resume Next
Cancel = True
If Target <> "" Then
Sheets(Target.Value).Select
End If
End Sub
赞 (0)