CODEWITHSUNDEEP
excelvba
rustingvba
rustingvba

Reputation: 3

copy and pasting area not the same size?

Dim lastrow&, lastCol&, myarray As Range
lastrow = Range("A1").End(xlDown).Row
lastCol = Range("XX1").End(xlToLeft).Column
Set myarray = Range("A1").Resize(lastrow, lastCol)
Range("A1", myarray).Select

So i added the above code to recognise the last column and last row and copy the array

 Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Application.WindowState = xlNormal
    Windows("Ex-Pakistan Calculator Final.xlsm").Activate
    Sheets("MRG").Select
    'has to find the last row by itself
    Range("A" & Rows.Count).End(xlUp).Offset(2, 0).Select
    ActiveSheet.Paste

Getting an error on the last line "activesheet.paste" saying copy and pasting area isn't the same size, try selecting one cell. enter image description here

Thing is, "Range("A" & Rows.Count).End(xlUp).Offset(2, 0).Select" does only select one cell, so I don't see the issue.

Upvotes: 0

Views: 874

Answers (2)

pgSystemTester
pgSystemTester

Reputation: 9917

Congrats on starting to use VBA. There's several things in your code that could use improvement. You want to avoid using select (a common beginner task). You also don't even need to move around your sheet, or even use copy/paste.

However, see below where I've broken up your code with some statements to stop and check where you're at. I think this will accomplish what you want, but also help you gain a better grasp of what you're doing (it's always a battle getting started!)

Keep battling.

Sub adfa()
Const turnOnStops As Boolean = True 'change to true or false to review code

    Dim WS_Pull As Worksheet:
        Set WS_Pull = ActiveSheet 'better to define this with actual sheet name

    Dim lastrow As Long:
        lastrow = WS_Pull.Cells(Rows.Count, 1).End(xlUp).Row 'this assumes column a has the bottom row and no rows hidden
    If turnOnStops Then
        Debug.Print "Lastrow is " & lastrow
        Stop
    End If



    Dim lastcol As Long:
        lastcol = WS_Pull.Cells(1, Columns.Count).End(xlToLeft).Column 'same assumptions but with columns on row 1 instead of columna a
    If turnOnStops Then
        Debug.Print "lastcol is " & lastcol
        Stop
    End If




    Dim myarray As Range:
        Set myarray = WS_Pull.Range("A1").Resize(lastrow, lastcol) ' I'm not sure what you're trying to do here.


    If turnOnStops Then
        Dim theAnswer As Long
            theAnswer = MsgBox("The address of myArray is " & myarray.Address & ". Stop code?", vbYesNo)
            If theAnswer = vbYes Then Stop
    End If


    Dim WS_paste As Worksheet: Set WS_paste = Sheets("MRG")  'it would be better to use the SHEET (shown in the VBA project)


    WS_Pull.Range("A1", myarray).Copy '<--- what are trying to copy.

    If turnOnStops Then
            theAnswer = MsgBox("The area copied was " & WS_Pull.Range("A1", myarray).Address & ". Stop code?", vbYesNo)
            If theAnswer = vbYes Then Stop
    End If


    If turnOnStops Then
        theAnswer = MsgBox("The area you are going to paste to is " & _
        WS_paste.Cells(1, Rows.Count).End(xlUp).Offset(2, 0).Address & " stop code?", vbYesNo)
        If theAnswer = vbYes Then Stop
    End If


End Sub

Upvotes: 0

Naveen Kumar
Naveen Kumar

Reputation: 2006

Following is an ideal way to copy and paste using range selection. You can change this code as per your requirement.

Sub CopyPaste()
    Dim selectRange As range
    Dim lastrow As Integer
    Application.CutCopyMode = False
    Sheets("Sheet1").Activate
    lastrow = range("A1").End(xlDown).Row
    Set selectRange = range("A1:A" & lastrow)
    selectRange.Copy
    Sheets("Sheet2").range("B1:B" & lastrow).PasteSpecial xlPasteAll
End Sub

Upvotes: 0

Related Questions