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!

 

[Norie]

It really would help if you told us a bit more about the code.

There's a lot going on in just that short piece you've posted - there are at least 3 workbooks involved and 2 being opened.

There's also no hardly any workbook/worksheet references and a lot of unneeded Activate/Select going on.

That's makes it hard to follow what's going on in what worksheet/workbook.

 

[xenou]

I have never experienced such a problem. I once fixed an unexplainable problem rebuilding the workbook. Thing is, I doubt anyone will be able to tell you why your code jumps around like this - it's not how VBA is supposed to work. Most likely, you will have to try your own experiments at cleaning up the code and/or the workbook until its worked itself out. As a matter of habit, however, I frequently do rebuild projects that have a lot of development behind them (i.e., to create a clean "final" product) - though I understand why this is sometimes undesirable when it's a lot of work to do.

 

[daverunt]

Is all the code there?

This doesn't appear to work for me.
Code fails line 3 because the ActiveCell is A65535 at this point.

Range("A6000").Select  
[COLOR=black]Selection.End(xlDown).Select  code. [/COLOR][COLOR=blue]-- activecell  A65535[/COLOR]
ActiveCell.Range("A1:B100").Select Selection.Copy

 

[jamieboss]

Everyone: Thank you sincerely for you input. I run a small business and am basically self taught on VBA. This forum has been my savior many times!

Dave Runt, you are my hero! The workbook named 'masterlist' did not have the correct preparation in it so the code was indeed jumping all the way to the bottom of the worksheet. I am not sure why that made the code jump rather than make an error?

Norie: What is my alternative to all the unnecessary 'active/ select' which you correctly point out? This is a case of me writing a macro like they are recorded rather than the most efficient way. What would be more efficient syntax?

Thanks again everyone!

 

[daverunt]

As Norrie said you have unnecessary Select & Activates in the code.
Here's an example on cutting it out.

You can do a search for 'copying and pasting from one workbook to another' to find examples.

Set WB_Masterlist = ActiveWorkbook
Range("A1:Q11").[COLOR=red]Select[/COLOR]
[COLOR=red]Selection.[/COLOR]Copy
WB_Saru_count2[COLOR=red].Activate[/COLOR]
Sheets("Mast").[COLOR=red]Select[/COLOR]
Range("A1").[COLOR=red]Select[/COLOR]
[COLOR=red]ActiveSheet.Paste[/COLOR]
 
with:
Set WB_Masterlist = ActiveWorkbook 
Range("A1:Q11").Copy [COLOR=blue]Destination:=[/COLOR]WB_Saru_count2.Sheets("Mast").Range("A1")