Excel VBA to another workbook

Question

I have an excel document with 3 tabs (Report, MI & CSR Data Dump).

When the report tab is complete and "submit" button pushed. The current VBA writes to the data dump sheet perfectly.

However I want to move the data dump into a separate workbook. I have tried and failed. Location:- K:\Call Quality\Quality MI\Quality MI.XLSM This will be the same worksheet name "Data Dump"

My current VBA which works perfectly is

Private Sub generate_report()

Dim i As Long
Dim fullcount As Long
Dim ws As Worksheet
Application.ScreenUpdating = False

Set ws = Worksheets("CSR Data Dump")

With ws

fullcount = Excel.WorksheetFunction.CountA(.Range("A:A"))
i = fullcount + 1


    Range("XER2").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("a" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False

    Range("XER5").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("b" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False

    Range("XER3:xer4").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("c" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False

     Range("XER6:xer7").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("e" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False

    Range("d11:d17").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("g" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False

    Range("d19").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("o" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False

    Range("e19:e22").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("s" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False

    Range("d23").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("w" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False

    Range("e23:e28").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("aa" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False

    Range("d29").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("ag" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False

    Range("e29:e33").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("ak" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False

    Range("d34").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("ap" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("e34:e37").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("at" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("d38").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("ax" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("e38:e39").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("az" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("d40").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("bb" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("E40:E42").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("Be" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("d44").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("bh" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("E44:e46").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("Bk" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("d47").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("bn" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("E47:e54").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("Bv" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("d55").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("cd" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("E55:e56").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("ch" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("d58").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("cl" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("E60:e63").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("cp" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("h65:h67").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("ct" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("j11").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("cw" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("j19").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("cx" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("j44").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("cy" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("j60").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("cz" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select

    Range("j65").Select
    Selection.Copy
    Sheets("CSR Data dump").Select
    Range("da" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Sheets("CM Form").Select
    Application.CutCopyMode = False
    Range("j64").Select


End With

Application.ScreenUpdating = True

End Sub

Answer 1

You are refering to ws, but then you are not using this With reference anywhere. You have to refer to it with a point, like this:

With ws

fullcount = WorksheetFunction.CountA(.Range("A:A"))
i = fullcount + 1


    .Range("XER2").Select
    Selection.Copy
    .Sheets("CSR Data dump").Select
    .Range("a" & i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    .Sheets("CM Form").Select
    Application.CutCopyMode = False

Concerning the ws, if it stays in another workbook, then it should be refered like this:

Set wbk = Workbooks.Open("K:\Call Quality\Quality MI\Quality MI.XLSM")
Set ws = wbk.Worksheets("CSR Data dump")

How do I reference to another (open or closed) workbook, and pull values back, in VBA? - Excel 2007

Answer 2

The below should take care of pasting into a different workbook

Private Sub generate_report()

Dim i As Long
Dim fullcount As Long
Dim ws As Worksheet
Dim wbk as Workbook
Dim srcWbk as Workbook

Application.ScreenUpdating = False

Set srcWbk = ActiveWorkbook
Set wbk = Workbooks.Open("K:\Call Quality\Quality MI\Quality MI.XLSM")
Set ws = wbk.Worksheets("CSR Data dump")
srcWbk.Activate
'You are not using With appropriately so let's drop this till you learn how to use it.
'With ws

fullcount = Excel.WorksheetFunction.CountA(ws.Range("A:A"))
i = fullcount + 1

    'Do this only once
    Sheets("CM Form").Select    
    ws.Range("a" & i).Value=Range("XER2").Value

    ws.Range("b" & i).Value=Range("XER5").Value

    ws.Range("c" & i & ":c" & (i+1)).Value=Range("XER3:xer4").Value

    ws.Range("e" & i & ":e" & (i+1)).Value=Range("XER6:xer7").Value

    ws.Range("g" & i & ":g" & (i+6)).Value=Range("d11:d17").Value

    ws.Range("o" & i).Value=Range("d19").Value

    ws.Range("s" & i & ":s" & (i+3)).Value=Range("e19:e22").Value

    ws.Range("w" & i).Value=Range("d23").Value

    ws.Range("aa" & i & ":aa" & (i+5)).Value=Range("e23:e28").Value

    ws.Range("ag" & i).Value=Range("d29").Value

    ws.Range("ak" & i & ":ak" & (i+4)).Value=Range("e29:e33").Value

    ws.Range("ap" & i).Value=Range("d34").Value

    ws.Range("at" & i & ":at" & (i+3)).Value=Range("e34:e37").Value

    ws.Range("ax" & i).Value=Range("d38").Value

    ws.Range("az" & i & ":az" & (i+1)).Value=Range("e38:e39").Value

    ws.Range("bb" & i).Value=Range("d40").Value

    ws.Range("Be" & i & ":be" & (i+2)).Value=Range("E40:E42").Value

    ws.Range("bh" & i).Value=Range("d44").Value

    ws.Range("Bk" & i & ":bk" & (i+2)).Value=Range("E44:e46").Value

    ws.Range("bn" & i).Value=Range("d47").Value

    ws.Range("Bv" & i & ":bv" & (i+7)).Value=Range("E47:e54").Value

    ws.Range("cd" & i).Value=Range("d55").Value

    ws.Range("ch" & i & ":ch" & (i+1)).Value=Range("E55:e56").Value

    ws.Range("cl" & i).Value=Range("d58").Value

    ws.Range("cp" & i & ":cp" & (i+3)).Value=Range("E60:e63").Value

    ws.Range("ct" & i & ":ct" & (i+2)).Value=Range("h65:h67").Value

    ws.Range("cw" & i).Value=Range("j11").Value

    ws.Range("cx" & i).Value=Range("j19").Value

    ws.Range("cy" & i).Value=Range("j44").Value

    ws.Range("cz" & i).Value=Range("j60").Value

    ws.Range("da" & i).Value=Range("j65").Value
'End With 

'Save and close the target workbook
wbk.Close(True)

Application.ScreenUpdating = True

End Sub

---

Below is completely your choice, and just a suggestion
I will keep the whole function as is(With only changes at start of Sub to fullcount= statement as shown) and change the signature of the Sub as below

Private Sub generate_report(ws As Worksheet)

Dim i As Long
Dim fullcount As Long

Application.ScreenUpdating = False    
.
.
.
Application.ScreenUpdating = True

End Sub

This allows you to paste the data into any target sheet that you pass in. It could be in the same workbook or a different workbook. And I would call it as below

Dim wbk as Workbook
Dim srcWbk as Workbook
Dim ws as Worksheet

Application.ScreenUpdating = False

Set srcWbk = ActiveWorkbook
Set wbk = Workbooks.Open("K:\Call Quality\Quality MI\Quality MI.XLSM")
Set ws = wbk.Worksheets("CSR Data dump")
srcWbk.Activate
Call generate_report(ws)
wbk.Close(True)

OR

Dim srcWbk as Workbook
Dim ws as Worksheet

Application.ScreenUpdating = False

Set ws = ActiveWorkbook.Worksheets("CSR Data dump")
Call generate_report(ws)

Cheers!