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

 

[footoo]

Do you mean? :

and i've format of invoice in sheet 2 to sheet 1001.
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 1001 sheets

 

[merlin777]

I might be able to get you part of the way there.

The INDIRECT function allows you to reference a cell by constructing the cell reference.


e.g. If sheet1 D1 contains '364526 6747 13', and sheet2 D1 has this:

=INDIRECT("sheet1!"&"D"&1)

then the formula in sheet2 D1 will return '364526 6747 13'.

Now, if your third sheet has =INDIRECT("sheet1!"&"D"&2) it will return your second invoice number, 765437 5674 45.

So basically, by repeating this formula on each of your following sheets up to 1001 but incrementing the cell reference by 1 each time then you'll get what you need.

However, the bit I'm not sure about is how you can increment that number automatically. I suspect the answer is a very simple bit of VBA (simple to anyone who understands VBA) but I don't know if there's an easy way.

hope that helps albeit partly.

 

[tharikcse]

Sheet2 — imgbb.com

PO Main Sheet1 — imgbb.com


Screen Shot what i requesting

Thanks

 

[merlin777]

I could give you an alternative to having 1000's of sheets with an invoice on each.

You could have a table on sheet1 with each row having a column for every item of information on your invoices.

Then, on sheet2 you enter an invoice number and use that with VLOOKUP to put a value from each column of that row into various cells on sheet 2 to create an invoice which you can then view or print.

The huge advantage to this is having all your data in one table to which you can then use data filter so you can see all invoices for one person, or one date, or one product and so on.

 

[Rick Rothstein]

Does this macro do what you want...

Sub tharikcse()
  Dim R As Long
  For R = 2 To Cells(Rows.Count, "D").End(xlUp).Row
    Sheets(R).Range("D1").Value = Cells(R, "D").Value
  Next
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (tharikcse) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[tharikcse]

Dear Rick and all members,

Can you help me to minimize the below code ,

when i do for 919 sheets , its showing me the error of " compile error: procedure too large excel "

So, please help me to minimize the code ,

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("D2")
Worksheets("Invoice (2)").Range("F5").Value = Worksheets("PO").Range("D2")
Worksheets("Invoice (2)").Range("F10").Value = Worksheets("PO").Range("D2")


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


'Worksheets("Invoice (919)").Range("F10").Value = Worksheets("PO").Range("D2")
'Worksheets("Invoice (919)").Range("F5").Value = Worksheets("PO").Range("D2")
'Worksheets("Invoice (919)").Range("F10").Value = Worksheets("PO").Range("D2")


End Sub

Thanks

 

[RoryA]

Re: Copy Data from one sheet to another (919) sheets

Is it supposed to be cells D1, F5 and F10 each time? (Your last triplet is different from the first two)

If it is, you can use a loop:

Private Sub CommandButton1_Click()
Dim n as long

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")

For n = 2 to 909
With Worksheets("Invoice (" & n & ")")
.Range("D1").Value = Worksheets("PO").Range("D2")
.Range("F5").Value = Worksheets("PO").Range("D2")
.Range("F10").Value = Worksheets("PO").Range("D2")
end with
next n

End Sub

 

[GTO]

This part is inconsistent:

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("D2")
Worksheets("Invoice (2)").Range("F5").Value = Worksheets("PO").Range("D2")
Worksheets("Invoice (2)").Range("F10").Value = Worksheets("PO").Range("D2")

Is that the way you really want it, that is, everything equals D2?

Mark

 

[RoryA]

@tharikcse

Please do not post the same question multiple times. I've removed your other two postings.