Excel Macro Copy and Paste Between WorkBooks

Question

So I've run in to a brick wall regarding copy and pasting from one Workbook to another using Macros

I have about 800 Work Books that I need to copy certain cells from and paste in to a separate "tracker" work book. Macro's are going to be the easiest way to do this.

The problem I'm running into is how do I tell the macro that the COPYFROM.XLSX workbook will be changing, and when it's being pasted it needs to the paste to the next line so as not to overwrite information.

Any help you guys have will be super useful, thanks.

Windows("COPYFROM.xlsx").Activate
Range("E39:F39").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("B8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("F13").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("C8").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("C13").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("D8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("C15").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("E8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("F17").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("F8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("C17:C18").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("G8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("C27").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("H8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("F21").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("J8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("C21").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("K8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("C23").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("N8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("F25").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("O8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("C37").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("Q8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("F59").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("S8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("F61").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("T8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("F19").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("U8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("C31").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("V8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("F49").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("W8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("F31").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("X8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("F37").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("Y8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("F15").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("AA8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("C37").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("AE8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


Windows("COPYFROM.xlsx").Activate
Range("F45").Select
Selection.Copy
Windows("Paste.XLSX").Activate
Range("AF8").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False



End Sub

Are you moving across columns as you paste? it would be worth you reading [How to avoid Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?s=1|266.9941). — SJR, Nov 29 '18 at 10:35
Have a look at how I avoided select in this code : https://stackoverflow.com/a/40768023/4961700 — Solar Mike, Nov 29 '18 at 10:40
I'm not sure how to about avoiding select for such specific Cells. — Dan Howles, Nov 29 '18 at 10:42

Lambik · Accepted Answer · 2018-11-29T11:05:55.327

Like this:

List the files you need to copy, either manually or using (another) macro. For instance, like this Get list of Excel files in a folder using VBA
Using this list, set a range to run through

Copy-paste the data onto the next free row

Sub test()
Dim LastColumn As Long, LastRow As Long, LR As Long, n As Long
Dim Thiswb As Workbook, Openwb As Workbook
Dim Source As Worksheet, wsTO As Worksheet, wsM As Worksheet
Dim FileRange As Range
Dim sSource As String, FileName As String
Dim cell As Variant, FilePath As Variant
Set Thiswb = ThisWorkbook
' Here you put the list of the files you want to copy from
Set Source = Thiswb.Worksheets("Source")
' Here you will paste your data
Set wsTO = Thiswb.Worksheets("HereComesYourData")
' Find the last row of column A. The list of files to look for is in this column
LastRow = Source.Cells(Rows.Count, 1).End(xlUp).Row
'Set the range in which to look
Set FileRange = Source.Range(Source.Cells(2, 1), Source.Cells(LastRow, 1))
n = 2
On Error Resume Next
For Each cell In FileRange    'Run through the whole range
    'Error handling when file or worksheet isn't found
    FilePath = Source.Cells(n, 2).Value
    FileName = Source.Cells(n, 1).Value
    Workbooks.Open (FilePath)
    Set Openwb = Workbooks(FileName)
    'Depending on what you want to copy - declare the correct variable
    Set wsM = Openwb.Worksheets("Master")
    'Calculate last column number of source
    LastColumn = wsM.Cells(1, Columns.Count).End(xlToLeft).Column
    'Calculate last row number of source
    LastRow = wsM.Cells(Rows.Count, 1).End(xlUp).Row
    'Calculate last row number of destination
    LR = wsTO.Cells(Rows.Count, 1).End(xlUp).Row
    'Paste values
    wsTO.Range(wsTO.Cells(LR, 1), wsTO.Cells(LR + LastRow, LastColumn)).Value = wsM.Range(wsM.Cells(2, 1), wsM.Cells(LastRow, LastColumn)).Value
    Openwb.Close SaveChanges:=False
Next cell
End sub

This has blown my mind a little bit. Any chance you could explain this a little bit as I don't know what I am looking at....it's a caffeineless morning so far. — Dan Howles, Nov 29 '18 at 10:57
First you list all files you want data copied from in a sheet. Then you open them, one by one and you copy their data on another sheet (which I've called wsTO). I've added a bit of comments in the code — Lambik, Nov 29 '18 at 11:02
I appreciate this the help. But the more I stare at this, the more I'm not understanding. I don't have the strongest knowledge base of VBA. — Dan Howles, Nov 29 '18 at 11:12

score 0 · Answer 2 · answered Nov 29 '18 at 10:45

0

Something along these lines. Presuming you are moving along row 8. You should use sheet names rather than the indexes below, and use more meaningful procedue/variable names.

Sub x()

Dim c As Long

Windows("COPYFROM.xlsx").Sheets(1).Range("E39:F39").Copy
With Windows("Paste.XLSX").Sheets(1)
    c = .Cells(8, Columns.Count).End(xlToLeft).Column + 1
    .Cells(8, c).PasteSpecial Paste:=xlPasteValues
End With

'etc

End Sub

answered Nov 29 '18 at 10:45

SJR

22,986
6
18
26

I'll have a play with something like this now, thank you. Would there be away to update the COPYFROM without having to recode the macro everytime? – Dan Howles Nov 29 '18 at 10:47
There doesn't seem to be a pattern to the cells you're copying from, but you could put the addresses in an array and loop through that. – SJR Nov 29 '18 at 11:03

Excel Macro Copy and Paste Between WorkBooks

2 Answers2