用excel vba写代码,提取当前工作簿中所有表指定列的数据,并在该工作簿中新建工作表,将上述数据横向粘贴
时间: 2024-05-12 15:17:25 浏览: 183
VBA实现Excel表格根据列拆分工作簿,再由工作簿根据列拆分成工作表.txt
5星 · 资源好评率100%
好的,您的问题是如何使用excel vba提取当前工作簿中所有表指定列的数据,并在该工作簿中新建工作表,将上述数据横向粘贴。
下面是一段VBA代码可以实现该功能:
```
Sub ExtractData()
Dim wb As Workbook
Dim ws As Worksheet
Dim newWS As Worksheet
Dim lastRow As Long
Dim lastCol As Long
Dim dataRange As Range
Dim x As Long
Set wb = ActiveWorkbook
Set newWS = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
x = 1
For Each ws In wb.Worksheets
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
Set dataRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
With newWS
If x = 1 Then
dataRange.Copy .Cells(1, 1)
Else
dataRange.Copy .Cells(1, .Cells(1, .Columns.Count).End(xlToLeft).Column + 1)
End If
End With
x = x + 1
Next ws
End Sub
```
您可以将该代码复制粘贴到VBA编辑器中,并在需要的时候执行即可。请记得在执行代码前先备份工作簿数据以避免意外情况的发生。
阅读全文