VBA line doesn't execute as expected then jumps backward in the code?

[jamieboss]

Hello All,

I'm trying to have a excel execute a simple procedure to copy and paste some cell ranges from one workbook to another. The code runs correctly up to a point but then doesn't execute a line as expected and then randomly jumps back 10 or so lines in the code (rather than continuing line by line as I would expect?). The relevant pieces of the code are below:

Dim WB_Masterlist As Workbook
Dim WB_Source2 As Workbook
......

'4.Prompts user to select the source file from dropdowns
'Assigns the name WB_Source to the file the user selects
Filt = "Excel Files (*.xls),*.xls"
Title = "Please select the Source File"
WB_Source = Application.GetOpenFilename _
(FileFilter:=Filt, _
Title:=Title)

'5.Opens the source workbook
Workbooks.Open Filename:=WB_Source

Set WB_Source2 = ActiveWorkbook

..........

'13.Enter End Date into cell C6
Range("C6").Value = EndDate

'14.Sets the variable names that are used to save the worksheet with the correct name
Barname = WB_Source2.Sheets("Set").Range("B4").Value
Startdate = WB_Saru_count2.Sheets("Dates").Range("C5").Text
EndingDate = WB_Saru_count2.Sheets("Dates").Range("C6").Text

'15.Load the masterlist

ChDir "C:\Bar-i\Tools"
Workbooks.Open Filename:= _
"C:\Bar-i\Clients\DropBox\Bar-i Masterlist.xls"
Set WB_Masterlist = ActiveWorkbook
Range("A1:Q5300").Select
Application.CutCopyMode = False
Selection.Copy

WB_Saru_count2.Activate
Sheets("Mast").Select
Range("A1").Select
ActiveSheet.Paste

WB_Masterlist.Activate
Sheets("Sheet1").Select
Range("A6000").Select

'The previous line to this one does not execute?

Selection.End(xlDown).Select
ActiveCell.Range("A1:B100").Select
Selection.Copy

'After this line it randomly jumps up to the line:
'13.Enter End Date into cell C6
Range("C6").Value = EndDate


I'm stuck! Any help is greatly appreciated!

 

[Andrew Poulsom]

Where is that code? Have you tried running CodeCleaner on your workbook?

http://www.appspro.com/Utilities/CodeCleaner.htm

 

[jamieboss]

Andrew,

Thanks for your quick reply! The code is stored as a module in the workbook in question. I downloaded the code cleaner and successfully ran it but it didn't fix the problem?

Any other suggestions/ thoughts?

Thank you!

 

[Norie]

I'm afraid it's not clear where in the code this happens.

Which workbook is the 'workbook in question'?

 

[jamieboss]

Sorry if this wasn't clear:

The line highlighted in Red is the line which doesn't execute
The line highlighted in Orange is the line after which it 'jumps' up a few lines
The line highlighted in Green is the line it jumps to

Dim WB_Masterlist As Workbook
Dim WB_Source2 As Workbook
......

'4.Prompts user to select the source file from dropdowns
'Assigns the name WB_Source to the file the user selects
Filt = "Excel Files (*.xls),*.xls"
Title = "Please select the Source File"
WB_Source = Application.GetOpenFilename _
(FileFilter:=Filt, _
Title:=Title)

'5.Opens the source workbook
Workbooks.Open Filename:=WB_Source

Set WB_Source2 = ActiveWorkbook

..........

'13.Enter End Date into cell C6
Range("C6").Value = EndDate

'14.Sets the variable names that are used to save the worksheet with the correct name
Barname = WB_Source2.Sheets("Set").Range("B4").Value
Startdate = WB_Saru_count2.Sheets("Dates").Range("C5").Text
EndingDate = WB_Saru_count2.Sheets("Dates").Range("C6").Text

'15.Load the masterlist

ChDir "C:\Bar-i\Tools"
Workbooks.Open Filename:= _
"C:\Bar-i\Clients\DropBox\Bar-i Masterlist.xls"
Set WB_Masterlist = ActiveWorkbook
Range("A1:Q5300").Select
Application.CutCopyMode = False
Selection.Copy

WB_Saru_count2.Activate
Sheets("Mast").Select
Range("A1").Select
ActiveSheet.Paste

WB_Masterlist.Activate
Sheets("Sheet1").Select
Range("A6000").Select

'The previous line to this one does not execute?

Selection.End(xlDown).Select
ActiveCell.Range("A1:B100").Select
Selection.Copy

'After this line it randomly jumps up to the line:
'13.Enter End Date into cell C6
Range("C6").Value = EndDate

 

[Norie]

Have you tried stepping through the code with F8 to see what's actually happening?

Or have you done that already and observed the code 'jumping' as you describe?

 

[jamieboss]

Exactly. I step through it with F8 and it works fine up to that red line then seems to ignore a couple of lines, then 'jump' backwards in the code?

 

[Norie]

I notice in the code there's a reference to what I think is a workbook called WB_Saru_count2.

Is this reference set somewhere else?

Is there other code?

How many workbooks are involved?

 

[xenou]

I would rebuild the workbook:

Copy sheet data (not whole sheets) into a new book
Copy code as text into new modules
Recompile

In short, there's no real cause for such inexplicable behavior that I can think of - just something gone haywire.

 

[jamieboss]

Xenou: Have you experienced and fixed similar problems using that method?

I can try that but we're talking about a 7MB excel file with many sheets. That would be quite a lot of work. Perhaps the size of the workbook is part of the problem? Does anyone else have any other suggestions?