How to enter data in a specific cell (c8 in all sheets ) when data is on 1st sheet

[dinesh kumar]

hi

can anybudy tell me How to enter data in a specific cell (c8 in all sheets ) when data is on 1st sheet

sheet 1

a1 RM01
a2 RM02
a3 RM03
a4 RM04
a5 RM05

Now i want to enter data automatically at onec (there are 500 sheet so it is so difficult to enter formula aur Manuel enter data in each sheet

RM01 in c8 of sheet 2
and RM02 in c8 of sheet 3
and RM03 in c8 of sheet 4
and RM04 in c8 of sheet 5
and RM05 in c8 of sheet 6


All sheets are same formet plse reply with the code if its done by any VBA code

[Zack Barresse]

Hi there, welcome to the board!

Your request is slightly confusing. What exactly are the requirements here? I'm not sure if you mean when any value is changed in column A of Sheet1 change cell C8 of all sheets, or if you want cell C8 of all sheets to just have the last value in column A of Sheet1. ?

[Kenneth Hobs]

Welcome to the forum!

With 500 sheets, I hope you are backing up your file. Try this on a backup copy.

Sub ColAtoC8()
  Dim c As Range, r As Range
  Set r = Worksheets(1).Range("A1", Worksheets(1).Range("A" & Rows.Count).End(xlUp))

  On Error GoTo EndNow
  For Each c In r
    Worksheets(c.Row + 1).Range("C8").Formula = "=" & c.Parent.Name & "!" & c.Address
  Next c
  
EndNow:
End Sub

[GarysStudent]

Without using the data in sheet1:

Sub AllSheets()
    Dim i As Long
    For i = 2 To Sheets.Count
        Sheets(i).Range("C8").Value = "RM0" & i - 1
    Next i
End Sub

Using the data in sheet1:

Sub AllSheets()
    Dim i As Long
    For i = 2 To Sheets.Count
        Sheets(i).Range("C8").Value = Sheets(1).Cells(i - 1, 1)
    Next i
End Sub

[dinesh kumar]

Kenneth Hobs sir and GarysStudent

the code is not working proper my problem is not yet solved I attached my flie ples see it and help me out and thanks for the codes

[Zack Barresse]

Did you read my post? Please clarify your exact requirements. Rather than guessing at what you want it would be easier for you to just explain it, that way you would get the code you want immediately.

[largefarva]

hi

can anybudy tell me How to enter data in a specific cell (c8 in all sheets ) when data is on 1st sheet

sheet 1

a1 RM01
a2 RM02
a3 RM03
a4 RM04
a5 RM05

Now i want to enter data automatically at onec (there are 500 sheet so it is so difficult to enter formula aur Manuel enter data in each sheet

RM01 in c8 of sheet 2
and RM02 in c8 of sheet 3
and RM03 in c8 of sheet 4
and RM04 in c8 of sheet 5
and RM05 in c8 of sheet 6


All sheets are same formet plse reply with the code if its done by any VBA code

I give you a solution that answers your question so literally that it may be useless to you:

Sub fivehundy()


Dim arr(5) As String
Dim i As Integer, j As Integer, x As Integer


For i = 1 To 5
    arr(i - 1) = Sheets("Sheet1").Range("A" & i).Value
Next i


j = 0
x = 2
Do While Worksheets.Count < 500
    Sheets.Add after:=Worksheets(Worksheets.Count)
    Sheets(x).Range("C8").Value = arr(j)
    If j = 4 Then
        j = 0
    Else
        j = j + 1
    End If
    x = x + 1
Loop


End Sub

All the way to 500 sheets, you're going to get this in C8:
Sheet2: RM01, Sheet3: RM02, Sheet4: RM03, Sheet5: RM04, Sheet6: RM05, Sheet7: RM01, Sheet8: RM02, Sheet9: RM03, etc., etc.

This assumes that only Sheet1 exists and that you need to create the other 499 sheets. Like I said, super literal....

[largefarva]

Kenneth Hobs sir and GarysStudent

the code is not working proper my problem is not yet solved I attached my flie ples see it and help me out and thanks for the codes

Assuming your first bill date is 4-May-13 located on Sheet1 cell A2, this works:

Sub test()
Dim i As Integer
For i = 2 To 100
    Sheets(i).[e3:f5].UnMerge
    With Sheets("Sheet1")
        .Range("A" & i).Copy Destination:=Sheets(i).Range("E4")
        .Range("B" & i).Copy Destination:=Sheets(i).Range("E3")
        .Range("C" & i).Copy Destination:=Sheets(i).Range("B18")
        .Range("D" & i).Copy Destination:=Sheets(i).Range("D18")
        .Range("I" & i).Copy Destination:=Sheets(i).Range("E5")
    End With
    Sheets(i).[e4:f4].Merge
    Sheets(i).[e3:f3].Merge
    Sheets(i).[e5:f5].Merge
Next i


End Sub

Namaste, homie

[yaniya]

I really like your way of expressing the opinion and sharing the information.

[dinesh kumar]

hey thanks
largefarva
thanks so much for the code you got me exactly right, i have one question if all sheet cell e4 and e3 change g2 and g3 and its not marge than what to do thanks once again for solve my problem