我正在尝试编写一个vba代码,当我点击一个按钮时:
-
创建新工作表
-
检查文件夹中的所有excel文件(固定地址)
-
复制后,对每个单元格值(等于10个数字)应用自动过滤器到列(K13:K)(或者在应用过滤器后复制它们)
-
全部取出并复制到新表格
-
在A1中创建一个新的过滤器我们有多种产品我们想要选择列表
-
来到新工作表的数据适用于自动调整行和列,VerticalAlignment,HorizontalAlignment和换行文本
注意:所有数据必须来自我们拥有所有Excel文件的文件夹 .
也有错误
我们不能对合并的单元格这样做 .
Sub CopyRangeToAnotherSheet()
'*-*-*-*
'*-*-
Workbooks.Open ("C:\Users\username\Desktop\foldername\Update\filename.xlsm")
Sheets("PFMEA").Range("A12:AD3377").Copy
'Activate the destination worksheet
Sheets("PFMEA").Activate
'Select the target range
Range("A2").Select
'Paste in the target destination
ActiveSheet.Paste
Application.CutCopyMode = False
AutoFilterMode = False
Range("K1:K3377").AutoFilter
ThisWorkbook.Sheets("critical").Range("K1:K3377").AutoFilter Field:=1, Criteria1:=10
Worksheets("critical").Range("d10").WrapText = True
'*-*-*-*Resize_Columns_And_Rows_No_Header
Dim currentSheet As Worksheet
Set currentSheet = ActiveSheet
Dim Sheet As Worksheet
For Each Sheet In ActiveWorkbook.Worksheets
With Sheet
With Worksheets("Critical").Range("A:AD").Cells.Rows
Worksheets("Critical").Range("A:AD").WrapText = True
Worksheets("Critical").Range("A:AD").VerticalAlignment = xlCenter
Worksheets("Critical").Range("A:AD").EntireRow.AutoFit
Worksheets("Critical").Range("A:AD").HorizontalAlignment = xlCenter
End With '.Cells.Rows
Worksheets("Critical").Range("A:AD").Columns.EntireColumn.AutoFit
Worksheets("Critical").Range("A:AD").EntireRow.AutoFit
End With 'sheet
Next Sheet
' *-*- Auto filter for product
' Range("A1:AD1").AutoFilter
ThisWorkbook.Sheets("critical").Range("A1:AD1").AutoFilter Field:=1
currentSheet.Activate
'*******************************
'Create a Button For Return Back
ActiveSheet.Buttons.Add(30, 1, 60, 20).Select
Selection.Name = "Return Back"
Selection.OnAction = "CheckTotals"
ActiveSheet.Shapes("New Button").Select
Selection.Characters.Text = "Return Back"
'*************************************
'**************************************
Sub returnback()
ThisWorkbook.Sheets("RISK_PRIORITY").Activate
End Sub
`