Creating a New Sheet with all relevant data from a different table.

[steveh8204]

Hi,

is it possible to create code to go through a table and if a value is met transfer the whole contents of the row into a new sheet totalling all relevant values.

For example creating a sheet with all the row information for every row that has the value in Column F higher than 0?

Thanks in advance.

 

[steveh8204]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet
    Set srcWS = Sheets("Sheet1")
    With srcWS
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("F1:F" & LastRow).AutoFilter Field:=1, Criteria1:=">0"
        .Range("F2:F" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("GraphicWIP").Cells(Sheets("GraphicWIP").Rows.Count, "A").End(xlUp).Offset(1, 0)
        .Range("F1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

What is the easiest way to change the criteria to include values both above and below zero? I need to adapt for one of my other sheets.

I tried changing the following:


Criteria1:=">0"


to <>0 and >0< but neither worked. The first pretty much brought up everything and the latter just locked my PC.

 

[mumps]

If you want all the values except zero then "<>0" should work.

 

[steveh8204]

If you want all the values except zero then "<>0" should work.

On a different sheet I've updated this for it's not quite doing what I expected.

On this one there are both minus and positive values in some rows that I need to pick up. If I use just "<0" or ">0" it picks the different values up fine but if I use "<>0" it copies across all the rows even though some have no values "0" in them.

 

[Yongle]

Excel is not consistent !

Test with these 6 values
(a) EMPTY Cell
(b) 0
(c) 0.00009 formatted to 0 decimal places (looks like 0)
(d) - 0.00009 formatted to 0 decimal places (looks like 0)
(e) 125
(f) -125

Now filter - using Number Filters options > 0, < 0 , < > 0 , = 0

and filter like this
> 0
RESULT b + c

< 0
RESULT d + f

< > 0
RESULT a + c + d + e + f

= 0
RESULT b + c + d

The anomoly - according to Excel c and d are BOTH = 0 AND <> 0

 

[mumps]

Do the cells have the number "0" or are they blank?

 

[steveh8204]

Do the cells have the number "0" or are they blank?

It seems to pick up the cells as you said but when I put an empty zero in a cell it seems to send it haywire.


It filters on the current sheet (as opposed to copying to the different one) when I put <>.


When I use = 0 its even worse. It says it hits a runtime error 1004 "no cells found" and it filters all the values on the current sheet out.


I've even formatted the rows to "Number" but that doesnt fix it.

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you may be currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[steveh8204]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you may be currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

Thanks:

https://www.dropbox.com/s/omm513a0ccwyl6j/test file.xlsm?dl=0

 

[steveh8204]

Sorry had issues uploading the file. It's ok now Ive just re-uploaded it. The differences should be on the "SAP" sheet and be anything that has a variance above or below zero inputted to "Issues"'

Thanks

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet
    Set srcWS = Sheets("SAP")
    With srcWS
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("F1:F" & LastRow).AutoFilter Field:=1, Criteria1:="<>0", Criteria2:="<>"
        .Range("F2:F" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Issues").Cells(Sheets("Issues").Rows.Count, "A").End(xlUp).Offset(1, 0)
        .Range("F1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub