Macro VBA giving error when running on different server

Question

Hi,

I have a macro file running in Excel 2010 Standard.

The Macro (.xlsm) works perfectly in one server. When it runs on another server, it giving error on the correct codes sometimes.

'Microsoft excel cannot paste the data'.

The Macro seems unstable. It will success when I close and open and maybe run a few more times.  

I have checked the version of .Net Framework. Both are using the same version 4.0.30319.

Anything I can do to get it success?

Answer 1

I think I've caught most of it. You will notice that when I am writing a cell or range reference inside of an With ... End With statement, I prefix Range with a period like .Range. This means that the range definitely belongs to what is defined as the With. Example:

With wkbk.Sheets("Sheets1")

    .Range("A1")   'refers to A1 on Sheet1 within wrkbk

End With

With wkbk.Sheets("Sheets1")

    Range("A1")   'refers to some A1, could be on Sheet1 or somewhere else

End With

You still had a lot of missing worksheet references. Even if you use .Activate to access a workbook, it isn't a good idea to rely on some worksheet being the one with the focus without defining it explicitly.

I don't know what you mean by "**paste values and descriptions". What are descriptions? Comments? Formats?

Private Sub Workbook_Open()

    Dim wkbk As Workbook, wkbk2 As Workbook, wkbk3 As Workbook

    Dim pvtTable1 As PivotTable

    Dim strYrNm As String

    Dim strMthNm As String

    Dim i As Range, src As Range, QueryRange As Range

    Dim X As Long

    Dim BankingName As String, BankingCode As Long

    Application.DisplayAlerts = False

    Set wkbk = Workbooks.Open("D:\Customer.xlsx", False)

    Set wkbk3 = Workbooks.Open("D:\INST.xlsx", False)

    Set QueryRange = wkbk3.Sheets("Sheet1").Range("A1:B500")

    For Each i In wkbk.Sheets("Sheet1").Range("B:AB,AD:AS,AU:BF").Columns

        With wkbk.Sheets("Sheet1")  ' sheet1 assumed, not in your code

            If Not Intersect(i, .Range("B:AB")) Is Nothing Then

                Set src = Union(.Columns("A"), i, .Columns("AC"), .Columns("BH"))

            ElseIf Not Intersect(i, .Range("AD:AS")) Is Nothing Then

                Set src = Union(.Columns("A"), i, .Columns("AT"), .Columns("BH"))

            Else

                Set src = Union(.Columns("A"), i, .Columns("BG"), .Columns("BH"))

            End If

        End With

        Set wkbk2 = Workbooks.Add

        With wkbk2.Sheets("Sheet1")

            .Name = "Test"

            src.Copy Destination:=.Range("A1")

            .Columns("D:D").Cut

            .Columns("B:B").Insert Shift:=xlToRight

            .Columns("D:D").Cut

            .Columns("C:C").Insert Shift:=xlToRight

            wkbk.Range("B4:B6").Copy Destination:=.Range("B4")

            .Range("C8").Cut Destination:=.Range("C7")

            .Range("D9").Cut Destination:=.Range("D7")

            .Range("8:9").Delete Shift:=xlUp

            .Range("D8:D9").Copy Destination:=.Range("B8:C9")

            BankingName = .Range("D7").Value

            BankingCode = Application.VLookup(BankingName, QueryRange, 2, False)

            .Columns.AutoFit

            .Cells(1, 1).Select

            .Parent.SaveAs Filename:="D:" & BankingCode & "_Customer.xlsx", FileFormat:=51

            .Parent.Close True

        End With

    Set src = nothing

   Next i

    Set QueryRange = Nothing

    wkbk3.Close SaveChanges:=False

    Set wkbk3 = Nothing

    wkbk.Close SaveChanges:=False

    Set wkbk = Nothing

    Application.DisplayAlerts = True

End Sub

Edit: there were some copy and paste problems. I believe I've corrected them now.

Answer 2

From your brief description I'm guessing that your macro relies on references like Activesheet and Selection to accomplish the .Paste. You need to modify the macro to definitively reference worksheets and cells by object references and not rely upon whatever is active or selected. Perhaps if you posted the portion of your code that is producing the error and identified the actual line of code that the debug is pointing to some modification(s) could be offered that will improve the procedure.

Answer 3

Hi Y_8765,

In order to facilitate useful help, try posting your code and indicate the run-time error number and the code line which is highlighted when the error is encountered.

===

Regards,

Norman

Answer 4

Here goes some part of the codes. Sometimes it gives error on first .PasteSpecial sometimes on second .PasteSpecial.

There's no runtime error code given when the error occurs.

Basically what the macro does is copy from wkbk and paste into new workbook, wkbk2.

Somehow now the server seems running perfectly without any error.

    wkbk.Activate

    Union(Columns("A:G"), Columns("J:O"), Range(Columns(i), Columns(i + 5))).Select

    Selection.Copy

    Set wkbk2 = Workbooks.Add

    With wkbk2

        Application.DisplayAlerts = False

        wkbk2.Sheets("Sheet1").Range("A1").PasteSpecial

        wkbk.Activate

        Range("B4:B6").Copy

        wkbk2.Activate

        Range("B4:B6").PasteSpecial

Thank you very much.

Answer 5

OK, couple more questions.

• Is wkbk the same as ThisWorkbook (e.g. the one with the code module)?
• what worksheet in wkbk is the source of the Union? Sheet1?
• What parameter is the PasteSpecial? Values? Formats? Formulas? Combination of these?
• When you add a new worksheet to wkbk2 do you want it at the end, beginning or where?
• You are already copying A:G (and more) from wkbk to wkbk2. Why do you copy B4:B6 again?
• Where is i assigned a number?
• Instead of copying entire columns, are you copying a block of data (no empty rows or columns)?

I think it should look something like this,

    Dim i As Long

    Dim ws As Worksheet, wkbk As Workbook, wkbk2 As Workbook

    Set wkbk = ThisWorkbook ' this needs to be set to the right workbook

    Set wkbk2 = Workbooks.Add

    Set ws = wkbk2.Sheets.Add

    i = 0 'some integer to reflect the column number

    With wkbk.Sheets("Sheet1") ' this needs to be set to the right worksheet

        .Union(.Columns("A:G"), .Columns("J:O"), .Range(Columns(i), .Columns(i + 5))).Copy _

          Destination:=ws.Range("A1")

        .Range("B4:B6").Copy Destination:=ws.Range("B4")

    End With

    Set ws = Nothing

    Set wkbk2 = Nothing

    Set wkbk = Nothing

There are a lot of questions to be answered before these references can be made definite but this is a start.