1

I am having a problem with the Name Manager (or so I think). I have created two ActiveX Command Buttons, Import Data and Clear Data, respectively. I have also created a set of named ranges using =Offset, which is then used to create a plot of the ranges. Everything works the first time, however, when I clear the data and re-import, the named range reference changes to the neighboring tab (left by one).

I have constructed a similar macro workbook before (Import/Clear/Re-Import), and I did not encounter this issue. I have attempted to recreate the entire workbook several times, to no avail.

I do not know where the problem is stemming from.

CommandButton1

Private Sub CommandButton1_Click()
'ImportDataFile Macro
'Button Caption : Import File

'Supress Active Screen Updating
Application.ScreenUpdating = False

Dim Ret

'Import Data File
Ret = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    If Ret <> False Then
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & Ret, Destination:=Range("$A$1"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    End If

'Move Header Info
Worksheets("Data_Summary").Range("1:27").Cut Worksheets("Header_Info").Range("A3") 'Cut and Paste

'Retrieve Filename
Worksheets("Header_Info").Range("A1") = Ret
Worksheets("Header_Info").Range("A1:F1").Interior.Color = RGB(255, 255, 0)

'Remove blank rows & clean up
Worksheets("Data_Summary").Select
Rows("1:27").Select
Selection.Delete Shift:=xlUp
Columns("A:A").ColumnWidth = 6.71
Columns("B:B").ColumnWidth = 10.57
Rows("6:6").Select
Selection.Delete Shift:=xlUp
ActiveWindow.FreezePanes = True
Range("A1").Select

'Format Time Column (doesn't work right!)
Worksheets("Data_Summary").Range(Cells(6, 2), Cells(6, 2).End(xlDown)).NumberFormat = "hh:mm:ss:ss"

'Disable the CommandButton after use
CommandButton1.Enabled = False

'Allow Screen Update
Application.ScreenUpdating = True

End Sub

CommandButton2

Private Sub CommandButton2_Click()
'Delete column contents
'Button Caption: Clear Data

'Supress Active Screen Updating
Application.ScreenUpdating = False

'Deleting the Header Info
Worksheets("Header_Info").Range("A:A").ClearContents
Worksheets("Header_Info").Range("B:B").ClearContents

'Unfreeze the panes
ActiveWindow.FreezePanes = False

'Deleting the Data Summary
ActiveSheet.Range("A1:A" & Range("A6").End(xlDown).Row).ClearContents
ActiveSheet.Range("B1:B" & Range("B6").End(xlDown).Row).ClearContents
ActiveSheet.Range("C1:C" & Range("C6").End(xlDown).Row).ClearContents
ActiveSheet.Range("D1:D" & Range("D6").End(xlDown).Row).ClearContents
ActiveSheet.Range("E1:E" & Range("E6").End(xlDown).Row).ClearContents
ActiveSheet.Range("F1:F" & Range("F6").End(xlDown).Row).ClearContents
ActiveSheet.Range("G1:G" & Range("G6").End(xlDown).Row).ClearContents
ActiveSheet.Range("H1:H" & Range("H6").End(xlDown).Row).ClearContents
ActiveSheet.Range("I1:I" & Range("I6").End(xlDown).Row).ClearContents
ActiveSheet.Range("J1:J" & Range("J6").End(xlDown).Row).ClearContents
ActiveSheet.Range("K1:K" & Range("K6").End(xlDown).Row).ClearContents
ActiveSheet.Range("L1:L" & Range("L6").End(xlDown).Row).ClearContents
ActiveSheet.Range("M1:M" & Range("M6").End(xlDown).Row).ClearContents
ActiveSheet.Range("N1:N" & Range("N6").End(xlDown).Row).ClearContents
ActiveSheet.Range("O1:O" & Range("O6").End(xlDown).Row).ClearContents
ActiveSheet.Range("P1:P" & Range("O6").End(xlDown).Row).ClearContents 'due to blanks, references neighboring column

'Clear Formatting
ActiveSheet.Range("A1:A" & Range("A6").End(xlDown).Row).ClearFormats

'Enable the CommandButton after data is cleared
CommandButton1.Enabled = True

'Allow Screen Update
Application.ScreenUpdating = True

End Sub

Named Ranges

CycNum

=OFFSET($C$6,0,0,MAX(1,COUNT($C:$C)),1)

SysP

=OFFSET($G$6,0,0,MAX(1,COUNT($G:$G)),1)
Cody W.
  • 23
  • 6
  • You should read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba Which sheet are your named ranges on? – SJR Jun 02 '20 at 19:31
  • My named ranges are on Sheet2(Data_Summary), with Sheet1(Header_Info) order in workbook: [Header_Info] [Data_Summary] [etc.] Thanks for the reading tip! – Cody W. Jun 02 '20 at 19:41
  • Make sure you use sheet names rather than `activesheet` as that will help to spot errors. You are cutting and pasting from that sheet - could that be the cause? Have you tried stepping through the code? – SJR Jun 02 '20 at 20:02
  • After considerable debugging and testing, I found that the issue was happening when I cut the header information and pasted it in a different sheet. Apparently the absolute reference in the named range was moving with the header. This is perhaps an error on my knowledge of how named ranges work. Any additional comments would be met with gratitude. – Cody W. Jun 03 '20 at 16:29

0 Answers0