copy/paste from one workbook to another

[DCSooner27]

Hello, I am fairly new to using VBA code and Excel Macros, and I was wondering if anyone has any recommendations on the following problem:

I compile an Excel workbook (Workbook 1) with data that is emailed to me from several Excel workbooks (Workbook A, Workbook B, etc.). As of now, I manually copy and paste values this data from each workbook that is emailed to me into my workbook.

1) I would like to create macros that will enable me to automatically copy a select range of data from each workbook (Workbook A, Workbook B, etc) that is emailed to me and paste values the data into specific ranges within specific worksheets in my workbook (Workbook 1) that I use to compile all of this information.

If anyone has any suggestions for the above, that will make my life much simpler. Furthermore, if possible I would like to expand on the ability to copy/paste from one workbook to another for the following:

2) I would like to copy several ranges of data from several worksheets in a workbook that is emailed to me and past values the different ranges of data into different worksheets in my workbook all in one macro.

Any help or advice is much appreciated.

[djbomaha]

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

[DCSooner27]

Thanks for the code and suggestion Daniel. I'll give it a try. I was, however, able to do what I was trying to accomplish by doing a macro recording.