Opening all files in a folder and pasting new dat from source workbook

[AndyXL]

I have a monthly process wher I have to update data in around 20 spreadsheets. I have found and successfully used code to open all the files but cant seem to get the copy and paste part to work.
Here is my code so far.

Sub UpdateAllSandbox()
Dim FileName, folderpath, FileArray(), period As String
Dim Count1, i As Integer
Dim SourceWB, DestWB As Workbook
period = "P" & Range("N6").Value & "-2020"

folderpath = "C:\Users\ahall\NDC Technologies\SEC-Finance - Documents\2020\Commissions\CalculatorsTEST\" & period & "\"

If Right(folderpath, 1) <> "\" Then
folderpath = folderpath & "\"
End If

FileName = Dir(folderpath & "*.xlsm")

Count1 = 0

While FileName <> ""
Count1 = Count1 + 1
ReDim Preserve FileArray(1 To Count1)
FileArray(Count1) = FileName
FileName = Dir()
Wend

Set SourceWB = ThisWorkbook

For i = 1 To UBound(FileArray)

Set DestWB = Workbooks.Open(folderpath & FileArray(i))

' this is the bit I cant get to work

' DestWB.Worksheets("YTD Orders").Visible = xlSheetVisible
' SourceWB.Worksheets("OrderData").Range("A:R").Copy
' DestWB.Worksheets("YTD Orders").Range("A:R").Paste
'DestWB.Worksheets("Lists").Visible = xlSheetHidden
'DestWB.Close True


Next

Set DestWB = Nothing
Set SourceWB = Nothing

End Sub




Any help as always gratefully received

 

[JLGWhiz]

Replace this:

SourceWB.Worksheets("OrderData").Range("A:R").Copy
 DestWB.Worksheets("YTD Orders").Range("A:R").Paste

With this"

 SourceWB.Worksheets("OrderData").Range("A:R").Copy DestWB.Worksheets("YTD Orders").Range("A1")

 

[AndyXL]

Thank you that worked a treat, I did run into one issue though, if there is a filter applied to destination worksheet the data does not paste correctly, is there a command to clear any filters if they exist.?

Thanks again for your help.

 

[JLGWhiz]

Thank you that worked a treat, I did run into one issue though, if there is a filter applied to destination worksheet the data does not paste correctly, is there a command to clear any filters if they exist.?

Thanks again for your help.

You can try this. Put it before the copy statement.

DestWB.Worksheets("YTD Orders").AutoFilterMode = False

 

[AndyXL]

Thanks, now works perfectly

 

[JLGWhiz]

Thanks, now works perfectly

You're welcome,
regards, JLG