Copying and Pasting Cells

Replace:
Range("J8:J" & Range("B65536").End(xlDown).Row).Formula = "=RC[-1]-RC[3]"

With:
Range("J8:J" & lRow).Formula = "=RC[-1]-RC[3]"

You defined lRow with the statement:
lRow = Range("B1").End(xlDown).Row

I assume that if you go to cell B1 and press END+ARROW DOWN you will get to the last row of your data.

Try it and let me know.

Barrie

Thanks Barrie - it does paste the formula, but it pastes it all the way down to row 65,536, even though there is only data until row 1485. When I hit ctrl and the down arrow, it goes all teh way to 65,536... any ideas how to stop that???

CJ, what column contains data in ALL cells (from the first row to the bottom row)?

Barrie

Barrie, column a has data from row 1 all the way to 1485. : Thanks Barrie - it does paste the formula, but it pastes it all the way down to row 65,536, even though there is only data until row 1485. When I hit ctrl and the down arrow, it goes all teh way to 65,536... any ideas how to stop that???

Perfect. Let's try this again.

Replace:
Range("J8:J" & Range("B65536").End(xlDown).Row).Formula = "=RC[-1]-RC[3]"

With:
Range("J8:J" & lRow).Formula = "=RC[-1]-RC[3]"

Replace:
lRow = Range("B1").End(xlDown).Row

With:
lRow = Range("A1").End(xlDown).Row

Try it again and let me know.

Regards,
Barrie

Hi again Barrie, it is still copying all the way to the bottom. My code is below. I don't know if this is what the problem is, but when I delete all the data, so all I have in the spreadsheet is the button that calls the procedure, If I do the control end, it goes to the row 65536, even though there is no data at all in the spreadsheet. Could this be the reason why???

Thanks again,
CJ

Private Sub cmdImport_Click()
Dim rec As Recordset
Dim rge As Range
Dim intRows As Integer
Dim intFields As Integer
Dim strSelect As String
Dim strConn As String
Dim db As Database
Dim wsp As Workspace
Dim stDocName1 As String
Dim lRow As Long
Dim RangeStr As String

lRow = Range("A1").End(xlDown).Row

Call Clear_DataRange

Set wsp = DBEngine.Workspaces(0)
Set db = wsp.OpenDatabase("n:\PPORevenue1.mdb")
db.QueryTimeout = 15000

Set rge = Worksheets("RTF").Range("a7")

Set rec = db.OpenRecordset("Monthly All PPO Results by Processing Site")
rec.MoveLast
intRows = rec.RecordCount
rec.MoveFirst
intFields = rec.Fields.Count

'pastes field names
For intCount1 = 0 To intFields - 1 'do as many times as there are fields
rge.Cells(1, intCount1 + 1).Value = rec.Fields(intCount1).Name
Next intCount1

'pastes field values
For intcount2 = 0 To intRows - 1 'do this as many times as there are rows
For intcount3 = 0 To intFields - 1 'do this as many times as there are fields
rge.Cells(intcount2 + 2, intcount3 + 1).Value = rec.Fields(intcount3).Value
Next intcount3
rec.MoveNext
Next intcount2

'Insert blank columns
Columns("I:K").EntireColumn.Insert
[K7] = "PPOSAVINGS%"
[J7] = "PPOSAVINGS"
[I7] = "PPOSTDREDUCT"

RangeStr = "J" & (7 + RecordCount)
Range("B8").EntireColumn.Insert

'Insert values into columns
'Range("J8:J" & Range("B65536").End(xlUp).Row).Formula = "=RC[-1]-RC[3]"
'Range("J8:J" & Range("RangeStr").End(xlUp).Row).Formula = "=RC[-1]-RC[3]"
Range("J8:J" & lRow).Formula = "=RC[-1]-RC[3]"
Range("K8:K" & lRow).Formula = "=RC[2]-RC[3]"
Range("L8:L" & lRow).Formula = "=RC[-1]/RC[-3]"
rec.Close

'Format width of columns and add border around titles
Range("A:Z").Columns.AutoFit 'starting and ending cell in ()

'With Worksheets("RTF")
'.Range(.Cells(7, 1), .Cells(7, 7)).Borders.Weight = xlThin
'End With

db.Close
End Sub

Hi CJ, you are bang on with your guess. If I understand your code, try putting:
lRow = 7 + RecordCount
right before:
RangeStr = "J" & (7 + RecordCount)
and delete:
lRow = Range("A1").End(xlDown).Row

Let me know if this works for you.
Barrie
PS - I won't be checking MrExcel until Saturday.

Thanks Barrie!!!

I finally got it to work - thanks so much for all your help!!! :) Hi CJ, you are bang on with your guess. If I understand your code, try putting: