ActiveCell.End(xlDown).Select not working consistently
 

I have some vba set up to import a query from access, go to the top of
a column, and select to the last value. When I run the two Subroutines
seperately, they work fine. However when I call them from another Sub,
the whole column from the top down to the bottom of the sheet is
selected, ruining the next part? Does anyone have any clues as to why
this is happening? Thanks.

################################################## #############################

Sub ImportData()
'
' Macro to import summary data according to LID from MSAccess,
qryBudgetMonitoringDetailSubtotals
'
Dim strBudgetManager As String

On Error Resume Next

' Verify a LID has been set, if not get one
If Range("LID").Value = "" Then
strLID = InputBox("What is your LID?")
Range("LID") = strLID
Else
MsgBox "Your LID is " & Range("LID")
End If

' Select then delete the "Summary" sheet to ensure clean data import
Sheets("Summary").Select
Application.DisplayAlerts = False ' Turns off delete sheet warning
ActiveWindow.SelectedSheets.Delete
Set wsNewWorkSheet = Worksheets.Add(after:=Worksheets(1))
wsNewWorkSheet.Name = "Summary"

' Set LID variable from home page, or by an input box
strLID = Range("LID")
MsgBox "Importing Data from Budget Monitoring"

'Import data from access
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=S:\REVMON\Budget
Monitoring\Maintenance\current.mdb;DefaultDir=S:\R EVMON\Budget
Monitoring\Maintenance;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTi" _
), Array("meout=5;")), Destination:=Range("B4"))
.CommandText = Array( _
"SELECT qryBudgetMonitoringDetailSubtotals.AllocationBM_LI D,
qryBudgetMonitoringDetailSubtotals.`Budget Manager`,
qryBudgetMonitoringDetailSubtotals.AllocationFMO_L ID,
qryBudgetMonitoringDetailSubtotal" _
, _
"s.FMO, qryBudgetMonitoringDetailSubtotals.CostCentre,
qryBudgetMonitoringDetailSubtotals.CostCentreDescr iption,
qryBudgetMonitoringDetailSubtotals.Budget,
qryBudgetMonitoringDetailSubtotals.Spend, qry" _
, _
"BudgetMonitoringDetailSubtotals.Projected,
qryBudgetMonitoringDetailSubtotals.Variance" & Chr(13) & "" & Chr(10)
& "FROM `S:\REVMON\Budget
Monitoring\Maintenance\current.mdb`.qryBudgetMonit oringDetailSubtotals
qryBudgetMonitoringDetailSubtotals" & Chr(13) & "" & Chr(10) & "WHERE
(qryBud" _
, _
"getMonitoringDetailSubtotals.AllocationBM_LID ='" & strLID &
"')" & Chr(13) & "" & Chr(10) & "ORDER BY
qryBudgetMonitoringDetailSubtotals.CostCentre" _
)
.Name = "BudgetSubtotalsImport"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery = False
End With

End Sub

################################################## #############################

Sub RangeNaming()
Sheets("Summary").Select

' Set CostCenters range
Range("F4").Select
Range(Selection, Selection.End(xlDown)).Select
If Selection.Count 2 Then
Range("F5").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Name = "CostCenters"
Else
Range("F5").Select
Selection.Name = "CostCenters"
End If

' Set CostCenterDescriptions
Range("G4").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
If Selection.Count < 2 Then
Range("G5").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Name = "CCDescriptions"
Else
Range("G5").Select
Selection.Name = "CCDescriptions"
End If

' Set Budget Managers Name
strBudgetManager = Range("C4").Value
MsgBox strBudgetManager
Range("F1").Value = "Budget Monitoring Summary for " &
strBudgetManager
Range("F1").Select
With Selection
.Font.Size = 18
.Font.Bold = True
End With

' Set Monitor Details
Range("F2").Value = strCurrentMonitor & ", " & strCurrentYear
Range("F2:K2").Select
With Selection
.HorizontalAlignment = xlCenter
.MergeCells = True
.Font.Size = 14
.Font.Bold = True
End With

' Set FinancialMonitoringOfficer
Range("E4").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
If Selection.Count < 2 Then
Range("E5").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Name = "FinancialMonitoringOfficer"
Else
Range("E5").Select
Selection.Name = "FinancialMonitoringOfficer"
End If

' Hide uneeded colums
Columns("B:E").Select ' BM LID Column
Selection.EntireColumn.Hidden = True

' Tidy up
Range("F4").Select
Application.DisplayAlerts = True ' Sets notifications back on. This
should be used for any changed Excel settings
End Sub

################################################## #############################

Sub RefreshAllData()
'
' Macro which updates all data (Imported summary and relevant sheets),
and provides a stop check
'

Ans = MsgBox("You are about to update all the data in your Budget
Monitor. Choose OK to continue or Cancel to quit", vbOKCancel, "Last
chance to quit!")

If Ans = vbOK Then
Application.Run "ImportData"
Application.Run "RangeNaming"
Application.Run "AddSheet"
Application.Run "AddDesc"
Application.Run "AddFMO"
Else
End
End If

End Sub

ActiveCell.End(xlDown).Select not working consistently
 

Instead of using Range(Selection,Selection.end(xldown))

use

Range(Selection,Cells(rows.count,ActiveCell.column ).End(xlup))

--
Regards,
Tom Ogilvy

"reclusive monkey" wrote in message
om...
I have some vba set up to import a query from access, go to the top of
a column, and select to the last value. When I run the two Subroutines
seperately, they work fine. However when I call them from another Sub,
the whole column from the top down to the bottom of the sheet is
selected, ruining the next part? Does anyone have any clues as to why
this is happening? Thanks.


################################################## ##########################
###

Sub ImportData()
'
' Macro to import summary data according to LID from MSAccess,
qryBudgetMonitoringDetailSubtotals
'
Dim strBudgetManager As String

On Error Resume Next

' Verify a LID has been set, if not get one
If Range("LID").Value = "" Then
strLID = InputBox("What is your LID?")
Range("LID") = strLID
Else
MsgBox "Your LID is " & Range("LID")
End If

' Select then delete the "Summary" sheet to ensure clean data import
Sheets("Summary").Select
Application.DisplayAlerts = False ' Turns off delete sheet warning
ActiveWindow.SelectedSheets.Delete
Set wsNewWorkSheet = Worksheets.Add(after:=Worksheets(1))
wsNewWorkSheet.Name = "Summary"

' Set LID variable from home page, or by an input box
strLID = Range("LID")
MsgBox "Importing Data from Budget Monitoring"

'Import data from access
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=S:\REVMON\Budget
Monitoring\Maintenance\current.mdb;DefaultDir=S:\R EVMON\Budget
Monitoring\Maintenance;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTi" _
), Array("meout=5;")), Destination:=Range("B4"))
.CommandText = Array( _
"SELECT qryBudgetMonitoringDetailSubtotals.AllocationBM_LI D,
qryBudgetMonitoringDetailSubtotals.`Budget Manager`,
qryBudgetMonitoringDetailSubtotals.AllocationFMO_L ID,
qryBudgetMonitoringDetailSubtotal" _
, _
"s.FMO, qryBudgetMonitoringDetailSubtotals.CostCentre,
qryBudgetMonitoringDetailSubtotals.CostCentreDescr iption,
qryBudgetMonitoringDetailSubtotals.Budget,
qryBudgetMonitoringDetailSubtotals.Spend, qry" _
, _
"BudgetMonitoringDetailSubtotals.Projected,
qryBudgetMonitoringDetailSubtotals.Variance" & Chr(13) & "" & Chr(10)
& "FROM `S:\REVMON\Budget
Monitoring\Maintenance\current.mdb`.qryBudgetMonit oringDetailSubtotals
qryBudgetMonitoringDetailSubtotals" & Chr(13) & "" & Chr(10) & "WHERE
(qryBud" _
, _
"getMonitoringDetailSubtotals.AllocationBM_LID ='" & strLID &
"')" & Chr(13) & "" & Chr(10) & "ORDER BY
qryBudgetMonitoringDetailSubtotals.CostCentre" _
)
.Name = "BudgetSubtotalsImport"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery = False
End With

End Sub


################################################## ##########################
###

Sub RangeNaming()
Sheets("Summary").Select

' Set CostCenters range
Range("F4").Select
Range(Selection, Selection.End(xlDown)).Select
If Selection.Count 2 Then
Range("F5").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Name = "CostCenters"
Else
Range("F5").Select
Selection.Name = "CostCenters"
End If

' Set CostCenterDescriptions
Range("G4").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
If Selection.Count < 2 Then
Range("G5").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Name = "CCDescriptions"
Else
Range("G5").Select
Selection.Name = "CCDescriptions"
End If

' Set Budget Managers Name
strBudgetManager = Range("C4").Value
MsgBox strBudgetManager
Range("F1").Value = "Budget Monitoring Summary for " &
strBudgetManager
Range("F1").Select
With Selection
.Font.Size = 18
.Font.Bold = True
End With

' Set Monitor Details
Range("F2").Value = strCurrentMonitor & ", " & strCurrentYear
Range("F2:K2").Select
With Selection
.HorizontalAlignment = xlCenter
.MergeCells = True
.Font.Size = 14
.Font.Bold = True
End With

' Set FinancialMonitoringOfficer
Range("E4").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
If Selection.Count < 2 Then
Range("E5").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Name = "FinancialMonitoringOfficer"
Else
Range("E5").Select
Selection.Name = "FinancialMonitoringOfficer"
End If

' Hide uneeded colums
Columns("B:E").Select ' BM LID Column
Selection.EntireColumn.Hidden = True

' Tidy up
Range("F4").Select
Application.DisplayAlerts = True ' Sets notifications back on. This
should be used for any changed Excel settings
End Sub


################################################## ##########################
###

Sub RefreshAllData()
'
' Macro which updates all data (Imported summary and relevant sheets),
and provides a stop check
'

Ans = MsgBox("You are about to update all the data in your Budget
Monitor. Choose OK to continue or Cancel to quit", vbOKCancel, "Last
chance to quit!")

If Ans = vbOK Then
Application.Run "ImportData"
Application.Run "RangeNaming"
Application.Run "AddSheet"
Application.Run "AddDesc"
Application.Run "AddFMO"
Else
End
End If

End Sub

ActiveCell.End(xlDown).Select not working consistently
 

"Tom Ogilvy" wrote in message ...
Instead of using Range(Selection,Selection.end(xldown))

use

Range(Selection,Cells(rows.count,ActiveCell.column ).End(xlup))

--
Regards,
Tom Ogilvy

Thanks for the tip Tom, but unfortunately that doesn't work either, it
selects a range above what I want across several columns. I think I
will just run the macros myself for an easy life!