PasteSpecial Method of Range Class Failed

[John.McLaughlin]

Hello, would conditional formatting of the destination cells cause this error?

Thanks in advance!

Sub Post()

    Application.CutCopyMode = False
    Dim DstRng As Range
    Dim DstWks As Worksheet
    Dim LastRow As Long
    Dim N As Long, r As Long
    Dim SrcRng As Range
    Dim SrcWks As Worksheet


    Sheets("SHEET2").Visible = True
    Sheets("SHEET1").Visible = True
    Sheets("SHEET1").Select
    ActiveSheet.Unprotect
     

    
      ' Assign the Worksheets
        Set SrcWks = Worksheets("SHEET2")
        Set DstWks = Worksheets("SHEET1")
      

      
      ' Get all cells in the Source Range starting with row 5
        Set SrcRng = SrcWks.Range("A5:E5")
        LastRow = SrcWks.Cells(Rows.Count, "A").End(xlUp).Row
        If LastRow < SrcRng.Row Then Exit Sub Else Set SrcRng = SrcRng.Resize(LastRow - SrcRng.Row + 1, 5)
        
      
      ' Find the next empty row in the Destination Range starting at row 5
        
        Set DstRng = DstWks.Range("B5")
        LastRow = DstWks.Cells(Rows.Count, "B").End(xlUp).Row
        Set DstRng = IIf(LastRow < DstRng.Row, DstRng, DstRng.Offset(LastRow - DstRng.Row + 1, 0))

        
        ' This is the paste of the text
          DstRng.Select

        ' This is the line that stops the code
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

        
 Sheets("SHEET2").Select
   Range("B5").Select

End Sub

 

[DanteAmor]

Hi John. Thanks for posting on the forum.

The important thing is missing from your code: Copy

I also made some adjustments to the code, try the following:

Sub Post()
  Dim DstWks As Worksheet, SrcWks As Worksheet
  Dim DstRng As Range, SrcRng As Range
  Dim LastRow As Long
 
  ' Assign the Worksheets
  Set SrcWks = Sheets("SHEET2")
  Set DstWks = Sheets("SHEET1")
 
  SrcWks.Visible = True
  DstWks.Visible = True
  DstWks.Unprotect
 
  ' Get all cells in the Source Range starting with row 5
  Set SrcRng = SrcWks.Range("A5:E5")
  LastRow = SrcWks.Cells(Rows.Count, "A").End(xlUp).Row
  If LastRow < SrcRng.Row Then Exit Sub Else Set SrcRng = SrcRng.Resize(LastRow - SrcRng.Row + 1, 5)
 
  ' Find the next empty row in the Destination Range starting at row 5
  Set DstRng = DstWks.Range("B5")
  LastRow = DstWks.Cells(Rows.Count, "B").End(xlUp).Row
  Set DstRng = IIf(LastRow < DstRng.Row, DstRng, DstRng.Offset(LastRow - DstRng.Row + 1, 0))
 
  'copy
  SrcRng.Copy
  DstRng.PasteSpecial Paste:=xlPasteValues
 
  SrcWks.Select
  Range("B5").Select
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[John.McLaughlin]

WOW! Yes it worked, and much cleaner code!

After looking at your modifications, I sure took the long way home

Thank you! Thank You!

 

[John.McLaughlin]

Hi John. Thanks for posting on the forum.

The important thing is missing from your code: Copy

I also made some adjustments to the code, try the following:

Sub Post()
  Dim DstWks As Worksheet, SrcWks As Worksheet
  Dim DstRng As Range, SrcRng As Range
  Dim LastRow As Long
 
  ' Assign the Worksheets
  Set SrcWks = Sheets("SHEET2")
  Set DstWks = Sheets("SHEET1")
 
  SrcWks.Visible = True
  DstWks.Visible = True
  DstWks.Unprotect
 
  ' Get all cells in the Source Range starting with row 5
  Set SrcRng = SrcWks.Range("A5:E5")
  LastRow = SrcWks.Cells(Rows.Count, "A").End(xlUp).Row
  If LastRow < SrcRng.Row Then Exit Sub Else Set SrcRng = SrcRng.Resize(LastRow - SrcRng.Row + 1, 5)
 
  ' Find the next empty row in the Destination Range starting at row 5
  Set DstRng = DstWks.Range("B5")
  LastRow = DstWks.Cells(Rows.Count, "B").End(xlUp).Row
  Set DstRng = IIf(LastRow < DstRng.Row, DstRng, DstRng.Offset(LastRow - DstRng.Row + 1, 0))
 
  'copy
  SrcRng.Copy
  DstRng.PasteSpecial Paste:=xlPasteValues
 
  SrcWks.Select
  Range("B5").Select
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Forgive me @Dante Amor,

When I change the Sheet Names in your reply to it's actual name, the copy doesn't happen?

If I use the CodeNames (Sheet2 & Sheet41), I get Subscript out of range. I tried to qualify the sheet names when using the Codenames, and same Subscript out of range error?

Your code was beautiful! I have spent the last few days trying to learn the answer Why would something so simple as a sheet name change do this?

Thanks (again) in advance!

Sub Post()
  
  
  Dim wbThis As Workbook
  Set wbThis = ActiveWorkbook
  wbThis.Activate
 
 
  Dim DstWks As Worksheet, SrcWks As Worksheet
  Dim DstRng As Range, SrcRng As Range
  Dim LastRow As Long
 
 
  With ThisWorkbook
 
  ' Assign the Worksheets
  Set SrcWks = wbThis.Sheets("SHEET41")
  Set DstWks = wbThis.Sheets("SHEET2")
 
  End With
  
  
  SrcWks.Visible = True
  DstWks.Visible = True
  DstWks.Unprotect
 
  ' Get all cells in the Source Range starting with row 5
  Set SrcRng = SrcWks.Range("A5:E5")
  LastRow = SrcWks.Cells(Rows.Count, "A").End(xlUp).Row
  If LastRow < SrcRng.Row Then Exit Sub Else Set SrcRng = SrcRng.Resize(LastRow - SrcRng.Row + 1, 5)
 
  ' Find the next empty row in the Destination Range starting at row 5
  Set DstRng = DstWks.Range("B5")
  LastRow = DstWks.Cells(Rows.Count, "B").End(xlUp).Row
  Set DstRng = IIf(LastRow < DstRng.Row, DstRng, DstRng.Offset(LastRow - DstRng.Row + 1, 0))
 
  'copy
  SrcRng.Copy
  DstRng.PasteSpecial Paste:=xlPasteValues
 
  DstWks.Select
  Range("B5").Select
End Sub

 

[DanteAmor]

If I use the CodeNames (Sheet2 & Sheet41)

The codename is already the name of the object.

The sheet have 3 names (so to speak):
1) The name of the tab.
2) The codename (name of the object).
3) The index of the sheet.

I think it is better explained in the following image:

Sub Explanation_1()
  Dim sh As Worksheet
  
  Set sh = Sheets("Master")   'Reference to the tab name in quotes
  Set sh = Sheet1             'Reference to object
  Set sh = Sheets(1)          'Reference to index
End Sub

------------------------------------------------------------------


So the code, if you want to use the codename:

Sub Post()
  Dim DstWks As Worksheet, SrcWks As Worksheet
  Dim DstRng As Range, SrcRng As Range
  Dim LastRow As Long
 
  ' Assign the Worksheets
  Set SrcWks = Sheet41
  Set DstWks = Sheet2
 
  SrcWks.Visible = True
  DstWks.Visible = True
  DstWks.Unprotect

If you want to reference Thisworkbook:

Sub Post()
  Dim wb As Workbook
  Dim DstWks As Worksheet, SrcWks As Worksheet
  Dim DstRng As Range, SrcRng As Range
  Dim LastRow As Long
 
  Set wb = ThisWorkbook
  ' Assign the Worksheets
  Set SrcWks = wb.Sheet41
  Set DstWks = wb.Sheet2
 
  SrcWks.Visible = True
  DstWks.Visible = True
  DstWks.Unprotect

Or this:

Sub Post_v2()
  Dim DstWks As Worksheet, SrcWks As Worksheet
  Dim DstRng As Range, SrcRng As Range
  Dim LastRow As Long
 
  ' Assign the Worksheets
  With ThisWorkbook
    Set SrcWks = .Sheet41
    Set DstWks = .Sheet2
  End With
 
  SrcWks.Visible = True
  DstWks.Visible = True
  DstWks.Unprotect

I hope the above helps you understand a bit more how to reference sheets and the workbook.