Copy/Paste Values multiple outputs with different sizes into one single Excel sheet using VBA

[daisylee]

Hi everyone,

I recently started learning VBA on my own. I would like to use VBA to automatically copy different outputs from one Excel file to another one. In the original data file, I have 10 outputs (let's say, A-J) with different sizes/ranges. I want to copy them into one single sheet in a destination file. In the destination file, each output will be pasted to the worksheet called "Final Data", according to the range that I assign.
'copy output A from original data
Workbooks("Original Data").Sheets("A").Activate
lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious).Row
lastcol = Cells.Find(What:="*", SearchDirection:=xlPrevious).Column

ActiveSheet.Range(Cells(2,1),Cells(lastrow,lastcol)).Copy

'paste output A to sheet "Final Data" in destination file
Workbooks("Destination Data").Sheets("Final Data").Activate

ActiveSheet.Range("B5").PasteSpecial xlPasteValues

I figured out the syntax for copy/paste 1 single output at a time. To improve efficiency, I want to use the loop to reference each of the 10 outputs, then paste them to their assigned range. Is there a way to dynamic change Sheets("A") to "B","C", etc., and also change Range("B5") to "B18" (for destination of output B), "B121" (for destination of output C), etc.? Thank you!

 

[DanteAmor]

Hi @daisylee Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Adjust the names of your sheets, your books, and the destination cells.
Try this:

Sub Copy_Values()
  Dim lr As Long, lc As Long, i As Long
  Dim ary As Variant
  Dim wb1 As Workbook
  Dim sh As Worksheet
  
  Set wb1 = Workbooks("Original Data")          'Original book name
  'All the names of your sheets and their respective cell
  ary = Array("A", "B5", "B", "B18", "C", "B121", _
              "D", "B200", "E", "B220", "F", "B225", _
              "G", "B230", "H", "B235", "I", "B240", _
              "J", "B245")
              
  For i = 0 To UBound(ary) Step 2
    Set sh = wb1.Sheets(ary(i))
    lr = sh.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    lc = sh.Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column
    sh.Range("A2", sh.Cells(lr, lc)).Copy
    Workbooks("Destination Data").Sheets("Final Data").Range(ary(i + 1)).PasteSpecial xlPasteValues
  Next
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[daisylee]

I tried this logic, and it worked. Thanks so much!