help its copying only visible cells

[jordanburch]

Hey GUys,

This code works great but it only copies visible cells in the data. Can you help me to get all rows ?

Sub IMPORTrawdata()
Worksheets.Add().Name = "DCAS"
Dim MyFile As String
Dim erow As Long
Dim Filepath As String
Dim wb1 As Workbook, wb2 As Workbook
Dim data_wbk4 As String
Dim data_wbk2 As String

Dim fn As String
data_wbk4 = InputBox("Enter FY I.E. FY20", Default:="FY20")
data_wbk2 = InputBox("Enter month I.E. 08-MAY20", Default:="08-MAY20")
fn = Left(data_wbk2, 6)

Application.ScreenUpdating = False
Set wb1 = ThisWorkbook
Filepath = "path\" & data_wbk4 & "\" & fn & "\"
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0 And MyFile <> "suspense automation.xlsm"

erow = wb1.Sheets("table").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Set wb2 = Workbooks.Open(Filepath & MyFile)
With wb2
.Sheets("Table").Range("A2:bm3000").Copy Destination:=wb1.Worksheets("table").Cells(erow, 1)
.Close savechanges:=False
End With
MyFile = Dir
Loop
Application.ScreenUpdating = True
End Sub

Jordan

 

[JLGWhiz]

Try adding the line in red

With wb2
    .Sheets("Table").Rows.Hidden = False
    .Sheets("Table").Range("A2:bm3000").Copy Destination:=wb1.Worksheets("table").Cells(erow, 1)
    .Close savechanges:=False
End With

 

[jordanburch]

t

Try adding the line in red

With wb2
    .Sheets("Table").Rows.Hidden = False
    .Sheets("Table").Range("A2:bm3000").Copy Destination:=wb1.Worksheets("table").Cells(erow, 1)
    .Close savechanges:=False
End With

thats seems right but its not working for whatever reason. There is a filter on one of the columns if that matters.

 

[JLGWhiz]

OK, try it this way

With wb2
    .Sheets("Table").AutoFilterMode = False  
    .Sheets("Table").Range("A2:bm3000").Copy Destination:=wb1.Worksheets("table").Cells(erow, 1)
    .Close savechanges:=False
End With

Although the Rows.Hidden = False should have done the same thing. But Excel has its Quirks.

If that does not work then try

.Sheets("Table").ShowAllData

Is sheets("Table") a pivot table?

 

[jordanburch]

OK, try it this way

With wb2
    .Sheets("Table").AutoFilterMode = False 
    .Sheets("Table").Range("A2:bm3000").Copy Destination:=wb1.Worksheets("table").Cells(erow, 1)
    .Close savechanges:=False
End With

Although the Rows.Hidden = False should have done the same thing. But Excel has its Quirks.

If that does not work then try

.Sheets("Table").ShowAllData

Is sheets("Table") a pivot table?
[/QUOTE

awesome the top one worked ! Thank you sir!