Macro to open multiple files and copy data from two seperate sheets and paste these on to to sheets

[howard]

I have a macro to open multiple files and copy these from 2 specific sheets and paste these one after another on the same sheet names as the source data

When running the macro only the data from the last workbook selected is copied and not from the other workbooks selected


it would be appreciated if someone can kindly amend my code

 Sub Open_MultipleFiles()
ChDir "C:\downloads\"
Dim LR As Long
Application.DisplayAlerts = False
With Sheets("Sales Data")
LR = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:C" & LR).ClearContents

End With

With Sheets("report Excluding Zero Values")
LR = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:C" & LR).ClearContents

End With




Dim fDialog As Object, varFile As Variant
Dim nb As Workbook, tw As Workbook, ts As Worksheet
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .CutCopyMode = False
End With
Set tw = ThisWorkbook
Set ts = tw.ActiveSheet
Set fDialog = Application.FileDialog(3)
ChDir "C:\downloads"
With fDialog
  .Filters.Clear
  .Filters.Add "Excel files", "*.xlsm*"
   .Show
   
   For Each varFile In .SelectedItems
      Set nb = Workbooks.Open(Filename:=varFile, local:=True)
     
     With Sheets("Sales Data")
   .Range("A1:C1000").Copy
    ThisWorkbook.Sheets("Sales Data").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    ThisWorkbook.Sheets("Sales Data").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormats

End With
     
      With Sheets("report Excluding Zero Values")
   .Range("A1:C1000").Copy
    ThisWorkbook.Sheets("report Excluding Zero Values").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    ThisWorkbook.Sheets("report Excluding Zero Values").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormats

End With
     
     
     
        nb.Close False
   Next
End With
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .CutCopyMode = True
End With

 Application.DisplayAlerts = True



End Sub

 

[mohadin]

Well I think missing leading period

With Sheets("report Excluding Zero Values")
                .Range("A1:C1000").Copy
                ThisWorkbook.Sheets("report Excluding Zero Values").Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
                ThisWorkbook.Sheets("report Excluding Zero Values").Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormats

            End With

And same foe the other sheet

 

[howard]

Thanks for your help, but unfortunately does not resolve the issue

See link below which shows sample source file with the two sheets

Dropbox - File Deleted - Simplify your life

www.dropbox.com

It would be appreciated if you could test and amend my code

 

[Fluff]

Firstly you need to paste formats before you paste values.
Secondly, do you always have data in every cell in col A?

 

[howard]

Thanks for your input Fluff

I don't have data in every cell in Col A, but wherever there is a value in Col A, Col B & C will have formulas even if these return a blank result

 

[Fluff]

Your code is using col A to find the last row of data, which maybe the problem.

 

[howard]

Where there are no values in Col A , B or C , it returns a blank cell

this may be causing only the last files data to be copied

How can I overcome this using my code ?

 

[Fluff]

Will there ever be data that needs to be copied below the last value in col A?

 

[howard]

Hi Fluff

The data will never need to be copied below the last value in Col A

 

[Fluff]

In that case I can see no reason why you would have problems.
That said I would use

nb.Sheets("Sales Data")

just to ensure it's pulling from the opened workbook.
If you step through the code, do all files get opened?