Copyrecordset is not pasting correctly when F5 is pressed, but works in F8

[aravindhan_31]

Hi,

I have a macro which extracts & copies data from SQL Server and pastes in Excel, for some strange reason, the data is pasted in row 999 and not in A10 which was specified clearly in the code

ActiveSheet.Cells(10, 1).CopyFromRecordset rst

but when i go the macro editor and press F8 to run step by step it pastes in A10.

Can you please help me how to solve this problem

Regards
Arvind

 

[Kyle123]

What's the rest of the code?

 

[aravindhan_31]

Sub master()
'Dim ws As Worksheet

Application.Calculation = xlCalculationAutomatic

Load Wait
Wait.Show vbModeless
DoEvents

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.ActiveWorkbook.Worksheets("Parameters").Visible = True
For Each ws In Worksheets
nme = ws.Name
   'ws.Select
   Sheets(nme).Activate
   If (ws.Name <> "Home" And ws.Name <> "Template" And ws.Name <> "Parameters") Then
   ActiveWindow.SelectedSheets.Delete
   End If
Next ws

Sheets("Parameters").Select
Range("e1") = "=COUNTA(C1:C20)"
cnt1 = Range("e1")

For i = 1 To cnt1
Sheets("Parameters").Select
If Range(Cells(i, 3), Cells(i, 3)) <> "" Then
clsnme = Range(Cells(i, 3), Cells(i, 3))
Call Refresh_data(clsnme)
 Rows("10:1000000").Select
    Selection.EntireRow.Hidden = False
    Range("A10").Select
End If


Next

Unload Wait

Application.ActiveWorkbook.Worksheets("Parameters").Visible = False

End Sub

and

'Sub Refresh_data()
Sub Refresh_data(clsnme)
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stDB, stConn, stSQL1, str As String, subd As String, bk1 As String, sec As String

Dim count As Long
Dim style As String

'Application.ScreenUpdating = False

Set cnt = New ADODB.Connection
cnt.ConnectionString = connString
cnt.ConnectionTimeout = 1000
cnt.CommandTimeout = 5000
Set rst = New ADODB.Recordset

sec = "'" & ThisWorkbook.Sheets("Home").Range("E6")
style = "'" & ThisWorkbook.Sheets("Home").Range("C15")

Application.ActiveWorkbook.ActiveSheet.Activate
subd = "'"
subd = subd + CStr(clsnme)
subd = subd + "%'"


'subd = Left(subd, Len(subd) - 2)
DoEvents


'Sheets("Lineprint template").Delete
Application.ActiveWorkbook.Worksheets("Template").Visible = True
'Application.ActiveWorkbook.Worksheets("Parameters").Visible = True
Sheets("Template").Select
Sheets("Template").Copy After:=Sheets("Home")
Application.ActiveWorkbook.ActiveSheet.Name = clsnme
Application.ActiveWorkbook.Worksheets("Template").Visible = False
ActiveWorkbook.Sheets("Home").Activate


stConn = '' deleted for security reason;


str = " select "'str = str + " LP.FP_Cost, "
str = str + " LP.Promo_Cost, "
str = str + " LP.Total_Cost, "
str = str + " LP.Markdown_Cost_YTD, "
str = str + " LP.Promo_Cost_YTD ,"
str = str + " LP.Ref "
tr = str + " from Reporting.Line_Print_Main LP"
str = str + " WHERE Section_Name in (" & sec & ")"
str = str + " And Class_Name like " & subd & ""
str = str + " and LP.Price_Status <>'0'"
str = str + " and LP.Style in (" & style & ")"



Debug.Print str
ActiveWorkbook.Sheets(CStr(clsnme)).Activate


Debug.Print str
With cnt
.Open (stConn)
End With

With rst
.Open str, cnt
End With


Application.ActiveWorkbook.ActiveSheet.Activate
ActiveSheet.Cells(10, 1).CopyFromRecordset rst
Range("b4") = clsnme
Range("b5").Copy
Range("b5").PasteSpecial xlPasteValues

rst.Close
Set rst = Nothing
Set cnt = Nothing

 

[aravindhan_31]

Hi,

Can someone help me on this?

Regards
Arvind