0

A tool has been built to make a process essentially more efficient and with less chance at human error. A lot of it is copy columns/rows and pasting it elsewhere as well as filtering pivot tabs and rows/columns then refreshing. I have some vlookup formulas as well referencing the tables and other worksheets.

This is an example of one of the formulas I use to compare previous data with the data that is produced using the tool.

=IFERROR((VLOOKUP(A5,$A5:$AH170,34,FALSE)-(VLOOKUP(A5,$AN5:$AR485,5,FALSE)))/(VLOOKUP(A5,$A5:$AH170,34,FALSE))," ")

below is a macro built to open the computer library and allow the user to choose a file and then the macro will copy certain columns and paste it in the workbook.

The problem I am having is the worksheet that has the formula I placed above as well as references to other pages hides over 1000 rows and even though the option to unhide is available it will not unhide them. Something within the macro below is causing the issue. Since no errors are popping up I'm finding it very hard to pinpoint the reason.

Sub GetData_Example4()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant, wb As Workbook, shtDest As Worksheet
SaveDriveDir = CurDir
MyPath = Application.DefaultFilePath    'or use "C:\Data"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*")

If FName = False Then
    'do nothing

Else
    Application.ScreenUpdating = False
    Set shtDest = ThisWorkbook.Sheets("Data")
    With Workbooks.Open(FName, ReadOnly:=True)
    .Sheets("Sheet1").ShowAllData
    ActiveSheet.Range("$A$1:$AF$7935").AutoFilter Field:=8, Criteria1:=Array( _
    "CAD", "GBP", "USD"), Operator:=xlFilterValues
    On Error Resume Next
        .Sheets("Sheet1").Range("D1:D10000").Copy shtDest.Range("A2")
         On Error Resume Next
        .Sheets("Sheet1").Range("H1:H10000").Copy shtDest.Range("B2")
        On Error Resume Next
        .Sheets("Sheet1").Range("Q1:Q10000").Copy shtDest.Range("C2")
         On Error Resume Next
        .Sheets("Sheet1").Range("R1:R10000").Copy shtDest.Range("D2")
      On Error Resume Next
        .Sheets("Sheet1").Range("AB1:AB10000").Copy shtDest.Range("E2")
         On Error Resume Next
         .Sheets("Sheet1").Range("AA1:AA10000").Copy shtDest.Range("F2")
         On Error Resume Next
    .Close False
    End With
ActiveSheet.Range("F:F").Select
Range("F2").Activate
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
ActiveSheet.Range("$A$2:$F$7936").AutoFilter Field:=5, Criteria1:=Array( _
    "12", "3", "4"), Operator:=xlFilterValues
Sheets("Data").Visible = False
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
ActiveWorkbook.RefreshAll
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Tmacjoshua
  • 87
  • 1
  • 13
  • 4
    No errors are popping up because you told your code to ignore them and carry on... – Tim Williams Jul 25 '19 at 15:08
  • 2
    Tim is right, get rid of your "On Error Resume Next"-lines. – Horst Jul 25 '19 at 15:09
  • 2
    When you say the option to unhide is available,. do you mean right-click "unhide" or using the filters the macro sets up? Side note: You want to [avoid using activate and select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. – cybernetic.nomad Jul 25 '19 at 15:11
  • 2
    The lines that starts `ActiveSheet.Range("").AutoFilter...` are filtering on values for CAD, GBP and USD in column H and later, 12, 3 and 4 in column E. Anything else would be hidden. To 'unhide' them, you need to remove the filters applied. – CLR Jul 25 '19 at 15:22
  • @cybernetic.nomad right click unhide – Tmacjoshua Jul 26 '19 at 17:40
  • @CLR You are correct I took that code away and it showed it all how would I reference the Data sheet for that part of the code? – Tmacjoshua Jul 26 '19 at 17:55
  • Unhiding rows that way will not unhide filtered rows – cybernetic.nomad Jul 26 '19 at 19:58

0 Answers0