Copying Data from one sheet to another (1000) sheets

[tharikcse]

Dear ,

I've Sheet 1 contains as below,

S.No PO Number Invoice No Qty
1 364526 6747 13
2 765437 5674 45
....
...
1000 73284 76474 765


and i've format of invoice in sheet 2 to sheet 1000.
in this format D1 of sheet2 needs the value of D2 of sheets1
and D1 of sheet3 needs the value of D3 of sheet1 and continues upto 1000 sheets .


please help,

Thanks

 

[GTO]

@Rory:

Thank you. I thought I was losing it! ("I SWEAR I just posted in this thread; where'd it go?)

 

[tharikcse]

Sorry ,

I was new user of this forum , sure i'll not post same threads other timing, in coming future.

Regarding the issue,


Dear RoryA,

Thanks for your reply.

Yes, the value to D1, F5 and F10 in all sheets from D2,C2,B2, and D3, C3 and B3 and up to B920, D920, C920 of sheet PO.

Sorry - can you please check exact as below, typo error sorry

Private Sub CommandButton1_Click()


Worksheets("Invoice").Range("D1").Value = Worksheets("PO").Range("D2")
Worksheets("Invoice").Range("F5").Value = Worksheets("PO").Range("B2")
Worksheets("Invoice").Range("F10").Value = Worksheets("PO").Range("C2")


Worksheets("Invoice (2)").Range("D1").Value = Worksheets("PO").Range("D3")
Worksheets("Invoice (2)").Range("F5").Value = Worksheets("PO").Range("B3")
Worksheets("Invoice (2)").Range("F10").Value = Worksheets("PO").Range("C3")


Worksheets("Invoice (3)").Range("D1").Value = Worksheets("PO").Range("D4")
Worksheets("Invoice (3)").Range("F5").Value = Worksheets("PO").Range("B4")
Worksheets("Invoice (3)").Range("F10").Value = Worksheets("PO").Range("C4")
' ....
' .... up to


'Worksheets("Invoice (919)").Range("D1").Value = Worksheets("PO").Range("D920")
'Worksheets("Invoice (919)").Range("F5").Value = Worksheets("PO").Range("B920")
'Worksheets("Invoice (919)").Range("F10").Value = Worksheets("PO").Range("C920")


End Sub

 

[RoryA]

OK, you can do this:

Private Sub CommandButton1_Click()
Dim n as long, sCode as string
dim wsPO as worksheet

set wsPO = Worksheets("PO")

for n = 1 to 919
   If n = 1 then sCode = vbNullString Else sCode = " (" & n & ")"
 With Worksheets("Invoice" & scode)
.Range("D1").Value = wsPO.cells(n + 1, "D").Value
.Range("F5").Value = wsPO.Cells(n + 1, "B").Value
.Range("F10").Value = wsPO.Cells(n + 1, "C").Value
End With
next n

End Sub

 

[tharikcse]

Dear GTO , Mark ,

Thanks for your reply.

You are right, It was typo error, i really needs as

Worksheets("Invoice (2)").Range("D1").Value = Worksheets("PO").Range("D3")
Worksheets("Invoice (2)").Range("F5").Value = Worksheets("PO").Range("B3")
Worksheets("Invoice (2)").Range("F10").Value = Worksheets("PO").Range("C3")

Thanks

Tharik

 

[tharikcse]

Dear RorY,

Thanks for your reply.
Your are genius - It's work perfect.

In same way i want to sum H36 from Invoice to Invoice (919) sheet ...?
How to do that ? could you please help me in this case also.

With all due respect and Appreciation,

Tharik

 

[RoryA]

What do you want to do with the sum, and why can't you just use a formula?

 

[tharikcse]

Dear Rory,

I used the formula =sum('Invoice:Invoice (919)'!H36)

But it's not working ..

Looking for a solution.

Thanks
Tharik Ahamed

 

[RoryA]

But it's not working ..

What specifically does that mean?

 

[tharikcse]

Dear Rory,

Good Day.

It's Solved . The same formula is worked . weird (before not worked , now its Ok.)

Thanks for your support and help, appreciated.

Tharik.