Auxie
10-18-2017, 07:35 AM
2016 user - macros have been working fine for the last couple months, newb coder.
So I've macros, while basic, have been working fine until today. I've included a snippet of my code below -
Dim wb1 As Workbook, wb2 As WorkbookSet wb1 = ThisWorkbook
'Clear sheets
Worksheets("RFIs").Select
Rows("2:" & Rows.Count).ClearContents
Worksheets("TQs").Select
Rows("2:" & Rows.Count).ClearContents
Worksheets("EWNs").Select
Rows("2:" & Rows.Count).ClearContents
Worksheets("CEs").Select
Rows("2:" & Rows.Count).ClearContents
Worksheets("PMIs").Select
Rows("2:" & Rows.Count).ClearContents
'Framework
Set wb2 = Workbooks.Open("Path", UpdateLinks:=0)
wb2.Worksheets("TQ Register").Range("A9:L100").Copy
wb1.Worksheets("TQs").Activate
Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wb2.Worksheets("RFI Register").Range("A9:K100").Copy
wb1.Worksheets("RFIs").Activate
Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wb2.Worksheets("EWN").Range("A9:L100").Copy
wb1.Worksheets("EWNs").Activate
Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wb2.Worksheets("CE").Range("A10:N100").Copy
wb1.Worksheets("CEs").Activate
Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wb2.Worksheets("PMI").Range("A8:M100").Copy
wb1.Worksheets("PMIs").Activate
Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
wb2.Close SaveChange = True
Application.DisplayAlerts = False
The code below the clear sheets is then duplicated a number of times for different spreadsheets but follows the same copy/paste code.
anyways, the debugger is flagging this line of code the first line of code but when I remove them it then jumps to this line
wb1.Worksheets("TQs").Activate
Pathways and sheet names are correct however they aren't running. However when i debug further it will actually pull the data as normal.
A little more information - the macros themselves are saved in a module and I have a Userform (containing a description and a button to start them), the buttons code is simply
Module1.pulldata
I assume I'm going to have to completely rewrite this?
So I've macros, while basic, have been working fine until today. I've included a snippet of my code below -
Dim wb1 As Workbook, wb2 As WorkbookSet wb1 = ThisWorkbook
'Clear sheets
Worksheets("RFIs").Select
Rows("2:" & Rows.Count).ClearContents
Worksheets("TQs").Select
Rows("2:" & Rows.Count).ClearContents
Worksheets("EWNs").Select
Rows("2:" & Rows.Count).ClearContents
Worksheets("CEs").Select
Rows("2:" & Rows.Count).ClearContents
Worksheets("PMIs").Select
Rows("2:" & Rows.Count).ClearContents
'Framework
Set wb2 = Workbooks.Open("Path", UpdateLinks:=0)
wb2.Worksheets("TQ Register").Range("A9:L100").Copy
wb1.Worksheets("TQs").Activate
Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wb2.Worksheets("RFI Register").Range("A9:K100").Copy
wb1.Worksheets("RFIs").Activate
Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wb2.Worksheets("EWN").Range("A9:L100").Copy
wb1.Worksheets("EWNs").Activate
Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wb2.Worksheets("CE").Range("A10:N100").Copy
wb1.Worksheets("CEs").Activate
Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wb2.Worksheets("PMI").Range("A8:M100").Copy
wb1.Worksheets("PMIs").Activate
Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
wb2.Close SaveChange = True
Application.DisplayAlerts = False
The code below the clear sheets is then duplicated a number of times for different spreadsheets but follows the same copy/paste code.
anyways, the debugger is flagging this line of code the first line of code but when I remove them it then jumps to this line
wb1.Worksheets("TQs").Activate
Pathways and sheet names are correct however they aren't running. However when i debug further it will actually pull the data as normal.
A little more information - the macros themselves are saved in a module and I have a Userform (containing a description and a button to start them), the buttons code is simply
Module1.pulldata
I assume I'm going to have to completely rewrite this?