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

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,784
Office Version
  1. 2019
Platform
  1. Windows
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

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





 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Well I think missing leading period
VBA Code:
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

Well-known Member
Joined
Jun 26, 2006
Messages
5,784
Office Version
  1. 2019
Platform
  1. Windows
Thanks for your help, but unfortunately does not resolve the issue

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



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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,850
Office Version
  1. 365
Platform
  1. Windows
Firstly you need to paste formats before you paste values.
Secondly, do you always have data in every cell in col A?
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,784
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,850
Office Version
  1. 365
Platform
  1. Windows
Your code is using col A to find the last row of data, which maybe the problem.
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,784
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,850
Office Version
  1. 365
Platform
  1. Windows
Will there ever be data that needs to be copied below the last value in col A?
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,784
Office Version
  1. 2019
Platform
  1. Windows
Hi Fluff

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,850
Office Version
  1. 365
Platform
  1. Windows
In that case I can see no reason why you would have problems.
That said I would use
VBA Code:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,173
Members
417,129
Latest member
geekzilla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top