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
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