Macro not working

Hi,
I posted a thread before asking about how to take data from several sheets and list it all (consecutively) in one column in a separate sheet (summary). The problem was that the data (in column I) had differing number of rows on each sheet, and i wanted it all listed in one column in order of sheet number. RYLO helped out by writing some code to do this....


Sub aaa()
Sheets("Summary").Range("A:A").ClearContents
Sheets("Summary").Range("A1").Value = "distance"

For i = 1 To Sheets.Count
If Sheets(i).Name <> "Summary" Then
With Sheets(i)
.Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row).Copy Destination:=Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
End If
Next i

End Sub


However, it didnt quite work, maybe becuase my data were in column I instead of column A. When I changed the column A within the code to column I, it returned #ref when i ran the code. Then, I tried to move the data to column A, but that also didnt work and 0 were returned.
Does anyone have any suggestions on what is going wrong?
thanks so much

Sarah

Sarah

How did you change the code?

I think I figured out my problem but I don't know how to solve it.

First, I just changed the code by substituting the correct column letter in this line:

.Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row).Copy Destination:=Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

instead of column A2:A, my data is in column I2:I. The output was #REF values. However, the data in these columns (the I columns in each sheet) are cummulative distances and are a formula not just a value. When I copy paste special the value only, then the macro works.

Is there anyway to incorperate this into the macro so it returns the value only?

thanks!

Hello Sarah,

I modified Rylo's macro to paste only the values.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

That worked perfectly, thank you SO much