VBA copy from multiple tabs of one workbook to multiple tabs of another

[Yellow Snow]

Hello Everyone,

Just wondering how I could modify the below script to work with my questions

1. Specify a file path location in the below code from a specific cell from a "master sheet" tab/completely separate tab
A. I have the file path location in a tab that has the complete file path directory including file type (.xlsm) from a tab named "Mail Balances"

2. Copy data formats from the source and retain the format in the destination paste location.

3. I do not want to use an array, but want to have an entire selection of data "A1"G200" copied over into the master file tab "SH" cells "B2:H200"


Public Sub Copy_values()

'declare varibales
Dim fileCells As Range, fileCell As Range
Dim destCells As Range, r As Long
Dim fromWorkbook As Workbook

With ThisWorkbook

'location of filenames to open
With .Worksheets("Sheet1")
'sets the number of times/files to do the prodecure based on the last low of data
Set fileCells = .Range(.Range("A1"), .Cells(.Rows.Count, "A").End(xlUp))
End With
'paste values from files into worksheet
Set destCells = .Worksheets(Array("FP Plan Extract", "OT Plan Extract")).Range("B8:EZ20")


End With
'disable screen updating
Application.ScreenUpdating = False
r = 0
'for each file that is listed do the procedure
For Each fileCell In fileCells
'open workbooks as read only, don't update links
Set fromWorkbook = Workbooks.Open(fileCell.Value, ReadOnly:=True, UpdateLinks:=0)
'copy values from open file

destCells.Offset(r).Value = fromWorkbook.Worksheets(Array("FP Plan Data", "OT Plan Data")).Range("A7:EY19").Value

'close open file without saving
fromWorkbook.Close SaveChanges:=False
'next file's 13 row of data
r = r + 13
DoEvents
Next

'enable screen updating
Application.ScreenUpdating = True
'prompt when finished
MsgBox "Finished"

End Sub