CODEWITHSUNDEEP
vbaexcel
M.CHA
M.CHA

Reputation: 11

Excel VBA Paste Error "...isn't the same size..."

NB: I'm brand new to VBA and code in general

I've attempted to make a macro that collates data from multiple workbooks and imports it into a master workbook ('ZMaster').

The following code successfully copies data from the cell C5 in multiple workbooks (from file C:\AutoMelinh) and pastes them in a column in my 'ZMaster' workbook.

The issue is I get the error 'The data you are pasting isn't the same size as your selection. Do you want to paste anyway?'. This comes after every paste, so I have to click 'ok' every single time. The format of the cell that is copied, is merged (between C5 and D5). I think this is the issue however I don't know how to mitigate that in the VBA code:

Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
Dim Filepath As String
Filepath = "C:\AutoMelinh\"
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0
    If MyFile = "ZMaster.xlsm" Then
    Exit Sub
    End If

    Workbooks.Open (Filepath & MyFile)
    Range("C5").Copy
    ActiveWorkbook.Close

    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 4))
    MyFile = Dir

Loop
End Sub

EDIT: I was able to solve the issue by using

Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
Dim Filepath As String
Filepath = "C:\AutoMelinh\"
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0
    If MyFile = "ZMaster.xlsm" Then
    Exit Sub
    End If

    Application.DisplayAlerts = False

    Workbooks.Open (Filepath & MyFile)
    Range("C5").Copy
    ActiveWorkbook.Close

    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 4))
    MyFile = Dir

    Application.DisplayAlerts = True

Loop
End Sub

Upvotes: 1

Views: 3212

Answers (1)

jsotola
jsotola

Reputation: 2278

you are getting the warning because you are pasting one cell into 4 cells

this should work without using copy/paste

Sub LoopThroughDirectory()

    Dim Filepath As String
    Filepath = "C:\AutoMelinh\"

    Dim MyFile As String
    MyFile = Dir(Filepath)

    Dim erow As Range
    Dim wb As Workbook

    Do While Len(MyFile) > 0
        If MyFile = "ZMaster.xlsm" Then Exit Sub

        Set wb = Workbooks.Open(Filepath & MyFile)

        erow = Workbooks("ZMaster.xlsm").Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

        erow.Value = wb.Sheets(1).Range("C5").Value

        if isempty(erow) then erow.value = "----------"

        wb.Close
        MyFile = Dir

    Loop

End Sub

Upvotes: 1

Related Questions