Macro Help

[frantzx]

I have a question regarding macros. I built a macro in the order I want things done in my work book. But for some reason when I run the macro it will do the last part first, then it will run the rest of the macro.I think it may be doing this because the last part of my macro is a quicker process than the rest? I dont know. How do I prevent this? Below is my macro:

Sub test()
'
' test Macro
'

'
Sheets("Adhocs in odd locations").Select
Columns("B:B").Select
Selection.ClearContents
Sheets("Total").Select

ActiveWorkbook.RefreshAll



Sheets("Adhocs in odd locations").Select
Range("A1").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(15, 1)), TrailingMinusNumbers:=True
Range("A3").Select
Selection.TextToColumns Destination:=Range("A3"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(19, 1)), TrailingMinusNumbers:=True
Range("A5").Select
Selection.TextToColumns Destination:=Range("A5"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(19, 1)), TrailingMinusNumbers:=True
Sheets("Total").Select
End Sub



The section that starts with "Sheets("Adhocs in odd locations")." is the part that will load first, I need it to load last.

Thanks in advance for your help.


Brian

 

[SkipVought]

You have "Sheets("Adhocs in odd locations") in there TWICE.

Just a little cleanup...

Sub test()
'
' test Macro
'

'
    Sheets("Adhocs in odd locations").Columns("B:B").ClearContents

    ActiveWorkbook.RefreshAll
       
    with Sheets("Adhocs in odd locations")
	.Range("A1").TextToColumns _
	Destination:=.Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(15, 1)), TrailingMinusNumbers:=True

    	.Range("A3").TextToColumns _
	Destination:=.Range("A3"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(19, 1)), TrailingMinusNumbers:=True

    	.Range("A5").TextToColumns _
	Destination:=.Range("A5"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(19, 1)), TrailingMinusNumbers:=True
    End with

    Sheets("Total").Select
End Sub

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[AnotherHiggins]

for some reason when I run the macro it will do the last part first, then it will run the rest of the macro.I think it may be doing this because the last part of my macro is a quicker process than the rest?

FYI: The macro doesn't look for the part that will run fastest and do that before the rest. It just goes in order.

If you want to see what is happening, press [F8] to step through the macro one line at a time. That might help you see what is happening.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[frantzx]

You have "Sheets("Adhocs in odd locations") in there TWICE.

Yes, sorry what I meant to put was everything after ActiveWorkbook.RefreshAll.

FYI: The macro doesn't look for the part that will run fastest and do that before the rest. It just goes in order.

If you want to see what is happening, press [F8] to step through the macro one line at a time. That might help you see what is happening.

I ran the F8 and seen what all was going on. What happens is that it will refresh the workbook, then do the text to columns, but after the text to columns is done the info I am refreshing will actually load on to the workbook, causing the info I need fom the("Adhocs in odd locations")sheet to load after the text to cloums is performed. When I refresh it is pulling a large amount of info from a external web source which is why I think it is loading after the text to columns is performed. I want my data to load then text to columns fuction to run. Is there a way to pause the macro until the data is loaded then do the text to columns?

 

[SkipVought]

    Sheets("Adhocs in odd locations").Columns("B:B").ClearContents

    [s]ActiveWorkbook.RefreshAll[/c]
       
    with Sheets("Adhocs in odd locations")
       .querytables(1).Refresh BackgroundQuery:=False
' ... and the rest of your code

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[frantzx]

Added that in and now I get:

Run-time error '1004':

This operation cannot be done because the data is refreshing in the background.

 

[SkipVought]

get rid of ActiveWorkbook.RefreshAll


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[frantzx]

If I do that none of my external data will refresh, correct? I need it to.

 

[SkipVought]

how many external querytables do you have?

Why not do the autorefresh in the workbook open event?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[frantzx]

I have 6 different query tables. I will need to leave the workbook open throughout the day and run the macro once an hour to update it. I could do a manual refresh then run the macro, but I wanted a macro that would do all that for me with one click.

 

[SkipVought]

dim qt as querytable, ws as worksheet
for each ws in worksheets
  for each qt in ws.querytables
     qt.refresh background:=false
  next
next

will refresh each and WAIT until each refresh is complete.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}