+ Reply to Thread
Results 1 to 47 of 47

Copy\Paste multiple criteria based rows to new sheet

Hybrid View

  1. #1
    Registered User
    Join Date
    03-25-2008
    Posts
    66

    Copy\Paste multiple criteria based rows to new sheet

    Hi All,

    I need to copy specific rows that have a specific value in colum B and Colum M to new worksheets.

    So from attached screenshot , say for an example macro shud filter rows that have "BigPond" in coloum B ,"RG2" in column M & "INT" in column W and i dont want all cells in these rows , i only want copy cells under column C,AK,AL,AM (in this order) to my other excel sheet that is named "BigPond" and it should paste it starting from Row5. ??

    I want to avoid recording a macro as it selects a range and i am expecting more new rows every week so if a record a macro the cell range for selection changes and i get incorrect result.

    Any HELP , ADVICE or a code for this ..plzzzz??

    See the attached screenshot to see my main worksheet from where all this data is coming from...

    Thnkz.
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    HI

    Can you please attach a sample workbook rather than a gif. If the columns all have headings, it may be possible to use an advanced filter to do this.


    rylo

  3. #3
    Registered User
    Join Date
    03-25-2008
    Posts
    66
    Quote Originally Posted by rylo
    HI

    Can you please attach a sample workbook rather than a gif. If the columns all have headings, it may be possible to use an advanced filter to do this.


    rylo
    Okies..Sample worksheet attached hereby.
    Thats where data is coming from ..

    thnkz.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    This assumes that you have an existing sheet called BigPond. Part of the process will delete any existing data on this sheet.

    Nothing will be copied across from your sample sheet as you don't have an entry in column M with the data RG2.

    rylo

    Sub aaa()
      Dim OutSH As Worksheet
      Set OutSH = Sheets("BigPond")
      OutSH.Cells.ClearContents
      
      'So from attached screenshot , say for an example macro shud filter rows that have
      '"BigPond" in coloum B ,"RG2" in column M & "INT" in column W and
      'i only want copy cells under column C,AK,AL,AM (in this order)
      'to my other excel sheet that is named "BigPond" and it should paste it starting from Row5. ??
      Sheets("Sites Data").Activate
      Range("A1").Select
      
      OutSH.Range("A1:C1").Value = Array("BU", "RG2", "Ext / Int")
      OutSH.Range("A2:C2").Value = Array("BigPond", "RG2", "INT")
      
      OutSH.Range("A4:D4").Value = Array("Prefered Name", "RG2 Apps Shakeout - Verify users can login successfully", "RG2 Apps Shakeout - Verify users have correct roles", "RG2 Apps Shakeout - Tester's Siebel login ID")
      Range("A1").CurrentRegion.AdvancedFilter action:=xlFilterCopy, criteriarange:=OutSH.Range("A1:C2"), copytorange:=OutSH.Range("A4:D4")
      
      
    End Sub

  5. #5
    Registered User
    Join Date
    03-25-2008
    Posts
    66
    Quote Originally Posted by rylo
    Hi

    This assumes that you have an existing sheet called BigPond. Part of the process will delete any existing data on this sheet.

    Nothing will be copied across from your sample sheet as you don't have an entry in column M with the data RG2.

    rylo

    Sub aaa()
      Dim OutSH As Worksheet
      Set OutSH = Sheets("BigPond")
      OutSH.Cells.ClearContents
      
      'So from attached screenshot , say for an example macro shud filter rows that have
      '"BigPond" in coloum B ,"RG2" in column M & "INT" in column W and
      'i only want copy cells under column C,AK,AL,AM (in this order)
      'to my other excel sheet that is named "BigPond" and it should paste it starting from Row5. ??
      Sheets("Sites Data").Activate
      Range("A1").Select
      
      OutSH.Range("A1:C1").Value = Array("BU", "RG2", "Ext / Int")
      OutSH.Range("A2:C2").Value = Array("BigPond", "RG2", "INT")
      
      OutSH.Range("A4:D4").Value = Array("Prefered Name", "RG2 Apps Shakeout - Verify users can login successfully", "RG2 Apps Shakeout - Verify users have correct roles", "RG2 Apps Shakeout - Tester's Siebel login ID")
      Range("A1").CurrentRegion.AdvancedFilter action:=xlFilterCopy, criteriarange:=OutSH.Range("A1:C2"), copytorange:=OutSH.Range("A4:D4")
      
      
    End Sub

    THanks a ton rylo...much appriciated.
    And as you said there no results ..now lets say if we take this a step further and i sai whole scenario remains same and we delete Column M "RG2" sites that display "0" or "Blank" ...that way only RG2 data will remain in this sheet...

    Secondly,i would like to know if this data(that we pulling) goes to a already saved sheet "BigPond" , deletes previously remaining data and copy/pastes this info as per the format & headers specified in attached Sample - BigPond sheet..currently i am doing this manually but i wud like to see if this can be achived by a macro...so only A,B,C,D columns shud be filled in BigPond sheet.....
    where BigPond.ColumnA = Site Data.ColumnC
    " .ColumnB=SiteData.AK
    ".ColumnC=SiteData.AL
    ".ColumnD=SiteData.AM

    So for this ex, we are selecting "BigPond" , non 0,Blank "RG2" , "INT" sites's cells values present in Column C,AK,AL,AM from main sheet (i.e. Site Data) to BigPond Sheet Columns A,B,C,D ...than same for "EXT" ...that way..

    I would really appriciate this can b automated in a macro as i m tired of doing it manually...

    I hope i've confused u...let me know if things r not clear.
    Attached Files Attached Files
    Last edited by namz; 05-24-2008 at 09:26 PM.

  6. #6
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    See how this goes. There is some formatting that hasn't been done, but I'm after the data extract and formula correctness.

    Sub aaa()
      Dim OutSH As Worksheet
      Set OutSH = Sheets("BigPond")
      OutSH.Cells.ClearContents
      
      'So from attached screenshot , say for an example macro shud filter rows that have
      '"BigPond" in coloum B ,"RG2" in column M & "INT" in column W and
      'i only want copy cells under column C,AK,AL,AM (in this order)
      'to my other excel sheet that is named "BigPond" and it should paste it starting from Row5. ??
      Sheets("Sites Data").Activate
      Range("A1").Select
      
      OutSH.Range("A1:C1").Value = Array("BU", "RG2", "Ext / Int")
      OutSH.Range("A2:C2").Value = Array("BigPond", ">0", "INT")
      
      OutSH.Range("A4:E4").Value = Array("Prefered Name", "RG2 Apps Shakeout - Verify App Deployment is complete", "RG2 Apps Shakeout - Verify users can login successfully", "RG2 Apps Shakeout - Verify users have correct roles", "Ext / Int")
      Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, criteriarange:=OutSH.Range("A1:B2"), copytorange:=OutSH.Range("A4:E4")
      
      With OutSH
        .Range("A4:E" & .Cells(Rows.Count, 1).End(xlUp).Row).Sort order1:=xlDescending, key1:=.Range("E4"), header:=xlYes
        i = 5
        While .Cells(i, "E").Value = "INT"
          i = i + 1
        Wend
        breakrow = i
        .Cells(breakrow, "A").Resize(5, 1).EntireRow.Insert shift:=xlDown
        .Cells(breakrow, "A").Value = "INT Ttoal"
        .Cells(breakrow, "B").Formula = "=SUM(B5:B" & breakrow - 1 & ")/COUNTA($A5:$A" & breakrow - 1 & ")"
        .Cells(breakrow, "B").AutoFill Destination:=.Range(.Cells(breakrow, "B"), .Cells(breakrow, "E"))
        .Range("E4").Value = "Overall"
        .Range("E5").Formula = "=SUM(B5:D5)/3"
        .Range("D5").Copy
        .Range("E5").PasteSpecial (xlPasteFormats)
        .Range("E5").AutoFill Destination:=.Range("E5:E" & breakrow - 1)
        
        'formulas for EXT
        breakrow = breakrow + 5
        lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
        .Cells(lastrow + 1, "A").Value = "EXT Total"
        .Cells(lastrow + 1, "B").Formula = "=sum(B" & breakrow & ":B" & lastrow & ")/counta($A" & breakrow & ":$A" & lastrow & ")"
        .Range("B" & lastrow + 1).Copy Destination:=.Range("B" & lastrow + 1 & ":E" & lastrow + 1)
        .Range("E5").Copy Destination:=.Range("E" & breakrow)
        .Range("E" & breakrow).AutoFill Destination:=.Range("E" & breakrow & ":E" & lastrow)
        lastrow = lastrow + 2
        .Range("A" & lastrow).Value = "Grand Total"
        
        intlastrow = .Range("A4").End(xlDown).Row - 1
        .Range("B" & lastrow).Formula = "=sum(B5:B" & intlastrow & ",B" & breakrow & ":B" & lastrow - 2 & ")/counta($A5:$A" & intlastrow & ",$A" & breakrow & ":$A" & lastrow - 2 & ")"
        .Range("B" & lastrow).AutoFill Destination:=.Range("B" & lastrow & ":E" & lastrow)
        
        .Range("B4:D4").Replace what:="RG2 Apps Shakeout - ", replacement:=""
      End With
    End Sub
    rylo

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1