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

Yellow Snow

New Member
Joined
Jul 16, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,223
Messages
5,768,903
Members
425,501
Latest member
sunderlalrwr

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