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

2
votes

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.

2
excelvbamacos
First of all you might benefit from reading 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ᴇʜ
Sheets("Payment Ref").Range("b2").Select – Paula Hansen

2 Answers

0
votes

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
-1
votes

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