0

The intent is to:

  1. On Sheet(Master), update a Cell which triggers other cells to update a specific range.
  2. On Sheet(Master), select updated range and copy that range.
  3. On Sheet(Paste), paste the values and formatting of the copied data below the last row of data.

The script below functions properly, except for the paste special portion: PasteSpecial Paste:=xlPasteValues.

Private Sub CommandButton1_Click()

'/ I am trying to Add Data From Sheet("Master") To Sheet("Paste") as a "PasteSpecial Paste:=xlPasteValues" 

    Dim Lastrow As Long
    Sheets("Master").Range("N3") = Sheets("Master").Range("N3") - Sheets("Master").Range("N4")
    Lastrow = Sheets("Paste").Range("A65536").End(xlUp).Row + 1
    Sheets("Master").Range("L13:AO17").Copy Destination:=Sheets("Paste").Range("A" & Lastrow)

End Sub

I have attempted to add the PasteSpecial Paste:=xlPasteValues portion as in the examples below:

Example One.

Lastrow = Sheets("Paste").Range("A65536").End(xlUp).Row.PasteSpecial Paste:=xlPasteValues

Example Two.

Sheets("Master").Range("L13:AO17").Copy Destination:=Sheets("Paste").Range("A" & Lastrow).PasteSpecial Paste:=xlPasteValues

Those are the only two types of options that I have attempted based on online research.

furthark
  • 1
  • 2
  • 2
    I reccomend reading [How to avoid using select](https://stackoverflow.com/a/23913882/16578424) and [How to avoid copy/paste](https://stackoverflow.com/a/64611707/16578424) - if you apply those adivses, the problem might be gone. – Ike Jan 26 '23 at 17:37
  • I looked over both recommended links extensively and I am not sure which bit of the advise actually applies. Prior to posting here, I actually did a lot of research but I could not pin point the right solution to the issue based on the type of scenario. If you could elaborate further regarding what exactly I am failing at, that might help. – furthark Jan 26 '23 at 19:33

1 Answers1

0

After reworking it a few times, the following solution worked.

    Range("N3") = Range("N3") - Range("N4")
    Range("L13:AO17").Copy
    Sheets("Paste").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

I decided to step away from using the Lastrow var, when I realized that it was not needed. Once that was eliminated, I then focused on cleaning the script and used the offset function instead of row. This then allowed for me to add the PasteSpecial function properly.

furthark
  • 1
  • 2