excel - Excel VBA-错误：Range类的PasteSpecial方法失败(Excel VBA - Error: PasteSpecial method of Range class failed)

Question

I try to write, in VBA, a macro that copies tables (colors, formats etc.) from the sheet for each day (Monday, Tuesday, Wednesday, Thursday and Friday) and pastes to sheets (262 sheets) for the same day.

(我尝试在VBA中编写一个宏，该宏每天（每天，周一，周二，周三，周四和周五）从工作表中复制表格（颜色，格式等），并粘贴到同一天的工作表（262张）中。)

(Monday - Monday etc.) Sheets names I have in sheet "Data".

(（星期一-星期一等。）工作表名称我在工作表“数据”中拥有的名称。)

But I got this error: Run-time error '1004': Method PasteSpecial class Range Failure.

(但是我遇到了以下错误：运行时错误'1004'：方法PasteSpecial类范围失败。)

Thank you for your help!

(谢谢您的帮助！)

This is my VBA macro:

(这是我的VBA宏：)

Sub copy_paste()

For i = 1 To 262
    If 1 = i Mod 5 Then
        Worksheets("wednesday").Activate
        Cells.Select
        Application.CutCopyMode = False
        Selection.Copy
        ' This is the problem part of code (said Debugger)
        Sheets(Worksheets("Data").Cells(i, 2).Value).Range("A1").PasteSpecial _
            Paste:=x1PasteAllUsingSourceTheme, Operation:=x1None _
            , SkipBlanks:=False, Transpose:=False
    End If
    If 2 = i Mod 5 Then
        Sheets("thursday").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets(Worksheets("Data").Cells(i, 2).Value).Range("A1").PasteSpecial _
            Paste:=x1PasteAllUsingSourceTheme, Operation:=x1None _
            , SkipBlanks:=False, Transpose:=False
    End If
    If 3 = i Mod 5 Then
        Sheets("friday").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets(Worksheets("Data").Cells(i, 2).Value).Range("A1").PasteSpecial _
            Paste:=x1PasteAllUsingSourceTheme, Operation:=x1None _
            , SkipBlanks:=False, Transpose:=False
    End If
    If 4 = i Mod 5 Then
        Sheets("monday").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets(Worksheets("Data").Cells(i, 2).Value).Range("A1").PasteSpecial _
            Paste:=x1PasteAllUsingSourceTheme, Operation:=x1None _
            , SkipBlanks:=False, Transpose:=False
    End If
    If 0 = i Mod 5 Then
        Sheets("tuesday").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets(Worksheets("Data").Cells(i, 2).Value).Range("A1").PasteSpecial _
            Paste:=x1PasteAllUsingSourceTheme, Operation:=x1None _
            , SkipBlanks:=False, Transpose:=False
    End If
Next i

End Sub

(结束子)

  ask by Jan Beran translate from so

Answer 1

You can use the Worksheets("SheetName").Paste method, instead of the Range.PasteSpecial method.

(您可以使用Worksheets（“ SheetName”）。Paste方法，而不是Range.PasteSpecial方法。)

But really, I'd recommend using a full up worksheet copy if you're literally copying everything:

(但实际上，如果您实际上要复制所有内容，建议使用完整的工作表副本：)

    Worksheets("wednesday").Copy After:=Worksheets(Sheets.Count)
    ActiveSheet.Name = Worksheets("Data").Cells(i, 2).Value