0

I want to copy data in cell "C1" in sheet1 and paste to dynamic range "AA:AA" (dynamic base on range "Z") in the same sheet. After that I want it loops to copy from cell "c1" from each sheet and paste to range "AA:AA" each sheet , do like this till finish all sheets, but it is fail.

Error : "Select method of Range class failed" on "Sheets(i).Range("Z21").End(xlDown).Offset(0, 1).Select" 

Sub copy()
Dim i As Long
Dim s As Worksheet

Application.ScreenUpdating = False
For i = 1 To Sheets.Count

If Worksheets(i).Range("C1").Value <> "" Then
Sheets(i).Range("Z21").End(xlDown).Offset(0, 1).Select
Range(Selection, Selection.End(xlUp).Offset(1, 0)).Select = Worksheets(i).Range("C1").Value
End If
Next
End Sub
v1787v
  • 45
  • 1
  • 5
  • 1
    Perhaps give [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) a read. – BigBen Mar 12 '20 at 15:03
  • You haven't actually asked a question. – SJR Mar 12 '20 at 15:25
  • Sorry sir, my above code does not work so i need help from someone here – v1787v Mar 12 '20 at 18:59
  • Error : "Select method of Range class failed" on "Sheets(i).Range("Z21").End(xlDown).Offset(0, 1).Select" – v1787v Mar 12 '20 at 19:03
  • What I want : I want to copy data in cell "C1" in sheet1 and paste to dynamic range "AA:AA" (dynamic base on range "Z") in the same sheet1. After that i want it loops to copy data in cell "C1" of another sheet and paste to another sheet too , do like this for the rest sheet. But it is fail, sir – v1787v Mar 12 '20 at 19:07

0 Answers0