0

A macro that is run inside my main macro generates values that I need to copy to another Excel file.

My main macro does not wait for the generating to finish and pastes empty cells into the other document.

The code works as intended if I step through but not when pressing "run".

Set wbThis = ActiveWorkbook
strName = ActiveSheet.Name
Set wbTarget = Workbooks.Open("D:\Users\user37\Desktop\exportas\Information.xlsm")
wbTarget.Worksheets("Information").Activate
ActiveSheet.Range("B2").Value = myValue
Application.Run ("'Information.xlsm'!ExportData")
wbTarget.Worksheets("SO Lines").Activate
ActiveSheet.Range("E4").Copy

Set wbTarget = Workbooks.Open("D:\Users\user37\Desktop\exportas\Sablonai.xlsm")
wbTarget.Worksheets("Duomenys").Activate
ActiveSheet.Range("A1").Select
activeSheet.PasteSpecial

I found several answers to this question but none of them seem to work for me.

I tried the sleep and wait methods. They stop the whole macro even the one that generates values.

I tried adding DoEvents in the end of the generating macro OR after when I call the generating macro in the main macro. The changes have no effect.

Suggested duplicate Excel waiting for one macro to finish before calling another does not help.

Community
  • 1
  • 1
sarunas2007
  • 11
  • 4
  • Can you give an example of the "several answers to this question" that you've tried and expand on "they didn't work"? – ashleedawg Jun 20 '18 at 08:35
  • 1
    Possible duplicate of [Excel waiting for one macro to finish before calling another](https://stackoverflow.com/questions/1703576/excel-waiting-for-one-macro-to-finish-before-calling-another) – Pᴇʜ Jun 20 '18 at 08:37
  • the Link there did not help... – sarunas2007 Jun 20 '18 at 08:50
  • I already tried the sleep and wait methods they just stop the whole macro even the one that generates values. Also tried adding DoEvents in the end of the generating macro OR after when i call the generating macro in the main macro. the changes have no effect what so ever the code still does not wait for the generating to finish – sarunas2007 Jun 20 '18 at 08:54

2 Answers2

0

Try to avoid Activate and Select due to it's performance issues. When you step through the code it should work as intended as select/activate are able to finish before the next rows are being executed. Haven't tested the code below yet but I hope you understand the principe of With.

Further examples of select could be seen at How to avoid using Select in Excel VBA

Set wbThis = ActiveWorkbook
strName = ActiveSheet.Name
Set wbTarget = 
Workbooks.Open("D:\Users\user37\Desktop\exportas\Information.xlsm")
With wbTarget.Worksheets("Information")
    ActiveSheet.Range("B2").Value = myValue
    Application.Run ("'Information.xlsm'!ExportData")
With wbTarget.Worksheets("SO Lines")
    .Range("E4").Copy
End With

Set wbTarget = Workbooks.Open("D:\Users\user37\Desktop\exportas\Sablonai.xlsm")
With wbTarget.Worksheets("Duomenys")
    With ActiveSheet.Range("A1")
        .PasteSpecial
    End With
End With

Updated:

'In case of multiple columns we create an array
Dim dData(1) as Variant 
Set wbThis = ActiveWorkbook
strName = ActiveSheet.Name
Set wbTarget = 
Workbooks.Open("D:\Users\user37\Desktop\exportas\Information.xlsm")
With wbTarget.Worksheets("Information")
'Copies column 1 and 2
    data(0) = Application.Transpose(Range(Cells(1,1),Cells(1,1).End(xlDown)).Value2)
    data(1) = Application.Transpose(Range(Cells(2,1), Cells(2,1).End(xlDown)).Value2)
End With

'Once again we copy the data from column 1 and 2
With wbTarget.Worksheets("SO Lines")
    data(0) = Application.Transpose(Range(Cells(1,1),Cells(1,1).End(xlDown)).Value2)
    data(1) = Application.Transpose(Range(Cells(2,1),Cells(2,1).End(xlDown)).Value2)
End With
Fredrik
  • 477
  • 4
  • 22
  • Thanks for the answer, but the macro still opens up the file Sablonai.xlsm before the macro finishes generating values – sarunas2007 Jun 20 '18 at 08:47
  • Can you show how the excel document is lined up? Before and after, I have done a program that may solve your problem if I could understand the current result a little more clearly. – Fredrik Jun 20 '18 at 08:52
  • This part of your code is wrong, you use With wbTarget.Worksheets("SO Lines") but then you have this after the With statement: ActiveSheet.Range("E4").Copy, which does NOT necessarily point to wbTarget.Worksheets("SO Lines"). To have it work that way, change to: .Range("E4").Copy – jkpieterse Jun 20 '18 at 08:55
  • do you mean the data generation excel document or the end document? because the end document is just a blank space to paste in – sarunas2007 Jun 20 '18 at 08:57
  • @jkpieterse I thought so, should this be included to activesheet.pasteSpecial too? – Fredrik Jun 20 '18 at 08:59
  • @sarunas2007 The data generation document would give us an insight on how it is structured. Furthermore, how is the generation occur? Do you have to save it to a document or could it be done with an array? – Fredrik Jun 20 '18 at 09:01
  • Yes, remove Activesheet there as well – jkpieterse Jun 20 '18 at 09:05
  • The generation macro works like this: you write in a sales order number (this part of the code is not in the code i provided) and it just exports data from Dynamics AX into different sheets. i just want to copy the data from one sheet SO Lines. Hope that helps this is enough because i cant really provide more... the last two lines that trigger everything in the generation macro are ActiveWorkbook.RefreshAll Application.ScreenUpdating = True – sarunas2007 Jun 20 '18 at 09:06
  • yet again the code you guys provided, with the changes, works like it should when run with pressing F8, but not when just casually running the macro – sarunas2007 Jun 20 '18 at 09:11
  • Okay so you optimize the program a little with ScreenUpdating. I was thinking that you could use `Application.Transpose(Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Value2)`. This will copy one column from the sheet into an array. By doing this you will only need to specify the sheet. If this solves the problem I will update the code above. – Fredrik Jun 20 '18 at 09:13
  • Sorry but i dont really understand where i am suppose to put this line of code. Im new to VBA coding – sarunas2007 Jun 20 '18 at 09:19
  • I can't show my project (due to policy) but will try to update the code above – Fredrik Jun 20 '18 at 09:20
  • @sarunas2007 Updated the code, bit difficult to copy everything so updated a part of it. This may/may not work depending on the format of your file. But it may guide you forward (I hope) – Fredrik Jun 20 '18 at 09:32
  • Same question again, where am i suppose to add the updated code as i see it does not include the start of my export macro. am i suppose to put it after the code you provided earlier? after reading it i dont even understand how does it have to do anything with making my macro wait for another macro to finish... – sarunas2007 Jun 20 '18 at 09:34
  • Dim dData(1) Variant is highlighted in red and says syntax error – sarunas2007 Jun 20 '18 at 09:43
  • I may have misunderstood then, what is wbThis, myValue, StrName. Though you wanted to copy the data but right now it looks like you want to paste it? The variables are declared but not used or not declared and used. Can you explain what you try to do? – Fredrik Jun 20 '18 at 09:48
  • I only posted the code that needs fixing, Wbthis is the current document, MyValue is the sales order number that is pasted into the execusion excel file where the generation macro is. strName isnt even uses anymore. Ill explain the point again. there are 3 excel files. 1 - where the main macro is that you see the code of. This file takes a sales number and puts it in -> file 2. Excel that has the generation macro. the macro generates like 10 sheets worth of different information from the sales order. then i just want to copy one sheet and paste it into-> empty excel 3 file – sarunas2007 Jun 20 '18 at 09:57
0

the Issue was solved by going to the generation macro -> data -> connections and disabling backround refresh for every query

sarunas2007
  • 11
  • 4