Bruce,

You can eliminate all of your select statements...
Sheets("Data_Assembly").Select
Range("Workarea").Select
ActiveCell.Value = Range("Detailloop").Offset(0, 0)
' change this to
Sheets("Data_Assembly").Range("Workarea").Value = _
Range("Detailloop").Offset(0, 0)
' repeat for the other cells

Since "Workarea" is a range of cells, you'll first have to identify the
first cell.

For the paste part:

Crows = Sheets("your data source").(Cells(1, 1).End(xlDown).Row
where Crows = the last row of the copy area

Prows = Sheets("your paste sheet").Cells(Rows.COUNT, "A").End
_(xlUp).Offset(1, 0).Row
where Prows = the row to paste to

Then
Sheets("your data source").Range(Cells(1,1),Cells(Crows,1).Copy
With Sheets("your paste sheet").Cells(Prows,1)
.PasteSpecial Paste:=xlValues
End With
' assuming you are only working with column A.

Play with this and see if it will do what you want. Make the appropriate
changes to suit your needs.

steve

"Bruce Roberson" wrote in message
...
Well, I gave up the idea of embedding the formulas in VB
if you followed the discussion the other day I had with
Bob and Tom.

This loop for the detail works, but I am sure you will
find the coding structure to be horrendously inefficient.
I still have too many selects as I work between sheets and
ranges to start my copying.

I keep having to select my worksheet on one line, and then
my range on another line. If I don't, it keeps giving me
this run time error '1004' Select method of range class
failed.

That is why the sheet is selected on one line (see line 7
of the loop), and then I selected the range on a separate
line.

There are three basic worksheets I'm working between. The
sheet "import" is where I've imported the row wise data
from my database Alpha V. The sheet "Data_Assembly" is
where I assemble results from all those awful formulas you
got a peak at yesterday, and its where I have a gob of
spreadsheet ranges. And it also is where I temporarily
copy the results of the data assembly into a spreadsheet
ranged called "Workarea".

Finally, it copies structured data from the workarea into
the sheet called "report". This is where I'm laying out my
report to the state, and the layout on this sheet is
straight text in columnar arrangement. It will be copied
and pasted to another file and saved as a TXT file when
this thing is finished.

So, those are my three sheets in the workbook that are
referred to in this looping, Import, Data_Assembly, and
Report.

Here is the detailloop at this point:


Sub detailloop()
Dim Crows As Long
Range("rowsum").Value = 1
Sheets("Import").Select
Crows = Cells(1, 1).End(xlDown).Row
For i = 1 To Crows - 1
Sheets("Data_Assembly").Select
Range("Workarea").Select
ActiveCell.Value = Range("Detailloop").Offset(0, 0)
ActiveCell.Offset(1, 0).Value =
Range("Detailloop").Offset(1, 0)
ActiveCell.Offset(2, 0).Value =
Range("Detailloop").Offset(2, 0)
ActiveCell.Offset(3, 0).Value =

And so on like this through the 13th offset then I do a
check of a True false range in the if statement below:

If Range("Do_lse_use") Then
ActiveCell.Offset(14, 0).Value =
Range("lease_use").Offset(0, 0)
ActiveCell.Offset(15, 0).Value =
Range("lease_use").Offset(1, 0)

This part goes on till the offset in range "lease use" is
at 13.


Else
GoTo copyrecords
End If
copyrecords:
Range(ActiveCell, ActiveCell.End(xlDown)).Copy
Sheets("Report").Select
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial xlPasteValues
Worksheets("Data_Assembly").Select
Range("Workarea").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Clear
Range("Row").Value = Range("Row").Value + 1
If Range("New_Prmo") Then
Range("rowsum").Value = Range("Rowsum").Value + 1
Call summonth
Else
End If
Next
End Sub


.