MAC VBA Trying to cut one cell from each worksheet and paste in another sheet in next empty cell

Question

I am trying to go through each worksheet in the active workbook in which the sheets are created as part of a macro that is working. The names can be different each time, so I can't just do a lookup. I want to take one piece of information (same cell on each worksheet) and paste it into the next empty cell in a column on another sheet.

At line 6 I get the error subscript out of range error 9. I've checked the naming of the sheet I am pasting to and it is correct. No extra spaces.

It's driving me nuts. Can you help?

ps I am working on this to run on a MAC which I normally don't do, so maybe I have the code slightly wrong.

For Each sh In ThisWorkbook.Worksheets
DoEvents

    sh.Activate
    Range("K5").Select
    Selection.Copy
    Sheets("Payment Ref").Range("b2").Select
    If Range("b2") = "" Then
    Range("b2").PasteSpecial Paste:=xlPasteFormats
    Range("b2").PasteSpecial Paste:=xlPasteValues
     Else
    Range("b2").Offset(1, 0).PasteSpecial xlPasteFormats
    Range("b2").Offset(1, 0).PasteSpecial xlPasteValues
    End If


Next sh

At the end of the run the Payment Ref Spreadsheet should have a column filled with invoice numbers from the invoice sheets created in the previous macro section. This works perfectly.

First of all you might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • Then please tell which one is line 6 (everyone counts different). Please tell which code throws the error. — Pᴇʜ, Sep 25 '19 at 12:56

Pᴇʜ · Answer 1 · 2019-09-25T13:13:48.330

0

Avoid .Select and .Activate (see How to avoid using Select in Excel VBA) and reference a worksheet for all your ranges (eg using With).

Also you need to find the last used cell everytime you paste. A good way to find the last used cell in a column is:

Cells(Rows.Count, "B").End(xlUp) 'last used cell in column B

So the following should work for you

Dim wsDestination As Worksheet
Set wsDestination = ThisWorkbook.Worksheets("Payment Ref")

For Each sh In ThisWorkbook.Worksheets    
    sh.Range("K5").Copy

    With wsDestination.Cells(wsDestination.Rows.Count, "B").End(xlUp).Offset(RowOffset:=1)
        .PasteSpecial Paste:=xlPasteFormats
        .PasteSpecial Paste:=xlPasteValues
    End With
Next sh

edited Sep 25 '19 at 13:13

answered Sep 25 '19 at 12:59

Pᴇʜ

56,719
10
49
73

HI. Now I get a compile error Data Member not found. I am working on MAC so as I said the coding may be slightly different. The error is on this line sh.Range("K5").Copy asnd range is highlighted so guessing on MAC it has to be worded differently. I know this works on PC but coding for working on MAC it doesn't seem to like it. – Paula Hansen Sep 25 '19 at 15:39

riskypenguin · Answer 2 · 2019-09-25T13:12:51.357

-1

You should avoid using .Select in general, as linked in the comments. This is most likely the cause for your error. Also, if you perform this code on more than 1 or 2 worksheets (depending on if B2 already has a value) the procedure will keep putting the values in cell B3. I'd suggest the following changes:

For Each sh In ThisWorkbook.Worksheets
    sh.Range("K5").Copy
    Sheets("Payment Ref").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteFormats
    Sheets("Payment Ref").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Next sh

I think this should work just as well:

For Each sh In ThisWorkbook.Worksheets
    sh.Range("K5").Copy Sheets("Payment Ref").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
Next sh

edited Sep 25 '19 at 13:12

answered Sep 25 '19 at 13:00

riskypenguin

2,139
1
10
22

Feel free to downvote, but please also let me know your suggestions on improving my answer, no way to learn otherwise. – riskypenguin Sep 25 '19 at 13:05
1

Not my dv, but are you sure you wanted to paste on `sh`? Doesn't look like that's what OP wanted. – BigBen Sep 25 '19 at 13:07
Thanks all. I'll try suggestions and let you know how it goes. Thanks for the .select tip – Paula Hansen Sep 25 '19 at 14:40
Hi. Also tried this and I get an error on this line: sh.Range("K5").Copy Sheets("Payment Ref").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) . with Range highlighted - compile error data member not found. As I said I am working on MAC so maybe the code is slightly different. – Paula Hansen Sep 25 '19 at 15:41

MAC VBA Trying to cut one cell from each worksheet and paste in another sheet in next empty cell

2 Answers2