Excel 2008 : Select arange value in for each loop

[lior_sh69]

Hi.
i got this code:

Sub A()
 Dim ws As Worksheet
 Dim Ccell As Range
 Set Ccell = Application.InputBox("בחר תא אותו תרצה להעתיק בכל הגליונות:", "בחירת תא", "A1", Type:=8)
   For Each ws In ActiveWorkbook.Worksheets
      If ws.Name <> "1" Then
          ws.Activate
          Ccell.Select
          Selection.Copy
          Sheet1.Select
          ActiveCell.Offset(1, 0).PasteSpecial
      End If
   Next ws
End Sub 

the VB debug me at "Ccell.Select". i just need to select it from ther i can get it on.

thx :]

[JBeaucaire]

I would do it this way, store the range "address" as a string, then copy that range from each ws using this syntax:

Please Login or Register  to view this content.

[lior_sh69]

i change it to:

Sub A()
Dim ws As Worksheet, Ccell As String

Ccell = Application.InputBox("בחר תא אותו תרצה להעתיק בכל הגליונות:", "בחירת תא", "A1", Type:=8).Address

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "1" Then
            ws.Range(Ccell).Copy
            Sheet1.Select
            ActiveCell.Offset(1, 0).PasteSpecial
        End If
    Next ws

End Sub 

thank you for your respone!