Hi all,
I am pretty new to vba and am having trouble. I have chopped, slopped and butchered a macro from another program trying to suit my needs and am having difficulty in making it work like I want it to. With a fresh sheet it works the first time. When I try it a second time I get the run-time error and when I try to debug it, vba points me to the .refresh backgroundquery:= False line. My macro pulls data from one tab in my workbook and places it in column A, everything else is pulling from a SQL database. I will try to provide a screenshot of the worksheet and the code. I want all of the data from A2:J17 to move down and be replaced with new data when I update and I want row 1 to remain stationary. Any ideas where I am going wrong?
Sub Update_Names()
Sheets("Data Entry").Select
ActiveWindow.SmallScroll Down:=54
Range("F75:F82").Select
Selection.Copy
Sheets("18-02 Sliving").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Forming 18-02***********************************
Sheets("Data Entry").Select
Range("F85:F92").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("18-02 Sliving").Select
Range("A10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Columns("A:A").EntireColumn.AutoFit
'Range("B14").Select
Dim strSQL As String
Dim V_Beg_Date As String
Dim V_End_Date As String
V_Beg_Date = Sheets("Data Entry").Range("D3")
V_End_Date = Sheets("Data Entry").Range("D4")
Sheets("18-02 Sliving").Select
'Cells.Select
'Selection.Delete Shift:=xlUp
'Selection.Delete Shift:=xlUp
'Selection.Clear
'Selection.ClearFormats
'ActiveWindow.FreezePanes = False
Range("B1").Select
strSQL = "SELECT TIMESTAMP, CHOPPER, SHIFT_CODE 'Shift', OE , "
strSQL = strSQL & "CE, BBOH, HTB, QTY_ON_HOLD '# on Hold', "
'strSQL = strSQL & "NUM_CHUTE_JAMS '# CJs', AVG_CHUTE_JAM_TIME 'Chute Downtime', "
'strSQL = strSQL & "TOT_CHUTE_JAM_TIME 'Total CJ Time', TOT_NET_BTA 'Net BTA', "
'strSQL = strSQL & "TOT_NET_FTA 'Net FTA', TOT_ROPE_OCC '# Rope Occ', "
'strSQL = strSQL & "TOTAL_LC_OCC '# Long Chop Occ', COT_CHANGES '# Cot Changes', "
strSQL = strSQL & "CHOP_CHECK_PCT 'Chop Check %'"
strSQL = strSQL & "From dbo.SHIFT_SUMM_CHPRDATA2 "
strSQL = strSQL & "Where ""timestamp"" >= '" & V_Beg_Date & "' "
strSQL = strSQL & "and ""timestamp"" < '" & V_End_Date & "'"
server = "ODBC;DSN=***_***"
'On Error Resume Next
'ActiveSheet.QueryTables(1).Delete
'On Error GoTo 0
With ActiveSheet.QueryTables.Add(server, _
Destination:=Range("B1"))
.Sql = (strSQL)
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With
End Sub
Bookmarks