Sorry about that................5th line in.

Dim cn As New ADODB.Connection
Says it's an undefined user type. Don't know how to define it, I'm a
newbie to VBA, but I know this works.............it is someone else's
code but it works in another excel file doing the same thing. All I did
was chage the DB and table from which it is retrieving the data.

Jim Thomlinson wrote:
Can you narrow it down a bit. What part is not working. Does your open
statement (AssetData.Open) return a valid recordset, ...
--
HTH...

Jim Thomlinson


"loren.pottinger" wrote:

Hey Thanks jim.

That worked great, but my first sub isn't working either. Let me know
if you see the problem. Thanks again.

Sub getFixedAssets()

Application.Calculation = xlCalculationManual
Dim m As Variant
Dim rglast As Variant
Dim cn As New ADODB.Connection
Set cn = New ADODB.Connection
cn.Open connString

Dim AssetData As ADODB.Recordset
Set AssetData = New ADODB.Recordset
Dim AssetQuery As String

AssetQuery = "SELECT FourDot, ProjectName, Mid(Acct,1,2) as [Type],
NULL, desc, [acq date], lifemonths, cost, nbv, dep from [FixedAssets1]
" & _
" WHERE FourDot like '" & Sheet20.Range("A1") & "%' order by
FourDot, acct;"
AssetData.Open AssetQuery, cn, adOpenForwardOnly, adLockReadOnly


Dim startRange As Range
Set startRange = Sheet20.Range("A5")

Dim i As Integer

i = 0
Dim r As ADODB.Record


If Not AssetData.EOF Then
'For Each r In AssetData
AssetData.MoveFirst

Do While Not AssetData.EOF

For m = 0 To 9
startRange.Offset(i, m).Value = AssetData.Fields(m).Value
'
Next m

startRange.Offset(i + 1, 0).EntireRow.Insert

i = i + 1
AssetData.MoveNext
Loop
AssetData.Close
Set AssetData = Nothing
cn.Close
Set cn = Nothing
'FixedAssets.CopyFixedAssetsformulas
Else
AssetData.Close
Set AssetData = Nothing
cn.Close
Set cn = Nothing
'FixedAssets.SetZero
End If
FixedAssets.CopyFixedAssetsformulas
'FixedAssets.HighlightStartEndIssues

Application.Calculation = xlCalculationAutomatic
End Sub

Jim Thomlinson wrote:
You never verify if you found Summary or not. Try something more like this...

Sub CopyFixedAssetsformulas()
Dim rngFound As Range

With Sheet20
Set rngFound = .Range(.Range("A5"), .cells(rows.count,
"A").end(xlup)).Find(What:="Summary", LookIn:=xlValues, LookAt:=xlWhole,
MatchCase:=False)


if rngfound is nothing then
msgbox "No Summary"
else
.Range("K5").Copy
.Range(.range("K6"), .cells(rngfound.row - 2, "K")).PasteSpecial
(xlPasteFormulasAndNumberFormats)
.Range("V5").Copy
.Range(.range("V6"), .cells(rngfound.row - 2, "V")).PasteSpecial
(xlPasteFormulasAndNumberFormats)
Application.CutCopyMode = False


.Range("A5").Select
end if
end with

End Sub

--
HTH...

Jim Thomlinson


"loren.pottinger" wrote:

I keep getting the error in the subject line. Can someone please help.
Thank you.

Sub CopyFixedAssetsformulas()
Dim startRange As Range
Dim rglast As Variant
Dim lastrange As Variant
Set startRange = Sheet20.Range("A5")
Set rglast = Sheet20.Range("A5").SpecialCells(xlCellTypeLastCel l)


Set lastrange = Sheet20.Range("A5", "A" &
rglast.Row).Find("Summary", LookIn:=xlValues)


Sheet20.Range("K5", "V5").Copy
Sheet20.Range("K6", "V" & lastrange.Row - 2).PasteSpecial
(xlPasteFormulasAndNumberFormats)
Application.CutCopyMode = False


Sheet20.Range("A5").Select


End Sub