0

I have an excel macro that is running correctly on over 10 pc's. On 1 pc it does not run correctly. It does not error out, just seems to not run to completion. All machines are running windows 10. I have included the code below. Fairly simple. Copies from "Follow_Up" sheet in the source file to "Sheet1" in the destination file. It seems to stop just after it activates "Sheet1"

    Sub copydata1()
Dim wbSrc As Workbook, wbDest As Workbook
Dim rngSrc As Range
Dim j As Integer
Dim i As Integer

Set wbSrc = ActiveWorkbook
Sheets("Follow_Up").Activate


Workbooks.Open "G:\Estimating Templates\Bid List_Lee.xlsm"
Set wbDest = ActiveWorkbook
Sheets("Sheet1").Activate
 -----seems to stop here---------

 Dim unusedRow As Long
unusedRow = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row



 j = ActiveCell.Row
j = unusedRow
  MsgBox j

wbSrc.Activate

 Sheets("Follow_Up").Select
  Range("L151").Select
    ActiveCell.FormulaR1C1 = "=CELL(""filename"")"
    Range("M151").Select

    Range("A151:Q151").Select
    Selection.Copy
   wbDest.Activate

    Sheet1.Cells(j, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Sheet1.Cells(j, 1) = Sheet1.Cells(j, 14)

            Sheet1.Cells(j, 17).Select
    ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[-1])"

      ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 1
    Range("A20").Select


    wbSrc.Activate
    Sheets("Follow_Up").Select
    Range("L151").Select
    ActiveCell.FormulaR1C1 = "=CELL(""filename"")"
    Range("M151").Select

     wbSrc.Activate
      Sheets("Summary").Select
End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
Mem_FBI
  • 1
  • 1
  • 1
    Probably good to read [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and incorporate its points into your code. – BigBen Jun 08 '20 at 17:04
  • 1
    To add to what @BigBen says, it is probably a waste of time to try to debug the code rather than rewriting it from the ground up without things like `.Activate` or `.Select`. That will probably solve the issue. The code is short, so rewriting it shouldn't take very long. – John Coleman Jun 08 '20 at 17:13
  • I agree with the previous comments, but I also have an idea: the newly opened workbook could not be activated so "sheet1" may not be available to activate. Set wbDest =Workbooks.Open (FileName:="G:\Estimating Templates\Bid List_Lee.xlsm") then use wdDest.activate. After it you can activate the sheet. – Viktor West Jun 08 '20 at 17:47
  • Where is the code located? In `wbSrc` or in a 3rd file? – VBasic2008 Jun 08 '20 at 17:51
  • The code is located in wbDest. – Mem_FBI Jun 08 '20 at 19:01
  • I'm talking about the code you have posted. It can be in `wbSrc`, a workbook containing `Sheet1` (CodeName) or another workbook. `wbDest` is definitely `Bid List_Lee.xlsm` – VBasic2008 Jun 08 '20 at 19:13
  • The code is in wbDest(Bid List_Lee.xlsm) It is run from the excel tool bar. Each user has their own "Bid List" – Mem_FBI Jun 08 '20 at 20:38
  • Basically, the purpose of this is to copy data from a "Follow_up" sheet in the workbook that the users have open, and copy it to their "Bid List" file. It is run by clicking an icon on the excel toolbar – Mem_FBI Jun 08 '20 at 20:44
  • The article took care of it! Thank you all for your help. – Mem_FBI Jun 08 '20 at 21:04

0 Answers0