why delete copied cells by deleting origin, after copy?

karbar · Feb 8th 2020

hello, I was copy cells of a rage to another palace, by VBA in Excel

then I delete origin, but, when I deleting origin, copied cells deleted also.

please tel me why?

Code

Set rng = Sheet_hazer.Range(Cells(2, 10), Cells(3, 12))
            rng.Select
            rng.Copy
            Cells(2, 13).Select
            Sheet_hazer.PasteSpecial
            Sheet_hazer.Range(Cells(2, 10), Cells(3, 10)).Value = ""

GCExcel · Feb 8th 2020

Hello and welcome to the forum,

I don't understand your question... I tried your code but only 2 cells (J2 and J3) are deleted, not the copied cells

Also, why do you use Pastespecial? You could simple copy-paste? Unless you only want to copy values, formulas, etc...

Here are a few examples of your code that you can try:

Code

Sub Test1()
   'No need to select cells...
   Dim rng As Range
   Set rng = Sheet_Hazer.Range(Cells(2, 10), Cells(3, 12))  'You copy 6 cells
   rng.Copy
   Sheet_Hazer.Cells(2, 13).PasteSpecial     'Why using pastespecial?
   Sheet_Hazer.Range(Cells(2, 10), Cells(3, 10)).Value = ""          'you are only delete 2 cells!
   
End Sub

Sub Test2()
   'Simpler
   Sheet_Hazer.Range("J2:L3").Copy
   Sheet_Hazer.Range("M2").PasteSpecial xlPasteValues    'Paste only values
   Sheet_Hazer.Range("J2:L3").ClearContents
End Sub

Sub Test3()
   'Even simpler
   With Sheet_Hazer
      .Range("J2:L3").Copy
      .Range("M2").PasteSpecial xlPasteValues
      .Range("J2:L3").ClearContents
   End With
End Sub

Sub Test4()
   'No paste special
   With Sheet_Hazer
      .Range("J2:L3").Copy .Range("M2")
      .Range("J2:L3").ClearContents
   End With
End Sub

Display More

**royUK** · Feb 8th 2020

I'm not sure if you have used the Sheet Code Name or declared a variable as Sheet_Hazer.

If you re copying constants then

Code

ActiveSheet.Range("J2:L3").Cut ActiveSheet.Range("M2")

karbar · Feb 9th 2020

Quote from royUK
I'm not sure if you have used the Sheet Code Name or declared a variable as Sheet_Hazer.

If you re copying constants then
Code
ActiveSheet.Range("J2:L3").Cut ActiveSheet.Range("M2")

thank's, solved by your response.

**royUK** · Feb 9th 2020

Pleased to help

karbar · Feb 9th 2020

Excuseme, completly solved by this:

Code

            Set rng = Sheet_hazer.Range(Cells(2, 10), Cells(3, 12))
            Set rng4 = Sheet_hazer.Cells(2, 12 + 5)
            rng.Copy
            rng4.PasteSpecial xlPasteValues
            rng.ClearContents

**royUK** · Feb 9th 2020

How is that more efficient than the line of code that I provided? Are you copying cells that have formulas in them?

why delete copied cells by deleting origin, after copy?

Participate now!

Share