copy/paste from one workbook to another

Hi DCSooner27,

Here is an example of some code that takes specific ranges from a file called "Workbook A" and "Workbook B" and pastes them into a master workbook. Hopefully this helps.

Option Explicit

Sub MainTransfer()

'
' Routine to sum scores across workbooks in a directory
'
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
'
' Define Variables
'
Dim MyPath As String
Dim MyFile As String
Dim wkb As Workbook
Dim i As Integer
Dim ScoreSum As Single
Dim CurrentBook As Workbook

' Set CurrentBook
Set CurrentBook = ActiveWorkbook
'
' Set Directory and file extension
'
MyPath = "c:\home\emails\"  ' <-- Set to folder that contains excel files with scores

MyFile = Dir(MyPath & "*.xls")  ' <--  Set to extension of excel files

' Run specific code to copy from workbook and paste in main workbook

Do While Len(MyFile) > 0
   Set wkb = Workbooks.Open(MyPath & MyFile)
   With wkb
   
   If (wkb.Name = "Workbook A.xls") Then
      Sheets(1).Select
      Range("A1:D4").Select
      Selection.Copy
      CurrentBook.Activate
      Sheets(1).Select
      Range("C2").Select
      ActiveSheet.Paste
      Application.CutCopyMode = False
   ElseIf (wkb.Name = "Workbook B.xls") Then
      Sheets(2).Select
      Range("A1:D4").Select
      Selection.Copy
      CurrentBook.Activate
      Sheets(2).Select
      Range("D2").Select
      ActiveSheet.Paste
      Application.CutCopyMode = False
   End If
      
' Close workbook
   
   End With
   wkb.Close SaveChanges:=False
   MyFile = Dir
Loop

' Reset sheet

Sheets(1).Select
Range("A1").Select

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

'
MsgBox "Process Successfully Completed."

End Sub

Thanks,

Daniel