将数据从一个工作簿复制到下一个工作簿.错误:选择范围类的方法失败

Question

尝试将数据从一个工作簿复制到下一个工作簿。

当我尝试选择工作表时，我会收到一条消息

选择范围类失败的方法

我想使用select来复制粘贴特殊的东西以保持格式。

Public Sub Worksheet_Export()

'Setting Dimensions for Current Workbook and New workbook 

Dim current_workbook As Workbook
Dim New_workbook As Workbook

Dim current_worksheet As Worksheet
Dim New_worksheet As Worksheet

Set current_workbook = ThisWorkbook
Set New_workbook = Workbooks.Add

Set current_worksheet = current_workbook.Sheets(2)
Set New_worksheet = New_workbook.Sheets(1)

'Copying Data From Current Workbook to CSV File Workbook

current_worksheet.Range("A:C").Select
End Sub

Answer 1

在选择范围之前，您需要激活工作表

更好的解决方案：

current_worksheet.Range("A:C"(.复制目的地：=新建工作表.范围("A1"(

Public Sub Worksheet_Export()
'Setting Dimensions for Current Workbook and New workbook
Dim current_workbook As Workbook
Dim New_workbook As Workbook
Dim current_worksheet As Worksheet
Dim New_worksheet As Worksheet
Set current_workbook = ThisWorkbook
Set New_workbook = Workbooks.Add
Set current_worksheet = current_workbook.Sheets(2)
Set New_worksheet = New_workbook.Sheets(1)


'Copying Data From Current Workbook to CSV File Workbook
current_worksheet.Activate 'you need to activate before selecting
current_worksheet.Range("A:C").Select

'better solution:
current_worksheet.Range("A:C").Copy Destination:=New_worksheet.Range("A1")
End Sub