Copying data from a closed workbook into an open workbook ignoring excel filter?

[reach78]

Hi All,

I found a macro while googling and have tweaked it to my preference. It does the following actions

1) Open file picker
2) I select the source file I want to get data from
3) data from 'sheet1' is copied into worksheet 'delivery' in my opened workbook
4) macro ends

What I have found is that, if someone has applied a filter to the source file, my macro will only copy the data that is visible (after filtering). How do I get the macro to copy ALL data?

All help appreciated. Thanks!

Reach


My macro below:

Sub CopyDeliveryData()
Dim ReportWbk As Workbook 'workbook with report data
Dim Report As String 'name of file with report data

' Opens file source
Application.DisplayAlerts = False
Application.FileDialog(msoFileDialogFilePicker).Show
Report = Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)
' Opens file source

' Copy and paste delivery data
Set ReportWbk = Workbooks.Open(Report)
ReportWbk.Sheets(1).Cells.Copy
ReportWbk.Close (False)
ThisWorkbook.Sheets("Delivery").Activate
Cells(1, 1).Select: ActiveSheet.Paste
' Copy and paste delivery data

[ajryan88]

Hi,

Before

ReportWbk.Sheets(1).Cells.Copy

insert the line

ReportWbk.Sheets(1).AutoFilterMode = False

Hope this helps

[arlu1201]

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE] before your code and [/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

[reach78]

Hi ajryan88, thanks that worked.

I now tried to automate another 2 worksheets from the source file where I wanted to merge the 2 worksheets into the open file

Sub CopyDeliveryData()
Dim ReportWbk As Workbook 'workbook with report data
Dim Report As String 'name of file with report data


' Opens file source
Application.DisplayAlerts = False
Application.FileDialog(msoFileDialogFilePicker).Show
Report = Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)
' Opens file source

' Copy and paste delivery data
Set ReportWbk = Workbooks.Open(Report)
ReportWbk.Sheets(1).AutoFilterMode = False
ReportWbk.Sheets(1).Cells.Copy
ReportWbk.Close (False)
ThisWorkbook.Sheets("Delivery").Activate
Cells(1, 1).Select: ActiveSheet.Paste
' Copy and paste delivery data

' Copy and paste NCD data
Set ReportWbk = Workbooks.Open(Report)
ReportWbk.Sheets(2).AutoFilterMode = False
ReportWbk.Sheets(2).Cells.Copy
ReportWbk.Close (False)
ThisWorkbook.Sheets("AMO NCD").Activate
Cells(1, 1).Select: ActiveSheet.Paste
Set ReportWbk = Workbooks.Open(Report)
ReportWbk.Sheets(3).AutoFilterMode = False
ReportWbk.Sheets(3).Cells.Copy
ReportWbk.Close (False)
ThisWorkbook.Sheets("AMO NCD").Activate
Selection.End(xlDown).Select ' finds the last row
ActiveSheet.Paste
' Copy and paste NCD data


End Sub

What I'm finding is that the header row of the 2nd worksheet is being copied as well (rightly so) and appended along with the data from the first worksheet. How do I delete the header row or not have the header row at all?

ps: Thanks arlu1201, I'll add them from now on.

Cheers,

Reach

[ajryan88]

No problem!

The simplest option here would be to delete the header row after the paste operation. So after

ActiveSheet.Paste

place this line of code:

ActiveSheet.Rows(1).Delete

Hope this helps

[reach78]

Thanks ajryan88. however that bit of code deleted the header row of the first table. Here's my illustrated example of what I'm trying to achieve.

macro_table_problem.jpg

[ajryan88]

Aha whoops, I get it now

Try changing

ThisWorkbook.Sheets("AMO NCD").Activate
Selection.End(xlDown).Select ' finds the last row
ActiveSheet.Paste

to

Dim lastRow As Long

ThisWorkbook.Sheets("AMO NCD").Activate
With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlup).Row + 1
    .Cells(lastRow, 1).Select
    .Paste
    .Rows(lastRow).Delete
End With

[reach78]

Hi ajryan88, that works a treat! But I found that for the 'NCD data' code block, the 'AutoFilterMode = False' line does not kick in. It seems to just copy the data that is filtered. That goes for sheets 2,3,4 in the code

' *******NCD data
Set ReportWbk = Workbooks.Open(Report)
ReportWbk.Sheets(2).AutoFilterMode = False
ReportWbk.Sheets(2).Cells.Copy
ReportWbk.Close (False)
ThisWorkbook.Sheets("AMO NCD").Activate
Cells(1, 1).Select: ActiveSheet.Paste
Set ReportWbk = Workbooks.Open(Report)
ReportWbk.Sheets(3).AutoFilterMode = False
ReportWbk.Sheets(3).Cells.Copy
ReportWbk.Close (False)
ThisWorkbook.Sheets("AMO NCD").Activate
With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    .Cells(lastRow, 1).Select
    .Paste
    .Rows(lastRow).Delete
End With
' Set ReportWbk = Workbooks.Open(Report)
' ReportWbk.Sheets(4).AutoFilterMode = False
' ReportWbk.Sheets(4).Cells.Copy
' ReportWbk.Close (False)
' ThisWorkbook.Sheets("AMO NCD").Activate
' With ActiveSheet
'     lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
'     .Cells(lastRow, 1).Select
'     .Paste
'     .Rows(lastRow).Delete
' End With
' ******* NCD data

End Sub

You've been helping so much. I feel bad for asking more. Good thing is I'm learning but still have not gotten the hang of the syntax and semantics of this language yet...:D

[ajryan88]

Hi again,

That seems a bit strange that the AutoFilter isn't turned off for sheets 2, 3, and 4 but it works fine for sheet 1.

Would it be at all possible for you to upload your workbooks (both the report workbook and the workbook that you are pasting the data into) so that I can take a look at why this is occurring?

And there's no need to feel bad about it, that's what the forum is here for!

Thanks

[reach78]

Hi ajryan88, thanks for that. I've uploaded the files into google docs.

macro file: https://docs.google.com/file/d/0BxMi...it?usp=sharing
sample table data: https://docs.google.com/file/d/0BxMi...it?usp=sharing

[ajryan88]

Hi,

I have commented out some of your code and made a few slight changes, but the major change is tagged (with "AJRYAN88") at the beginning of the macro. I have done 3 things:
1. The filters were table filters, not worksheet filters, so slightly different code was required to disable them
2. Rather than opening and closing the workbook to copy each sheet, the workbook is opened at the beginning, all filters are removed, and then workbook is left open until the completion of the macro
3. I have changed the copy mode of sheet 3 from copying the entire sheet to copying only the used range. This is because an error occurs if trying to copy the contents of an entire worksheet onto a cell that isn't A1.

Hope this is working for you now

[reach78]

Hi ajryan88, it's working!

ta mate. Much appreciated.

[ajryan88]

No problems! Glad I could help

Please don't forget to mark this thread as solved and please click on the * next to my post(s) to say thanks

[reach78]

Reputation added, thanks :D

[reach78]

Hi All,

I think I've gone and killed the code again.

This is for the 'NCD Data' part of the code, what it has gone and done is that it is now not only deleting the header row (which I want deleted) but also the first row of data of the second table I want to append to the first.

Sub CopyDeliveryData()
Dim ReportWbk As Workbook 'workbook with report data
Dim Report As String 'name of file with report data
Dim lastRow As Long 'last row of first table
Dim WS As Worksheet
Dim listObj As ListObject

'Opens source file
Application.DisplayAlerts = False
Application.FileDialog(msoFileDialogFilePicker).Show
Report = Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)
'/Opens source file


'Source file setup
Set ReportWbk = Workbooks.Open(Report)
For Each WS In ReportWbk.Worksheets
    For Each listObj In WS.ListObjects
        listObj.AutoFilter.ShowAllData
    Next listObj
Next WS
'/Source file setup

'***Delivery data***
ReportWbk.Sheets(1).Cells.Copy
ThisWorkbook.Sheets("Delivery").Activate
Cells(1, 1).Select: ActiveSheet.Paste
' ***Delivery data***

' ***NCD data***

ReportWbk.Sheets(2).Cells.Copy
ThisWorkbook.Sheets("AMO NCD").Activate
Cells(1, 1).Select: ActiveSheet.Paste
With ReportWbk.Sheets(3)
    .Range(.Cells(2, "A"), .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).Copy
End With


'Deleting the header row for sheet 3
ThisWorkbook.Sheets("AMO NCD").Activate
With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    .Cells(lastRow, 1).Select
    .Paste
    .Rows(lastRow).Select
    .Rows(lastRow).Delete
End With

Also just for my understanding:

With ReportWbk.Sheets(3)
    .Range(.Cells(2, "A"), .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).Copy
End With

The code above copies only the 'used' rows and columns of a sheet starting from cell A2 right? Does this mean that for sheets(3) in the original code at the top, it does not copy the header row during the copy process thus eliminating the range for additional coding?

AND

'Deleting the header row for sheet 3
ThisWorkbook.Sheets("AMO NCD").Activate
With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    .Cells(lastRow, 1).Select
    .Paste
    .Rows(lastRow).Select
    .Rows(lastRow).Delete
End With
'/Deleting the header row for sheet 3

The code above, for the current activesheet defines the lastrow and adds 1 row to it (row below the last used row of the first table), then pastes the second table on that row. It then deletes that row (essentially removing the header).

If my logic serves me right, this double elimination has deleted more rows than needed or is my understanding flawed?

I tried changing

.Range(.Cells(2, "A"), .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).Copy

With

 .Range(.Cells(1, "A"), .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).Copy

But it gave me a range class error.

[ajryan88]

Hi,

Could you please re-upload your workbook with the newest changes in it.

Thanks

[reach78]

Managed to solve it by deleting the last 2 rows of code within the last With...End With block.

Have found another bug. Will raise another query. :D