0

I use a template sheet with a macro that opens a selected text file, copies and pastes the information into an Excel sheet tab, closes the text file. Repeating 8 times (8 total text files) then performs a text to column as well as a pivot refresh. While performing the action of opening the file then copying and pasting the information, I will sometimes receive the error in the title. It is not every time but it is fairly often. Below is the code being used in VBA.

Sub FormatTemplate()

    ActiveSheet.Shapes.Range(Array("Button 1")).Select
    Selection.Delete
    Range("A1").Select

    Dim my_FileName As Variant
    my_FileName = Application.GetOpenFilename
    If my_FileName <> False Then
        Workbooks.Open Filename:=my_FileName
    End If
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.Close
    Windows("Template.xlsm").Activate
    Sheets("1").Select
    ActiveSheet.Paste
    
    my_FileName = Application.GetOpenFilename
    If my_FileName <> False Then
        Workbooks.Open Filename:=my_FileName
    End If
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.Close
    Windows("Template.xlsm").Activate
    Sheets("2").Select
    ActiveSheet.Paste
    
    my_FileName = Application.GetOpenFilename
    If my_FileName <> False Then
        Workbooks.Open Filename:=my_FileName
    End If
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.Close
    Windows("Template.xlsm").Activate
    Sheets("3").Select
    ActiveSheet.Paste
    
    my_FileName = Application.GetOpenFilename
    If my_FileName <> False Then
        Workbooks.Open Filename:=my_FileName
    End If
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.Close
    Windows("Template.xlsm").Activate
    Sheets("4").Select
    ActiveSheet.Paste

    my_FileName = Application.GetOpenFilename
    If my_FileName <> False Then
        Workbooks.Open Filename:=my_FileName
    End If
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.Close
    Windows("Template.xlsm").Activate
    Sheets("5").Select
    ActiveSheet.Paste

    my_FileName = Application.GetOpenFilename
    If my_FileName <> False Then
        Workbooks.Open Filename:=my_FileName
    End If
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.Close
    Windows("Template.xlsm").Activate
    Sheets("6").Select
    ActiveSheet.Paste

    my_FileName = Application.GetOpenFilename
    If my_FileName <> False Then
        Workbooks.Open Filename:=my_FileName
    End If
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.Close
    Windows("Template.xlsm").Activate
    Sheets("7").Select
    ActiveSheet.Paste
    
    my_FileName = Application.GetOpenFilename
    If my_FileName <> False Then
        Workbooks.Open Filename:=my_FileName
    End If
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.Close
    Windows("Template.xlsm").Activate
    Sheets("8").Select
    ActiveSheet.Paste

    Sheets("1").Select
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(16, 1), Array(21, 1), Array(26, 1), Array(34, 1), _
        Array(52, 1), Array(57, 1), Array(69, 1), Array(73, 1), Array(82, 1), Array(86, 1), Array( _
        91, 1), Array(97, 1), Array(98, 1), Array(100, 1), Array(110, 1), Array(115, 1), Array(117, _
        1), Array(119, 1), Array(121, 1), Array(133, 1), Array(141, 1), Array(148, 1), Array(154, 1 _
        ), Array(163, 1)), TrailingMinusNumbers:=True
    Range("A7:W7").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$7:$Y$5000").AutoFilter Field:=21, Criteria1:="=ZX-101" _
        , Operator:=xlAnd
    ActiveSheet.Range("$A$7:$Y$5000").AutoFilter Field:=1, Criteria1:="=4*", _
        Operator:=xlAnd
    Columns("I:I").Select
    Selection.Style = "Comma"
    Range("A7").Select
    
    Sheets("2").Select
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(16, 1), Array(21, 1), Array(26, 1), Array(34, 1), _
        Array(52, 1), Array(57, 1), Array(69, 1), Array(73, 1), Array(82, 1), Array(86, 1), Array( _
        91, 1), Array(97, 1), Array(98, 1), Array(100, 1), Array(110, 1), Array(115, 1), Array(117, _
        1), Array(119, 1), Array(121, 1), Array(133, 1), Array(141, 1), Array(148, 1), Array(154, 1 _
        ), Array(163, 1)), TrailingMinusNumbers:=True
    Range("A7:W7").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$7:$Y$5000").AutoFilter Field:=21, Criteria1:="=ZX-101" _
        , Operator:=xlAnd
    ActiveSheet.Range("$A$7:$Y$5000").AutoFilter Field:=1, Criteria1:="=4*", _
        Operator:=xlAnd
    Columns("I:I").Select
    Selection.Style = "Comma"
    Range("A7").Select
    
    Sheets("3").Select
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(16, 1), Array(21, 1), Array(26, 1), Array(34, 1), _
        Array(52, 1), Array(57, 1), Array(69, 1), Array(73, 1), Array(82, 1), Array(86, 1), Array( _
        91, 1), Array(97, 1), Array(98, 1), Array(100, 1), Array(110, 1), Array(115, 1), Array(117, _
        1), Array(119, 1), Array(121, 1), Array(133, 1), Array(141, 1), Array(148, 1), Array(154, 1 _
        ), Array(163, 1)), TrailingMinusNumbers:=True
    Range("A7:W7").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$7:$Y$5000").AutoFilter Field:=21, Criteria1:="=ZX-101" _
        , Operator:=xlAnd
    ActiveSheet.Range("$A$7:$Y$5000").AutoFilter Field:=1, Criteria1:="=4*", _
        Operator:=xlAnd
    Columns("I:I").Select
    Selection.Style = "Comma"
    Range("A7").Select
    
    Sheets("4").Select
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(16, 1), Array(21, 1), Array(26, 1), Array(34, 1), _
        Array(52, 1), Array(57, 1), Array(69, 1), Array(73, 1), Array(82, 1), Array(86, 1), Array( _
        91, 1), Array(97, 1), Array(98, 1), Array(100, 1), Array(110, 1), Array(115, 1), Array(117, _
        1), Array(119, 1), Array(121, 1), Array(133, 1), Array(141, 1), Array(148, 1), Array(154, 1 _
        ), Array(163, 1)), TrailingMinusNumbers:=True
    Range("A7:W7").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$7:$Y$5000").AutoFilter Field:=21, Criteria1:="=ZX-101" _
        , Operator:=xlAnd
    ActiveSheet.Range("$A$7:$Y$5000").AutoFilter Field:=1, Criteria1:="=4*", _
        Operator:=xlAnd
    Columns("I:I").Select
    Selection.Style = "Comma"
    Range("A7").Select
    
    Sheets("5").Select
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(16, 1), Array(21, 1), Array(26, 1), Array(34, 1), _
        Array(52, 1), Array(57, 1), Array(69, 1), Array(73, 1), Array(82, 1), Array(86, 1), Array( _
        91, 1), Array(97, 1), Array(98, 1), Array(100, 1), Array(110, 1), Array(115, 1), Array(117, _
        1), Array(119, 1), Array(121, 1), Array(133, 1), Array(141, 1), Array(148, 1), Array(154, 1 _
        ), Array(163, 1)), TrailingMinusNumbers:=True
    Range("A7:W7").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$7:$Y$5000").AutoFilter Field:=21, Criteria1:="=ZX-101" _
        , Operator:=xlAnd
    ActiveSheet.Range("$A$7:$Y$5000").AutoFilter Field:=1, Criteria1:="=4*", _
        Operator:=xlAnd
    Columns("I:I").Select
    Selection.Style = "Comma"
    Range("A7").Select
    
    Sheets("6").Select
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(16, 1), Array(21, 1), Array(26, 1), Array(34, 1), _
        Array(52, 1), Array(57, 1), Array(69, 1), Array(73, 1), Array(82, 1), Array(86, 1), Array( _
        91, 1), Array(97, 1), Array(98, 1), Array(100, 1), Array(110, 1), Array(115, 1), Array(117, _
        1), Array(119, 1), Array(121, 1), Array(133, 1), Array(141, 1), Array(148, 1), Array(154, 1 _
        ), Array(163, 1)), TrailingMinusNumbers:=True
    Range("A7:W7").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$7:$Y$5000").AutoFilter Field:=21, Criteria1:="=ZX-101" _
        , Operator:=xlAnd
    ActiveSheet.Range("$A$7:$Y$5000").AutoFilter Field:=1, Criteria1:="=4*", _
        Operator:=xlAnd
    Columns("I:I").Select
    Selection.Style = "Comma"
    Range("A7").Select
    
    Sheets("7").Select
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(16, 1), Array(21, 1), Array(26, 1), Array(34, 1), _
        Array(52, 1), Array(57, 1), Array(69, 1), Array(73, 1), Array(82, 1), Array(86, 1), Array( _
        91, 1), Array(97, 1), Array(98, 1), Array(100, 1), Array(110, 1), Array(115, 1), Array(117, _
        1), Array(119, 1), Array(121, 1), Array(133, 1), Array(141, 1), Array(148, 1), Array(154, 1 _
        ), Array(163, 1)), TrailingMinusNumbers:=True
    Range("A7:W7").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$7:$Y$5000").AutoFilter Field:=21, Criteria1:="=ZX-101" _
        , Operator:=xlAnd
    ActiveSheet.Range("$A$7:$Y$5000").AutoFilter Field:=1, Criteria1:="=4*", _
        Operator:=xlAnd
    Columns("I:I").Select
    Selection.Style = "Comma"
    Range("A7").Select
    
    Sheets("8").Select
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(16, 1), Array(21, 1), Array(26, 1), Array(34, 1), _
        Array(52, 1), Array(57, 1), Array(69, 1), Array(73, 1), Array(82, 1), Array(86, 1), Array( _
        91, 1), Array(97, 1), Array(98, 1), Array(100, 1), Array(110, 1), Array(115, 1), Array(117, _
        1), Array(119, 1), Array(121, 1), Array(133, 1), Array(141, 1), Array(148, 1), Array(154, 1 _
        ), Array(163, 1)), TrailingMinusNumbers:=True
    Range("A7:W7").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$7:$Y$5000").AutoFilter Field:=21, Criteria1:="=ZX-101" _
        , Operator:=xlAnd
    ActiveSheet.Range("$A$7:$Y$5000").AutoFilter Field:=1, Criteria1:="=4*", _
        Operator:=xlAnd
    Columns("I:I").Select
    Selection.Style = "Comma"
    Range("A7").Select
    
    Sheets("1").Select
    Range("A7").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets("All PRIN").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("2").Select
    Range("A7").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("All PRIN").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
       Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("3").Select
    Range("A7").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("All PRIN").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("4").Select
    Range("A7").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("All PRIN").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("5").Select
    Range("A7").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("All PRIN").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("6").Select
    Range("A7").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("All PRIN").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
       Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("7").Select
    Range("A7").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("All PRIN").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("8").Select
    Range("A7").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("All PRIN").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AA1").Select
    Application.CutCopyMode = False
    ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh

End Sub

Hitting debug highlights one of the ActiveSheet.Paste lines

I have attempted different ways of copying and pasting, as well as changing how the macro goes about opening and closing the text files. Looked up and applied different answers that I found through StackOverflow and none seemed to resolve the issue.

JohnM
  • 2,422
  • 2
  • 8
  • 20
Zyranos
  • 1
  • 2
  • Have you tried splitting the copy/paste? Here https://stackoverflow.com/a/76794690/18247317 how I managed to have it in Excel (look at the Update) – Oran G. Utan Aug 10 '23 at 16:53
  • Might or might not be the cause of your problem, but you might want to see [this SO question](https://stackoverflow.com/q/10714251/11318818) particularly section starting "A common (bad) piece of code is to open a book, get some data then close again". Also you might try manually copying and pasting from / to the same worksheets to see what problem(s) you experience? – JohnM Aug 10 '23 at 16:55
  • Would recommend reviewing this post and applying the guidelines outlined there. It will reduce the size of your code and make it more robust. https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Tim Williams Aug 10 '23 at 17:35
  • Also you can copy values between ranges without using copy/paste - plenty of examples here on SO. – Tim Williams Aug 10 '23 at 17:48

0 Answers0