Results 1 to 16 of 16

Find specific cell, copy row from several tabs and paste in summary sheet

Threaded View

  1. 09-03-2012, 07:41 AM #1
    helnil
    helnil is offline
    Registered User
    Join Date
    09-03-2012
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    8

    Find specific cell, copy row from several tabs and paste in summary sheet

    Hello!

    Trying to do my first macro ever...pasted togehter some code that i've found.

    So what I'm trying to do is a summary page that will collect the data (both text and numbers) in columns A:AA linked to a specific row.
    The row represents a certain package number, that you should type in on the first page, then click on run macro.
    It should then go through all the tabs and collect the rows from each tab = 3 in total

    Please please help me!! Been doing this for 2 days now...


    Example1.xlsm


    Here's my file and the code i've got so far which doesn't return any value...

    
Sub Find_Package_Number()
'
'DoCmd.Hourglass True
'Application.ScreenUpdating = False
Application.EnableCancelKey = xlDisabled

Dim Value As Integer

WP_row = 11
Find_row = 11

    Sheets("Trend Analysis").Select
    Range("A11:AB500").Select
    Selection.ClearContents
    
    WP_no = Cells(2, 31).Value
    
Sheets("Issue 1").Select
    Range("package_no").Select
        Selection.Find(What:=WP_no, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
             
        Find_row = ActiveCell.Row
        
        WP1 = Cells(Find_row, 1).Value
        WP2 = Cells(Find_row, 2).Value
        WP3 = Cells(Find_row, 3).Value
        WP4 = Cells(Find_row, 4).Value
        WP5 = Cells(Find_row, 5).Value
        WP6 = Cells(Find_row, 6).Value
        WP7 = Cells(Find_row, 7).Value
        WP8 = Cells(Find_row, 8).Value
        WP9 = Cells(Find_row, 9).Value
        WP10 = Cells(Find_row, 10).Value
        WP11 = Cells(Find_row, 11).Value
        WP12 = Cells(Find_row, 12).Value
        WP13 = Cells(Find_row, 13).Value
        WP14 = Cells(Find_row, 14).Value
        WP15 = Cells(Find_row, 15).Value
        WP16 = Cells(Find_row, 16).Value
        WP17 = Cells(Find_row, 17).Value
        WP18 = Cells(Find_row, 18).Value
        WP19 = Cells(Find_row, 19).Value
        WP20 = Cells(Find_row, 20).Value
        WP21 = Cells(Find_row, 21).Value
        WP22 = Cells(Find_row, 22).Value
        WP23 = Cells(Find_row, 23).Value
        WP24 = Cells(Find_row, 24).Value
        WP25 = Cells(Find_row, 25).Value
        WP26 = Cells(Find_row, 26).Value
        WP27 = Cells(Find_row, 27).Value
    
    Sheets("Trend Analysis").Select
                   
        Cells(WP_row, 1).Value = WP1
        Cells(WP_row, 2).Value = WP2
        Cells(WP_row, 3).Value = WP3
        Cells(WP_row, 4).Value = WP4
        Cells(WP_row, 5).Value = WP5
        Cells(WP_row, 6).Value = WP6
        Cells(WP_row, 7).Value = WP7
        Cells(WP_row, 8).Value = WP8
        Cells(WP_row, 9).Value = WP9
        Cells(WP_row, 10).Value = WP10
        Cells(WP_row, 11).Value = WP11
        Cells(WP_row, 12).Value = WP12
        Cells(WP_row, 13).Value = WP13
        Cells(WP_row, 14).Value = WP14
        Cells(WP_row, 15).Value = WP15
        Cells(WP_row, 16).Value = WP16
        Cells(WP_row, 17).Value = WP17
        Cells(WP_row, 18).Value = WP18
        Cells(WP_row, 19).Value = WP19
        Cells(WP_row, 20).Value = WP20
        Cells(WP_row, 21).Value = WP21
        Cells(WP_row, 22).Value = WP22
        Cells(WP_row, 23).Value = WP23
        Cells(WP_row, 24).Value = WP24
        Cells(WP_row, 25).Value = WP25
        Cells(WP_row, 26).Value = WP26
        Cells(WP_row, 27).Value = WP27
            
        WP_row = WP_row + 1
          
Sheets("Issue 2").Select
    Range("package_no2").Select
        Selection.Find(What:=WP_no, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
             
        
        Find_row = ActiveCell.Row
        
        WP1 = Cells(Find_row, 1).Value
        WP2 = Cells(Find_row, 2).Value
        WP3 = Cells(Find_row, 3).Value
        WP4 = Cells(Find_row, 4).Value
        WP5 = Cells(Find_row, 5).Value
        WP6 = Cells(Find_row, 6).Value
        WP7 = Cells(Find_row, 7).Value
        WP8 = Cells(Find_row, 8).Value
        WP9 = Cells(Find_row, 9).Value
        WP10 = Cells(Find_row, 10).Value
        WP11 = Cells(Find_row, 11).Value
        WP12 = Cells(Find_row, 12).Value
        WP13 = Cells(Find_row, 13).Value
        WP14 = Cells(Find_row, 14).Value
        WP15 = Cells(Find_row, 15).Value
        WP16 = Cells(Find_row, 16).Value
        WP17 = Cells(Find_row, 17).Value
        WP18 = Cells(Find_row, 18).Value
        WP19 = Cells(Find_row, 19).Value
        WP20 = Cells(Find_row, 20).Value
        WP21 = Cells(Find_row, 21).Value
        WP22 = Cells(Find_row, 22).Value
        WP23 = Cells(Find_row, 23).Value
        WP24 = Cells(Find_row, 24).Value
        WP25 = Cells(Find_row, 25).Value
        WP26 = Cells(Find_row, 26).Value
        WP27 = Cells(Find_row, 27).Value
    
    Sheets("Trend Analysis").Select
        
        Cells(WP_row, 1).Value = WP1
        Cells(WP_row, 2).Value = WP2
        Cells(WP_row, 3).Value = WP3
        Cells(WP_row, 4).Value = WP4
        Cells(WP_row, 5).Value = WP5
        Cells(WP_row, 6).Value = WP6
        Cells(WP_row, 7).Value = WP7
        Cells(WP_row, 8).Value = WP8
        Cells(WP_row, 9).Value = WP9
        Cells(WP_row, 10).Value = WP10
        Cells(WP_row, 11).Value = WP11
        Cells(WP_row, 12).Value = WP12
        Cells(WP_row, 13).Value = WP13
        Cells(WP_row, 14).Value = WP14
        Cells(WP_row, 15).Value = WP15
        Cells(WP_row, 16).Value = WP16
        Cells(WP_row, 17).Value = WP17
        Cells(WP_row, 18).Value = WP18
        Cells(WP_row, 19).Value = WP19
        Cells(WP_row, 20).Value = WP20
        Cells(WP_row, 21).Value = WP21
        Cells(WP_row, 22).Value = WP22
        Cells(WP_row, 23).Value = WP23
        Cells(WP_row, 24).Value = WP24
        Cells(WP_row, 25).Value = WP25
        Cells(WP_row, 26).Value = WP26
        Cells(WP_row, 27).Value = WP27
            
        WP_row = WP_row + 1

Sheets("Issue 3").Select
    Range("package_no3").Select
        Selection.Find(What:=WP_no, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
             
        
        Find_row = ActiveCell.Row
        
        WP1 = Cells(Find_row, 1).Value
        WP2 = Cells(Find_row, 2).Value
        WP3 = Cells(Find_row, 3).Value
        WP4 = Cells(Find_row, 4).Value
        WP5 = Cells(Find_row, 5).Value
        WP6 = Cells(Find_row, 6).Value
        WP7 = Cells(Find_row, 7).Value
        WP8 = Cells(Find_row, 8).Value
        WP9 = Cells(Find_row, 9).Value
        WP10 = Cells(Find_row, 10).Value
        WP11 = Cells(Find_row, 11).Value
        WP12 = Cells(Find_row, 12).Value
        WP13 = Cells(Find_row, 13).Value
        WP14 = Cells(Find_row, 14).Value
        WP15 = Cells(Find_row, 15).Value
        WP16 = Cells(Find_row, 16).Value
        WP17 = Cells(Find_row, 17).Value
        WP18 = Cells(Find_row, 18).Value
        WP19 = Cells(Find_row, 19).Value
        WP20 = Cells(Find_row, 20).Value
        WP21 = Cells(Find_row, 21).Value
        WP22 = Cells(Find_row, 22).Value
        WP23 = Cells(Find_row, 23).Value
        WP24 = Cells(Find_row, 24).Value
        WP25 = Cells(Find_row, 25).Value
        WP26 = Cells(Find_row, 26).Value
        WP27 = Cells(Find_row, 27).Value
    
    Sheets("Trend Analysis").Select
        
        Cells(WP_row, 1).Value = WP1
        Cells(WP_row, 2).Value = WP2
        Cells(WP_row, 3).Value = WP3
        Cells(WP_row, 4).Value = WP4
        Cells(WP_row, 5).Value = WP5
        Cells(WP_row, 6).Value = WP6
        Cells(WP_row, 7).Value = WP7
        Cells(WP_row, 8).Value = WP8
        Cells(WP_row, 9).Value = WP9
        Cells(WP_row, 10).Value = WP10
        Cells(WP_row, 11).Value = WP11
        Cells(WP_row, 12).Value = WP12
        Cells(WP_row, 13).Value = WP13
        Cells(WP_row, 14).Value = WP14
        Cells(WP_row, 15).Value = WP15
        Cells(WP_row, 16).Value = WP16
        Cells(WP_row, 17).Value = WP17
        Cells(WP_row, 18).Value = WP18
        Cells(WP_row, 19).Value = WP19
        Cells(WP_row, 20).Value = WP20
        Cells(WP_row, 21).Value = WP21
        Cells(WP_row, 22).Value = WP22
        Cells(WP_row, 23).Value = WP23
        Cells(WP_row, 24).Value = WP24
        Cells(WP_row, 25).Value = WP25
        Cells(WP_row, 26).Value = WP26
        Cells(WP_row, 27).Value = WP27
            
Exit Sub

Application.ScreenUpdating = True
    
End Sub
    Last edited by helnil; 09-03-2012 at 08:54 AM.
    Reply With QuoteRegister To Reply
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1