Excel VBA Copy Paste error

Question

I have two sheets of same data

A  B  C
   5  6
4  3  3

Formula 1

Sub Button1_Click()

Dim Current As Worksheet


  Range("A2").Copy _
  Destination:=Range("A1")
  Range("A2").ClearContents         

End Sub

The formula works for me. But I need to apply this script to all sheets,

Formula 2

Dim Current As Worksheet

         ' Loop through all of the worksheets in the active workbook.
         For Each Current In ThisWorkbook.Worksheets
    With Current



           Range("A2").Copy _ Destination:=Range("A1")
  Range("A2").ClearContents
            End With
         Next Current


End Sub

--> It works but values in A1 also deleted. And it is not being used for all sheets. Only active sheets.

FYI.. they are not formula

Linga
– Linga

2016-02-12 05:25:44 +00:00
Commented Feb 12, 2016 at 5:25 — Linga
– Linga, Commented Feb 12, 2016 at 5:25

user4039065 · Accepted Answer · 2016-02-12 05:24:46Z

3

A With ... End With statement can carry a parent worksheet reference along in a block of commands but you must prefix each .Range or .Cells reference with a period (aka full stop) to accept the parent worksheet relationship.

Dim Current As Worksheet

' Loop through all of the worksheets in the active workbook.
For Each Current In ThisWorkbook.Worksheets
    With Current
       .Range("A2").Copy Destination:=.Range("A1")
       .Range("A2").ClearContents
    End With
Next Current

Note .Range and not Range.

answered Feb 12, 2016 at 5:24

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

Sewak Tamang Over a year ago

Thank you so much. It works like a charm. Thank you so much. Great help

Collectives™ on Stack Overflow

Excel VBA Copy Paste error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related