Copy/SpecialPaste Range from multiple closed workbooks to master workbook

Question

I am trying to copy Range(A14:N26) from every closed workbook in a folder on my desktop and paste them into the current worksheet (which is my master worksheet). The code does grab the right range of data but struggles with the pasting part. It is supposed to SpecialPaste the code as there are formulas in the cells and I want to only copy what is visible in the cells. (Note: The outcome of some of of the formulas are words, the outcome of others are numbers)

Option Explicit

Sub CopySheetFromFileOnDesktop()

Dim wkbDest As Workbook
Dim wksDest As Worksheet
Dim wkbSource As Workbook
Dim wksSource As Worksheet
Dim MyPath As String
Dim MyFile As String
Dim SheetIndex As Integer



Application.ScreenUpdating = False

Set wkbDest = ThisWorkbook
Set wksDest = wkbDest.Worksheets("Master Sheet")

SheetIndex = 1

MyPath = "C:\Users\.."

If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

MyFile = Dir(MyPath & "*.xlsm")


Do While Len(MyFile) > 0
    Set wkbSource = Workbooks.Open(MyPath & MyFile)
    Set wksSource = wkbSource.Worksheets("Sheet containing the info")
    If WorksheetFunction.CountA(wkbSource.Sheets("Sheet containing the 
info").Range("A14:L26")) <> 0 Then
   'lRow = .Range("L" & Rows.Count).End(xlUp).Row 'UNSURE HOW TO LAST ROW
    wkbSource.Sheets("Sheet containing the info").Range("A14:L26").Copy
    wkbDest.Range("A:L" & Rows.Count).End(xlUp)(2).PasteSpecial _ 
    Paste:=xlPasteValues 'PASTESPECIAL SEEMS TO BE THE PROBLEM

    wkbSource.Close savechanges:=False

    Application.CutCopyMode = False

    Else
    End If
Loop

Application.ScreenUpdating = True

MsgBox "Completed...", vbInformation


End Sub

when running the macro it shows this bug: Runtime Error 438: Object does not support Properties or Method. And the debugger highlights the line where I define where to paste the copied range

Possible duplicate https://stackoverflow.com/q/54706555/4961700 — Solar Mike, Feb 15 '19 at 14:51
@SolarMike thanks but that problem hasn't been solved either — Roxana, Feb 15 '19 at 16:16
You may find some clues here : https://stackoverflow.com/q/50776026/4961700 — Solar Mike, Feb 15 '19 at 16:26
Like @Asger says below, the `wkbDest.Range("A:L" & Rows...` is probably where you are hitting the problem. You may fix it by setting a previous last row or resizing. `lRow = wkbDest.Range("A" & Rows.Count).End(xlUp).row` `wkbDest.Range("A"& lRow).Resize(12, 12).PasteSpecial Paste:=xlPasteValues` — jessi, Feb 15 '19 at 21:54

Asger · Answer 1 · 2019-02-15T18:30:54.113

The code row with your destination range needs an optimization:

You erroneously used wkbDest instead of wksDest
A partly row can not be addressed by Range("A:L" & 1000)
If you use Rows.Count without a leading dot, then the ActiveSheet is assumed

First attempt

wksDest.Cells(wksDest.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 12).PasteSpecial _
   Paste:=xlPasteValues

The destination is built as follows:

Find the last used cell in column 1 (e. g. A100)
Offset it to the next row (e. g. A101)
Resize it to a new dimension of 1 row and 12 columns (e. g. A101:L101)

Second attempt:

If you paste, it is only necessary to address the first cell of the destination. So following should also work:

wksDest.Cells(wksDest.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial _
   Paste:=xlPasteValues

Recommendation:

If you define source and destination range of the same size, you can just assign their values (simular to PastSpecial of values, but faster):

wksDest.Cells(wksDest.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 12).Value = _
    wksSource.Range("A14:L26").Value

Copy/SpecialPaste Range from multiple closed workbooks to master workbook

1 Answers1