Sorry my last message was probably very confusing. I've worked on this all
afternoon trying to get the correct syntax.....This script works great...i
just need to tweak it a bit.....In each sheet for column D if the row/cell
includes the word TOTAL ..ex (18051-0707 Total) I want to select 18051, and
if it is the total row then extact the extended cost in column L of each
sheet....and put in Echosheet. Can you please help?

"JLatham" wrote:

Here's what I come up with. I was a bit confused as your sample code had a
title of RoundToZero and yet it does no such thing. But no matter...

Sub WorkThroughData()
Dim echoSheet As Worksheet
Dim ws As Worksheet
Dim colDRange As Range
Dim anyColDCell As Range
Dim newFormula As String

'get your Echo sheet into the
'workbook and then pick up
'here to work through the
'other sheets/data
Set echoSheet = Worksheets("Echo")
For Each ws In ThisWorkbook.Worksheets
If ws.Name < echoSheet.Name Then
Set colDRange = ws.Range("D1:" & _
ws.Range("D" & Rows.Count).End(xlUp).Address)
For Each anyColDCell In colDRange
If Not IsEmpty(anyColDCell) Then
'you're going to want to tweak
'this formula, probably using
'the row number of anyColDCell
'as anyColDCell.Row
newFormula = "=MID('" & ws.Name & _
"'!R[3]C[3],1,5)"
'put formula into next available
'cell in column A of Echo sheet
echoSheet.Range("A" & Rows.Count). _
End(xlUp).Offset(1, 0).FormulaR1C1 _
= newFormula
End If
Next ' individual cell loop
End If
Next ' worksheet loop end
'all done, do housekeeping
Set colDRange = Nothing
Set echoSheet = Nothing
End Sub

Hope this gets things moving for you.

"pm" wrote:

I have a spreadsheet that has 6 tabs and need to read the data in a specific
column (D) in each sheet...if the cell is not blank then store the data in a
new sheet....then go to next sheet and so on...i can't get my loop to
work....any suggestions?

This creates the new sheet:

Set NewSheet = Worksheets.Add
NewSheet.Name = "Echo"
Sheets("Echo").Select
Sheets("Echo").Move After:=Sheets("Store Ops")
Sheets("Apparel").Select

I want this to read data in each cell in column D for each sheet and if it's
not blank then write record to Echo sheet:

Sub RoundToZero()
For Each ws In Worksheets
dlr = Cells(Rows.Count, 1).End(xlUp).Row + 1
If ws.Name < ActiveSheet.Name Then
With ws
Cells(dlr, "d") = .Range("d2").Value
Next ws
End If
End Sub

This is the data i want to write to Echo sheet:

Sheets("Echo").Select
ActiveCell.FormulaR1C1 = "=MID(Janitorial!R[3]C[3],1,5)"
Sheets("Echo").Select