Copying Data from one sheet to another (1000) sheets

Page 1 of 2 12 LastLast
Results 1 to 10 of 19

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

  1. #1
    New Member
    Join Date
    Aug 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Sep 2016
    Posts
    1,213
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Aug 2009
    Posts
    1,329
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    New Member
    Join Date
    Aug 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    Board Regular
    Join Date
    Aug 2009
    Posts
    1,329
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    30,490
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    5 Thread(s)

    Default 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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    New Member
    Join Date
    Aug 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    30,625
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #9
    MrExcel MVP
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    6,150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #10
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    30,625
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com