0

the following code is supposed to (amongst other things) select non-adjacent columns in a worksheet, select all the rows with data, copy them to another worksheet and paste. Unfortunately, I am getting the 'You can't paste this here because the Copy and paste area aren't the same size..' message:

Sub Macro2()
'
' Macro2 Macro
 
    Sheets("Share Registry Transactions").Select
    Range("A1:AA2").EntireRow.Select
    Selection.UnMerge
    Rows("1:2").Select
    Selection.Delete Shift:=xlUp
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "=WORKDAY(RC[-1],3)"
    Range("D1").Select
    Selection.Copy
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A4,C2").Select
    Range("C2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A4,C2:C4,D2").Select
    Range("D2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A4,C2:C4,D2:D4,E2").Select
    Range("E2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A4,C2:C4,D2:D4,E2:E4,G2").Select
    Range("G2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A4,C2:C4,D2:D4,E2:E4,G2:G4,H2").Select
    Range("H2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A4,C2:C4,D2:D4,E2:E4,G2:G4,H2:H4,I2").Select
    Range("I2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A4,C2:C4,D2:D4,E2:E4,G2:G4,H2:H4,I2:I4,J2").Select
    Range("J2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A4,C2:C4,D2:D4,E2:E4,G2:G4,H2:H4,I2:I4,J2:J4,L2").Select
    Range("L2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:A4,C2:C4,D2:D4,E2:E4,G2:G4,H2:H4,I2:I4,J2:J4,L2:L4,M2").Select
    Range("M2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Daily Recon").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveSheet.Paste
End Sub

Any help greatly appreciated.

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
Dave
  • 3
  • 1
  • You can safely e.g. do this: `ThisWorkbook.Worksheets("Share Registry Transactions").Range("A2:A4,C2:E4,G2:J4,L2:L4").Copy ThisWorkbook.Worksheets("Daily Recon").Range("A" & Rows.Count).End(xlUp).Offset(1)` which doesn't cover whatever you're trying to do with `.End(xlDown)` which you are possibly using since one of the rows is empty. Why don't you share the screenshots of the worksheets and accurately explain what needs to be copied (the exact address)? You can [edit your post](https://stackoverflow.com/posts/72763652/edit) at any time. – VBasic2008 Jun 26 '22 at 17:48
  • Hi, the .End(xlDown) was because I never know how many lines will be added to Share Registry Transactions. There could be 1 line, there could be 20. I wanted to select all rows with data in them. Screenshots are tricky as I am working in the Cloud but cannot access this site from there. – Dave Jun 26 '22 at 18:54
  • Will column `A` have the most rows, or could it be some other column? Is there data below the data you're trying to reference? Does `ThisWorkbook.Worksheets("Share Registry Transactions").Range("A1").CurrentRegion.Select` select the whole range obviously including the last row? – VBasic2008 Jun 26 '22 at 19:24
  • When you copy multiple non-contiguous ranges,each area must be the same shape (ie same number of rows). Try doing it manually to see what I mean. – chris neilsen Jun 26 '22 at 19:36
  • Each column will have the same number of rows. There is no data below the data I am trying to reference. Basically the point of the exercise is to take a data extract and remove the useless columns. – Dave Jun 26 '22 at 20:04
  • @chris I believe that each area has the same number of rows. – Dave Jun 26 '22 at 20:06
  • Why not copy the whole sheet and delete the columns which you don't need? – simple-solution Jun 26 '22 at 20:19
  • @dave well, the error message suggests otherwise. Verify by adding a `Debug.Print Selection.Address` and check the results in the immediate window. – chris neilsen Jun 26 '22 at 21:13
  • And it would much easier to read and debug if you [avoid Select, Selection, Activate. ActiveCell](https://stackoverflow.com/q/10714251) – chris neilsen Jun 26 '22 at 21:15

1 Answers1

0

So, as already commented this code would be easier to walk through without the selects and activates (though I assume you're fairly new to this so welcome!). also stopping this code at every line shows it doing kind of odd things honestly, at each line you select a number of ranges rows 2 to 4 and one in just row 2 (i.e. A2:A4, C2:C4, D2). then the selection xlDown selects column A row 2 to the end of table (i.e A2:A8 for example), then the code repeats with another range added for selection for rows 2 to 4 but still goes back to selecting column A it appears.

I believe what you want is:

Sub test()

    Set ws = ActiveWorkbook.Sheets("Share Registry Transactions")
    ws.Range("1:2").EntireRow.UnMerge
    ws.Rows("1:2").Delete Shift:=xlUp
    
    lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

    ws.Range("D2:D" & lastRow).FormulaR1C1 = "=WORKDAY(RC[-1],3)"
    
    ws.Range("A2:A" & lastRow & ",C2:C" & lastRow & ",D2:D" & lastRow & ",E2:E" & lastRow & ",G2:G" & lastRow & ",H2:H" & lastRow & ",I2:I" & lastRow & ",J2:J" & lastRow & ",L2:L" & lastRow & ",M2:M" & lastRow).Copy
        
    'Sheets("Daily Recon").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveSheet.Paste
End Sub

this will select columns a,c,d,e,g,h,i,j,l and m from row 2 to the lastrow populated in column A (note: if any other column is populated past the last row in column A those values will be lost. These values would be overwritten in the next running of the code anyway because of how the last three lines assign where to paste the incoming data in sheet "Daily Recon").

It will also put them where you want them using your last bit of code (there is a little inefficiency here from using select. But honestly this code should run relatively quickly so unless you're planning to loop it multiple times I wouldnt worry.

InjuredCoding
  • 430
  • 3
  • 5
  • Many thanks, I'll give this a go. Yeah I am a complete novice and recorded a lot of the code. Thanks to all. – Dave Jun 27 '22 at 06:21
  • this seems to work fine. I changed ws.Rows("1:2"). Delete to wsRows("1").Delete as I was losing the first line of data. Would you be able to help further with the following Range("D1").Select ActiveCell.FormulaR1C1 = "=WORKDAY(RC[-1],3)" Range("D1").Select Selection.Copy Range("D2").Select Range(Selection, Selection.End(xlDown)).Select ActiveSheet.Paste Here I am taking the date in column C and adding 3 business days and applying this to column D. – Dave Jun 27 '22 at 07:30
  • put this directly after lastRow: ws.Range("D2:D" & lastRow).FormulaR1C1 = "=WORKDAY(RC[-1],3)" – InjuredCoding Jun 27 '22 at 09:52
  • couple of things, if you don't mind marking the answer as accepted that would be great. Also the thing that helps a lot with going through VBA code and was essential here is stopping the code and looking at what is happening. If you click on the grey bit slightly left of the code area in VBA it will turn the line of code red. this will make the code stop when it gets to this line (before executing it), open the locals window (View -> Locals Window) put code and excel next to each other and step by step you will see what's happening. – InjuredCoding Jun 27 '22 at 09:58
  • Ok I marked the answer as accepted. Many thanks for answering. Just finally, I am being a bit thick here but where do I put that final bit of code? Thanks – Dave Jun 27 '22 at 10:20
  • thanks, I have edited my answer to make it clearer with that code as well. let me know if that covers everything you were asking! – InjuredCoding Jun 27 '22 at 10:45
  • I have to hand it to you, I think you're a blinking genius! Works perfectly. Thanks for your time, patience and tips! – Dave Jun 27 '22 at 10:59
  • sure thing, I'm learning from the challenge! – InjuredCoding Jun 27 '22 at 11:04