Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: VBA - Copy cells in Excel but avoid filtered items

  1. #1
    Join Date
    4th August 2010
    Posts
    39

    VBA - Copy cells in Excel but avoid filtered items

    I am trying to find last used cell in Column E, copy & paste to different sheet and then move up until I reach header but I need to avoid Filtered items and a header.
    Is this even possible?
    Code I tried copy’s filtered items also which I need to avoid.

    VB:
    Sub Macro1() 
         '
         ' Macro1 Macro
         '
         '
        Sheets("owssvr").Select 
        Range("E" & Cells.Rows.Count).End(xlUp).Copy 
        Sheets("Sheet2").Select 
        Range("K2").PasteSpecial 
         
    End Sub 
    
    

    Thank You

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    30th November 2010
    Location
    Nicosia, Cyprus
    Posts
    451

    Re: VBA - Copy cells in Excel but avoid filtered items

    Hello CROforce,

    Try the macro below. I used the special cells method to "select" only the visible cells (avoid filtered items) and then copy them to your destination.
    VB:
    Sub Macro1() 
         
        Dim LastRow As Long 
         
        With Worksheets("owssvr") 
             
            LastRow = .Range("E" & Rows.Count).End(xlUp).Row 
             
            .Range("E2:E" & LastRow).SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets("Sheet2").Range("K2") 
             
        End With 
    End Sub 
     
    Best Regards 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    4th August 2010
    Posts
    39

    Re: VBA - Copy cells in Excel but avoid filtered items

    This is good but unfortunately it is not going to work as I need to have data horizontally entered 1 by one.
    I have attached sample spreadsheet and here is quick overview what I am trying to achieve and there is probably better method for it.

    Under “Sheet2”, Column “E1” is where I would have Factory Name entered.
    Based on that requirement, I need to be able to copy items from “owssvr” work sheet “B” column that belong to this factory and paste them into “Sheet2” column “K2”, “N2”, etc (In my case Apple and Orange belong to NZ Factory). Next items I need to copy are the Scores for each question. Now based on Factory Name and Item name under the work sheet “owssvr”, I need to look for scores for each question and place them under appropriate column/ cell under “Sheet 2” work sheet.

    I would greatly appreciate any pointers how to achieve this.

    Best Regards,
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    30th November 2010
    Location
    Nicosia, Cyprus
    Posts
    451

    Re: VBA - Copy cells in Excel but avoid filtered items

    Hi CROforce,

    Try the macro below.
    VB:
    Option Explicit 
    Option Base 1 
     
     
    Sub Macro1() 
         
         
        Dim wsData As Worksheet, wsOutput As Worksheet 
        Dim rngArea As Range 
        Dim rngData() As Variant 
        Dim IndexNo As Long, NoRows As Long, AreaRows As Long, LastRow As Long 
        Dim ColsCnt As Long, arrInput As Long, arrOutput As Long, ColsReq As Long 
         
         
        Set wsData = Worksheets("owssvr") 
        Set wsOutput = Worksheets("Sheet2") 
         
         
        LastRow = wsData.Range("A" & Rows.Count).End(xlUp).Row 
         
         
        Set rngArea = wsData.Range("B2:C" & LastRow).SpecialCells(xlCellTypeVisible) 
         
         
        NoRows = wsData.Range("B2:C" & LastRow).Columns(1).SpecialCells(xlCellTypeVisible).Count 
        Redim rngData(NoRows, 2) 
        arrInput = 1 
         
         
         'Populate array with filtered data using the Areas method
        For Each rngArea In rngArea.Areas 
            AreaRows = rngArea.Rows.Count 
            For IndexNo = 1 To AreaRows 
                rngData(arrInput, 1) = rngArea.Cells(IndexNo, 1).Value 
                rngData(arrInput, 2) = rngArea.Cells(IndexNo, 2).Value 
                arrInput = arrInput + 1 
            Next IndexNo 
        Next rngArea 
         
         
        ColsReq = (NoRows * 3) + 9 
        arrOutput = 1 
         
         
         'Factory Name
        wsOutput.Range("E1").Value = rngData(1, 2) 
         
         'Products starting from column K
        For ColsCnt = 11 To ColsReq Step 3 
            wsOutput.Cells(2, ColsCnt).Value = rngData(arrOutput, 1) 
            arrOutput = arrOutput + 1 
        Next ColsCnt 
         
         
    End Sub 
    
    
    Best regards
    Last edited by michaelnicolas; November 11th, 2011 at 16:39. Reason: Macro Correction after second thought

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    4th August 2010
    Posts
    39

    Re: VBA - Copy cells in Excel but avoid filtered items

    Thank you for your help

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. VBA Excel to PPT: Copy filtered range and paste into multiple PPT slides
    By macarius in forum Excel and/or Powerpoint Help
    Replies: 9
    Last Post: March 26th, 2013, 00:55
  2. Only process filtered items
    By Bas123 in forum EXCEL HELP
    Replies: 1
    Last Post: August 12th, 2011, 20:34
  3. Copy filtered cells to userform listbox
    By RMD287 in forum EXCEL HELP
    Replies: 2
    Last Post: May 21st, 2011, 10:33
  4. Copy Visible Cells From Filtered Sheet
    By Sgt. Schultz in forum EXCEL HELP
    Replies: 10
    Last Post: October 23rd, 2007, 09:08
  5. Copy filtered data in to cells on worksheet
    By jonespa10 in forum EXCEL HELP
    Replies: 3
    Last Post: January 26th, 2006, 00:46

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
  •  
porno