# Thread: Copying Data from one sheet to another (1000) sheets

1. ## Copying Data from one sheet to another (1000) sheets

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

2. ## Re: Copying Data from one sheet to another (1000) sheets

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

3. ## Re: Copying Data from one sheet to another (1000) sheets

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.

4. ## Re: Copying Data from one sheet to another (1000) sheets

Sheet2 — imgbb.com

PO Main Sheet1 — imgbb.com

Screen Shot what i requesting

Thanks

5. ## Re: Copying Data from one sheet to another (1000) sheets

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.

6. ## Re: Copying Data from one sheet to another (1000) sheets

Does this macro do what you want...
Code:
```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.

7. ## Re: Copying Data from one sheet to another (1000) sheets

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

8. ## 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:

Code:
```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```

9. ## Re: Copying Data from one sheet to another (1000) sheets

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

10. ## Re: Copying Data from one sheet to another (1000) sheets

@tharikcse

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•