VBA help - Copy data from another workbook if data is present and is more than 0

[edwardtong694]

Hi Guys,

I was hoping someone could help.I have the below code which i can use to browse to another spreadsheet and copy and paste data into a spreadsheet I am working on.

This works well, however I need to only copy data which appears in the spreadsheet I am browsing to so in the openwb datasheet Range D where a) there is a value and b) this value is more than 0.

I think I might need to write a for loop for this.. can anyone advise.

Thanks in advance.

Ed

Sub GetFile()
Application.ScreenUpdating = False
Dim fNameAndPath As Variant, openWb As Workbook, datesSheet As Worksheet, reportSheet As Worksheet, currentWb As Workbook, c As Range, R As Range
    
fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSM), *.XLSM", Title:="Select File To Be Opened")


If fNameAndPath = False Then Exit Sub


Set openWb = Workbooks.Open(fNameAndPath)
Set currentWb = ThisWorkbook
Set reportSheet = openWb.Sheets("Report")
Set datesSheet = openWb.Sheets("Dates")
 
   
        datesSheet.Range(datesSheet.Cells(9, 10), datesSheet.Cells(Rows.Count, 10).End(xlUp)).Copy _
        Destination:=currentWb.Sheets("Projects overview").Range("c23") 'Copy the data from col J and paste into Cells starting C23.


    openWb.Close (False)
 
End Sub

 

[JackDanIce]

Have you tried to apply a filter to column D to show 0 values and then delete the visible cells (excluding the header)? Code for that would be:

Dim r as Long
With currentwb.Sheets("Projects overview")
  If .AutoFilterMode Then .AutoFilterMode = False
  r = .Range("D" & .Rows.Count).End(xlUp).Row

  With .Range("D1").Resize(r) 'Assumes you have headers in row 1
     .AutoFilter
     .AutoFilter field:=1, Criteria1:=0
     If .Specialcells(xlCellTypeVisible).Count > 1 Then 
       .Offset(1).Resize(r-1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
     End If
  End With

  .AutoFilterMode = False
End With