Trying to paste values only without Select/Copy

[StuLux]

Hi folks, long time since I came to this board for help and many thanks to all those who have helped me in past years. I'm trying to improve my code so that instead of actually selecting a range and then pasting as values I am trying to use the following code

        Sheet1.Range("B2:F" & RecordCount).Cells.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets(SheetName).Range("B" & RowNumber).Value

I believe the .Value at the end should ensure it only pastes values and not formatting etc. but when I add the .Value to my code I get "Copy Method of Range Class Failed". The code works without the .Value but pastes formulas and formatting instead of just values. What am I doing wrong?

 

[DanteAmor]

Try this:

  Sheet1.Range("B2:F" & RecordCount).Cells.SpecialCells(xlCellTypeVisible).Copy
  Sheets(SheetName).Range("B" & RowNumber).PasteSpecial Paste:=xlPasteValues
  Application.CutCopyMode = False

 

[steve the fish]

You could remove the destination from that line and use a paste special line instead:

Sheets(SheetName).Range("B" & RowNumber).PasteSpecial xlPasteValues

 

[StuLux]

Thanks for the suggestion the code is now working by separating the destination to a separate line as follows:

        Sheet1.Range("B2:F" & RecordCount).Cells.SpecialCells(xlCellTypeVisible).Copy        
Sheets(SheetName).Range("B" & RowNumber).PasteSpecial Paste:=xlPasteValues

 

[StuLux]

Thanks for the suggestion the code is now working by separating the destination to a separate line as follows:

 Sheet1.Range("B2:F" & RecordCount).Cells.SpecialCells(xlCellTypeVisible).Copy 
       Sheets(SheetName).Range("B" & RowNumber).PasteSpecial Paste:=xlPasteValues

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.