PDA

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



dinesh kumar
09-10-2013, 06:07 AM
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
09-10-2013, 07:35 AM
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
09-10-2013, 09:50 AM
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
09-10-2013, 10:32 AM
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
09-11-2013, 05:52 AM
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


:hi:

Zack Barresse
09-11-2013, 07:06 AM
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
09-11-2013, 09:33 PM
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
09-11-2013, 10:19 PM
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


:hi:


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
09-12-2013, 03:53 AM
I really like your way of expressing the opinion and sharing the information.

dinesh kumar
09-12-2013, 11:22 PM
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