This macro gets some of what you want, but I couldn't figure out what you
wanted with:
"Column D needs to be a total, which is listed in Column H, but it in
different cells, the cell above it always
says Balance. Column E needs to be the last populated cell in the column
that says balance."
Sub Copy_Data()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "Copyto"
For Each w In ActiveWorkbook.Worksheets
Set Dest = [A1]
With Sheets(w)
.[A5].Copy Dest
.[A10].Copy Dest.Offset(, 1)
.[C3].Copy Dest.Offset(, 2)
End With
Set Dest = Dest.Offset(1)
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Post back with more detail about that above part. HTH Otto

"Kel" wrote in message
oups.com...
I have many tabs that I want to copy information into a new sheet
within the same workbook. I need to Copy A5 into Column A, A10 into
Column B, C3 into Column C. Column D needs to be a total, which is
listed in Column H, but it in different cells, the cell above it always
says Balance. Column E needs to be the last populated cell in the
column that says balance.

I have the following macro, how do I adjust it to get the additional
data?

Sub Copy_Data()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "Copyto"
For Each w In ActiveWorkbook.Worksheets
w.Range("A1").Copy Destination:=Sheets("CopyTo") _
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Thanks!