troubleshoot pasting values in VBA Code from one sheet to another

Hi - any hints as to why I keep getting an error on the final line?

    

'    Choose a specific sheet

    Sheets("A").Select

'   Selecting specific cells

    Range("A4:AC4").Select
    Selection.Copy
    
'   Pasting them to the another sheet, which is protected and has hidden cells
  
    Sheets("B").Select
    Sheets("B").Unprotect Password:="PASSWORD"
      
    Cells.Select
    Selection.EntireRow.Hidden = False
    Selection.EntireColumn.Hidden = False
    Range("A3").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.Paste

Hope that's better?

So any ideas?

hi,

To fix your existing code (in the original post) you could just change the last line...

'    from "Selection.Paste" to "
ActiveSheet.Paste
Application.CutCopyMode = False

Or you could try the below code which I have optimised slightly by removing the un-necessary* use of "select":
*This is the way it is recorded by the macro recorder.

Sub test()
Dim RngToCopy As Range
'define the range to be copied
Set RngToCopy = Sheets("A").Range("A4:AC4")
'   prepare other sheet (which is protected and has hidden cells) for pasting
With Sheets("B")
    .Unprotect Password:="PASSWORD"
    With .Cells
        .EntireRow.Hidden = False
        .EntireColumn.Hidden = False
    End With
'    copy & paste
    RngToCopy.Copy .Range("A3").End(xlDown).Offset(1, 0)
End With
'free memory
Set RngToCopy = Nothing
End Sub

hth
Rob

Ooopps!

Sorry about that, the reason your code doesn't work is that the ".copy" is "undone" (for want of a better word) as soon as another action is done by the code (ie the "Sheets("B").Unprotect"). I would be very surprised if it had even worked once with the way the code is presented - have you change the order of the code since recording it?
To overcome this you need to change the order of your code so that ".copy" is immediately before the ".paste".

I included the

Application.CutCopyMode = False

to deactivate the clipboard once the range had been pasted into the right place. Without this line it is possible that the range could be re-pasted if [enter] is pressed in the spreadsheet (depending what other lines of code you have). If you were to shrink the VBE window & step through the original code, you could see the dotted "copy lines" around the original range & possibly the comment "select destination & press ENTER or choose paste" at the bottom of the Excel window.

However, I suggest using my optimised code as this is more efficient (without all the selections & associated screen flickering). The one line tecnique of using "range.copy destination" in my code prevents Excel from showing the previously mentioned comment.

btw, to learn about specific properties & methods in VBA code, you can select the phrase in the VBE window & press [F1] to bring up the Help files.

hth
Rob

I'm pleased I coud help - thanks for the feedback

Rob

hi Crozanski,

Welcome to the Forum

Can you please start a new thread for your question?
(see the Forum Rules esp. #2)

Also, when you start a new thread please wrap your code in "Code Tags" as per Rule #3. If you think it relevant, you can provide a link to this thread in your new thread.

Rob