我有一个宏来创建一个新的工作表,根据另一个工作表(即sheet1.Range("F3").value
)中的单元格的值命名该工作表。
之后,我需要转到数据所在的同一工作簿中的另一个工作表,选择某些数据并将其传输到新工作表。
这是我用来创建新表并命名它的代码。
sub createNewSheet()
sheet_name_to_creat = sheet1.range("F3").value
for rep = 1 to (worksheets.count)
if lCase(Sheets(rep).name = Lcase(Sheet_name_to_create) Then
MagBox "This Sheet already exists"
exit sub
end if
next
Sheets.add after:=sheets(sheets.count
Sheets(ActiveSheet.Name).name=Sheet_name_to_create
end sub
您可以声明工作表变量,并将数据表和新添加的工作表放入其中。这样在任意一个上操作数据都很容易。
Sub createNewSheet()
Dim wsNew As Worksheet
Dim wsData As Worksheet
'Rename this sheet to the name of the sheet where your data is located
Set wsData = ActiveWorkbook.Sheets("MyDataSheet")
sheet_name_to_create = Sheet1.Range("F3").Value
For rep = 1 To (Worksheets.Count)
If LCase(Sheets(rep)).Name = LCase(Sheet_name_to_create) Then
MagBox "This Sheet already exists"
Exit Sub
End If
Next
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(ActiveSheet.Name).Name = Sheet_name_to_create
Set wsNew = ActiveWorkbook.ActiveSheet
'Now grab the data from your data worksheet
Dim myData As String
myData = wsData.Range("A1").Value
'Then put it in your newly added sheet
wsNew.Range("A1").Value = myData
wsData = Nothing
wsNew = Nothing
End Sub
我会对上面的语句做一些修改,使其更有效。我知道改动很小,但是应该可以了。
Sub createNewSheet()
Dim wsNew As Worksheet
Dim wsData As Worksheet
'Rename this sheet to the name of the sheet where your data is located
Set wsData = ActiveWorkbook.Sheets("MyDataSheet")
sheet_name_to_create = Sheet1.Range("F3").Value
For rep = 1 To (Worksheets.Count)
If LCase(Sheets(rep).Name) = LCase(Sheet_name_to_create) Then
MagBox "This Sheet already exists"
Exit Sub
End If
Next
Set wsNew = Sheets.Add after:=Sheets(Sheets.Count)
wsNew.Name = Sheet_name_to_create
wsData.Range("A1:Z80").copy 'Change range here to whatever range you need to move to the new sheet
wsNew.Range("A1").pastespecial xlPasteValuesAndNumberFormats
wsNew = Nothing
End Sub