CODEWITHSUNDEEP
excelvba
Louis
Louis

Reputation: 19

VBA Selection.Copy copies a completely different range when running my code

I have spent the day trying to understand what is going on with my excel.. I am running some code which worked fine before, I modified part of it early up (but which still works fine), and now a Selection.Copy later on in the code has stopped working. Here it is :

 Range("AE3").Select
 Range(Selection, Selection.End(xlDown)).Select
 Selection.Copy

I used break points to find the problem. After the first two lines of code, it is the data in column AE which is selected. When I move on to the last line (Selection.Copy), it is not the data in AE which is selected but the columns AA and AB. I have tried literally everything I can think of to try and fix this but can't find anything..

If I run the code up to this point of the code and do the selection and copying manually, it also copies the wrong cells (it copies AA and AB like when it's done with vba)

I would post screenshots of it but you can't put photos here it seems.

Thanks for your help!

Resolved:

I went through the code step by step and noticed that previously in the code I copied the data from columns AA and AB to lower columns. To do so I had selected the columns and then copied them. I changed that so that I selected only the data in the columns and not the columns themselves and copied the data. This change has made my code work. I'm not sure why this was effecting the later Selection.Copy, but it was in some way. Thank you everyone for their help!

Upvotes: 0

Views: 2654

Answers (3)

FAB
FAB

Reputation: 2569

As I've suggested in my comment, avoid using .Select & Selection, is usually bad practice and almost everything can be done in VBA without the need to use them. I understand those are a result of the recorder (which is a good place to start learning how to do certain things in VBA), just need to learn as well how to use the code generated by the recorder.

See if this helps (see comments in code as well):

Sub copyRange()

Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Sheet1") 'use a variable for the sheet you want to use

Dim lRow As Long
lRow = ws.Cells(Rows.Count, "AE").End(xlUp).Row 'get the last row at the desired column

With ws
    .Range("AE3:AE" & lRow).Copy _
        Destination:=.Range("AE3:AE" & lRow).Offset(0, -10) 'destination offset 10 columns to the left
        'or alternatively specify the destination
        'Destination:=.Range("U3:U" & lRow)
End With

'ALTERNATIVE to the above - copy values only
With ws.Range("AE3:AE" & lRow)
    .Offset(0, -10).Value = .Value 'destination offset 10 columns to the left
        'or alternatively specify the destination
    'ws.Range("U3:U" & lRow).Value = .Value
End With

'2nd ALTERNATIVE to the above - copy values only
With ws.Range(ws.Cells(3, 31), ws.Cells(lRow, 31))
    .Offset(0, -10).Value = .Value 'destination offset 10 columns to the left
        'or alternatively specify the destination
    ws.Range(ws.Cells(3, 21), ws.Cells(lRow, 21)).Value = .Value
End With

End Sub

Note the use of With statement, .Range(...) is not the same as Range(...).

Upvotes: 1

Error 1004
Error 1004

Reputation: 8220

You could try:

Option Explicit

Sub test()

    Dim LastRow As Long
    'Create a with statement refer to the sheet where your data are
    With ThisWorkbook.Worksheets("Sheet1")
        'Find the LastRow of column AE
         LastRow = .Cells(.Rows.Count, "AE").End(xlUp).Row
         'Refer to the range starting from AE3 and ends at Lastrow
         .Range("AE3" & ":AE" & LastRow).Copy
    End With

End Sub

Results: enter image description here

Upvotes: 0

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

In case you want to copy all in column AE try this:

Range("EA3:EA" & Range("EA" & Rows.Count).End(xlUp).Row)).Copy

And to paste you could use:

Range("U3").PasteSpecial (xlPasteValues)

Also, I strongly suggest you to read:

How to avoid using Select in Excel VBA

Upvotes: 0

Related Questions