Loop through sheets error

[JM5]

Hi,

I'm new to VBA and I have a problem with a macro I created. I am trying to loop through all the worksheets in the workbook paste some formulas, do a calculation, take that calculation and place it in a worksheet at the end of the workbook and place the value into the first empty cell in column "A" of the worksheet. The problem is when I run the code I get an error on the destination line:

Range("IO5").Copy Destination:=Worksheets("Sheet4").Range("A" & Rows.Count).End(xlDown).Offset(1).Select

What am I doing wrong?

Here is the complete code:

Sub INSERT()
'
' INSERT Macro
'

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
  
    Windows("workbook1.xlsm").Activate
    Range("H1:IW1675").Select
    Selection.Copy
    Windows("workbook2.xlsm").Activate
    Range("H1").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        Range("IO5").Copy Destination:=Worksheets("Sheet4").Range("A" & Rows.Count).End(xlDown).Offset(1).Select
        
        ws.Delete
        Next ws
End Sub

Thanks

[HaHoBe]

Hi, JM5,

please add code-tags when showing procedures where on Excel Forum.

You start at the last cell and want to go further down - no chance there:

Range("IO5").Copy Destination:=Worksheets("Sheet4").Range("A" & Rows.Count).End(xlDown).Offset(1)

You could check the last cell for any entry:

Range("IO5").Copy Destination:=Worksheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1)

Ciao,
Holger

[JM5]

Hey thanks. That worked great. I have one more problem. When I delete the worksheet at the end of the macro, it removes the the numbers in "sheet4" and gives me zeros. I tried looking at how to special paste values with a destination, but it seems you can't that. How would I paste the values either directly to "sheet4" or paste the way I am doing and then repasting the values in "column B" with special paste? Thanks.

[Izandol]

You may put the value directly:

Worksheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Range("IO5").Value

[JM5]

That works thanks.

[abousetta]

Hi @JM5,

Please add code tags to your first post and mark the thread as solved.

Thanks.

abousetta