vba 将数据传输到新工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16985046/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Transfer data to new sheet
提问by MikeS
I have a macro to create a new worksheet, name that sheet based on the value of cell in another sheet (i.e. sheet1.Range("F3").value).
我有一个宏来创建一个新的工作表，根据另一个工作表中单元格的值命名该工作表（即sheet1.Range("F3").value）。
After that I need to go to another sheet in the same workbook where my data is located, select certain data and transfer it to the new sheet.
之后，我需要转到我的数据所在的同一工作簿中的另一个工作表，选择某些数据并将其传输到新工作表。
This is the code I use to create the new sheet and name it.
这是我用来创建新工作表并为其命名的代码。
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
回答by rwisch45
You can declare worksheet variables and put your data sheet and your newly added sheet into them. Then it's easy to manipulate data on either one.
您可以声明工作表变量并将数据表和新添加的表放入其中。然后很容易操作任何一个上的数据。
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
回答by The Gambill
I would make a few edits to the above statement to make it more effective. I know the changes are minor but this should work.
我将对上述声明进行一些编辑以使其更有效。我知道这些变化很小，但这应该有效。
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
