0

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!

Louis
  • 19
  • 1
  • 6
  • You should have a read on how to avoid using `.Select` & `Selection`, that might resolve your issue as well. Where are you trying to copy this range to? – FAB Jun 24 '19 at 08:52
  • What @FAB is referring to: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Tim Stack Jun 24 '19 at 08:56
  • I tried it without the .Select & Selection but it doesn't work either. I'm very new to vba and have only just seen that I should avoid doing that (not something you can just imagine is bad..). If it helps, 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) – Louis Jun 24 '19 at 08:56
  • Oh and trying to copy it to column U – Louis Jun 24 '19 at 08:57

3 Answers3

1

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(...).

FAB
  • 2,505
  • 1
  • 10
  • 21
  • Thanks for your help, but I have the same problem when using this (I can't break the code to see what it is copying exactly but I have the same error message indicating that the size of the copy range isn't equal to that of the destination, so I guess it is still copying two columns, AA and AB). I also need it to be a special copy because I am only pasting the values into the U column. – Louis Jun 24 '19 at 09:11
  • *I have the same error message indicating that the size of the copy range isn't equal to that of the destination, so I guess it is still copying two columns, AA and AB)* Nope, that's different. How are you pasting the values? – Foxfire And Burns And Burns Jun 24 '19 at 09:33
  • @Louis if you want only the values, you can do something like this: `destination.Range = source.Range`. I'll update my answer to reflect that. I suggest you update your question with more of your code, I`m pretty sure something else is going wrong in your code, as all 3 answers you've got so far, would do just about what you've asked, not to mention your own code posted in the question would work fine as well. – FAB Jun 24 '19 at 10:01
  • @Louis can you try something for me... run `?Range("AE1").Column` in the `Immediate Window` and let me know the result. It should be 31, but if for whatever weird reason your ranges are messed up, it might be something else. – FAB Jun 24 '19 at 10:16
  • Yep sure, I get 31 – Louis Jun 24 '19 at 10:22
  • Ok 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! – Louis Jun 24 '19 at 10:46
  • @Louis glad you've sort it.. though not sure why the `Selection` would be messed up, it only adds to the `is bad to use it !`. I've added another alternative in my answer, but generally you can pick a few good things from all answers here to improve your code. – FAB Jun 24 '19 at 10:48
0

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

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38
  • Hello, when I use this formula the exact same thing happens. it copies columns AA and AB.. – Louis Jun 24 '19 at 09:01
  • The above should copy the range "EA3:EA" up to last row, not AA/AB.. you might have something else in your code that does that. – FAB Jun 24 '19 at 09:04
  • @AhmedAU it shouldn't matter, it would still copy only from EA, ignoring the merged cells (that are not starting at EA). – FAB Jun 24 '19 at 09:08
  • @FAB I have the same problem when I stop the code just before it does the copying and do the copying manually, I select the data in AE, copy it, and it copies the columns AA and AB.. – Louis Jun 24 '19 at 09:13
  • 1
    Can we see the file @Louis ? It sounds really weird, to be honest. – Foxfire And Burns And Burns Jun 24 '19 at 09:32
  • Because the VBA code is very long (I'm creating the excel sheet from a pandas dataframe, and formatting the worksheet so that it's pretty for my boss.. but I'm also adding data from another excel and doing a few other things so it makes it pretty long) – Louis Jun 24 '19 at 09:44
  • Just the datatable, so we can try to copy and paste. But also, how are you pasting the values? – Foxfire And Burns And Burns Jun 24 '19 at 09:45
  • The data table is confidential unfortunately.. But the column I am copying doesn't exist when I first open the table. I am doing a few calculations using different columns of the table (using a few columns to the right of the data for intermediate results) and then copying the values from the column AE to the column U (I use column AE because the formula used to calculate the final value uses the other intermediate columns that I then delete after copying the values in AE to U) – Louis Jun 24 '19 at 09:52
  • To paste the values I am using Range("U3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False – Louis Jun 24 '19 at 09:53
  • Sorry can't figure out how to format the code in comments – Louis Jun 24 '19 at 09:54
  • Are there any filters active when you paste, or are there any other merged cells ? Does your code do something else between copying `AE` and pasting into `U?` – Foxfire And Burns And Burns Jun 24 '19 at 09:55
  • There are no active filters and no merged cells. It does nothing else between copying AE and pasting U. But When I break after copying AE I can clearly see that it is not AE which is copied but AA and AB columns (and this for all the methods everyone here has suggested). And as I said, if i stop the code just before the copy, and do the copy myself, it also copies AA and AB.. – Louis Jun 24 '19 at 09:57
  • But does the code arises any errors or something?. Instead of using breakpoints, execute it step by step, line by line, with F8 and check every single step of your code. – Foxfire And Burns And Burns Jun 24 '19 at 10:02
  • Ok I will try that. The message error I get says that The paste destination and copy zone are not the same size. I don't know if it helps at all, but I am comparing the code to a previous version of the code that worked (but I have changed parts of it since) to see what differs. At some point in the previous code there was "Range("AA1").Select" (it was just me clicking on AA1 when recording a macro). But if I add "Range("AA1").Select" or "Range("AA2").Select" at the same place of my new code, I get an error 1004 message saying "Method 'Range' of object '_Global' failed" – Louis Jun 24 '19 at 10:16
  • Ok I went through it 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! – Louis Jun 24 '19 at 10:46
  • Glad to help. Please, poste what you did as answer, and mark it as correct, so the question can be closed, @Louis – Foxfire And Burns And Burns Jun 24 '19 at 11:32
0

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

Error 1004
  • 7,877
  • 3
  • 23
  • 46
  • Hello, I have the same problem again when using this code. It copies columns AA and AB and not the data in AE.. – Louis Jun 24 '19 at 09:20
  • 1
    I test it and it s working. You could please create a new module, insert the code and test it? i m waiting for your results. – Error 1004 Jun 24 '19 at 10:37
  • Hi, I did try it by opening a new module and I had the same problem. This is what I did to fix it (just fixed it 2 minutes ago :) ) – Louis Jun 24 '19 at 10:48
  • I went through it 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 for your help! – Louis Jun 24 '19 at 10:48
  • If you run the code from a separated module there is no change to face some error. – Error 1004 Jun 24 '19 at 10:53