Copy range from workbook and paste it into a closed workbook

[Sarahj0630]

I'm wanting to log user inputs from one spreadsheet to another one that would be closed. It just needs to paste to the next available row. I've scoured the internet and am having trouble finding something that works. The below code will open the workbook but isn't copy/pasting the info or closing the new workbook. I'm getting a run-time error. Please help!

Sub UpdateTrackerTool()

Dim FilePath As String
Dim wbk As Workbook

TrackerTool = "C:\Users\blah blah\Tech Issue Tracking Tool.xlsm"

Set wbk = ActiveWorkbook

Workbooks.Open TrackerTool

Range("A2").End(xlDown).Offset(1, 0).Select
wbk.Sheets("Tech Issue").Range("Q29:AA29").copy Destination:=ActiveCell

ActiveWorkbook.Close savechanges:=True


MsgBox "Technical Issue Logged Successfully!"

End Sub

 

[Joe4]

I like to use Workbook objects for both workbooks. Give this a try:

Sub UpdateTrackerTool()

    Dim TrackerTool As String
    Dim wbk1 As Workbook, wbk2 As Workbook

    TrackerTool = "C:\Users\blah blah\Tech Issue Tracking Tool.xlsm"

    Set wbk1 = ActiveWorkbook

    Workbooks.Open TrackerTool
   
    Set wbk2 = ActiveWorkbook

    wbk1.Activate
    wbk1.Sheets("Tech Issue").Range("Q29:AA29").Copy
    wbk2.Activate
    Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    wbk2.Close savechanges:=True

    MsgBox "Technical Issue Logged Successfully!"

End Sub

 

[Sarahj0630]

I like to use Workbook objects for both workbooks. Give this a try:

Sub UpdateTrackerTool()

    Dim TrackerTool As String
    Dim wbk1 As Workbook, wbk2 As Workbook

    TrackerTool = "C:\Users\blah blah\Tech Issue Tracking Tool.xlsm"

    Set wbk1 = ActiveWorkbook

    Workbooks.Open TrackerTool
  
    Set wbk2 = ActiveWorkbook

    wbk1.Activate
    wbk1.Sheets("Tech Issue").Range("Q29:AA29").Copy
    wbk2.Activate
    Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    wbk2.Close savechanges:=True

    MsgBox "Technical Issue Logged Successfully!"

End Sub

Thank you! I needed to enter this as a module and I had it in my sheet which was giving me grief. I also needed to paste as values as well and so I adjusted it and its working! Here is what I ended up with for those that might find this thread and need assistance

Sub UpdateTrackerTool()

Dim TrackerTool As String
Dim wbk1 As Workbook, wbk2 As Workbook

TrackerTool = "C:\Users\blah blah\Tech Issue Tracking Tool.xlsm"

Set wbk1 = ActiveWorkbook

Workbooks.Open TrackerTool

Set wbk2 = ActiveWorkbook

wbk1.Activate
wbk1.Sheets("Tech Issue").Range("Q29:AA29").copy
wbk2.Activate
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Tha
Application.CutCopyMode = False

wbk2.Close savechanges:=True

MsgBox "Technical Issue Logged Successfully!"

End Sub

 

[Joe4]

Your are welcome!

I needed to enter this as a module and I had it in my sheet which was giving me grief.

Just to clarify, you didn't enter this "as a Module", rather you entered the code into "a Module". A Module is just a container to hold VBA code.

VBA code is stored in Modules. There are different kind of Modules. There are the default "Sheet" and "ThisWorkbook" modules, that are used mostly for Event Procedure VBA code, which is code that runs automatically upon some event happening (i.e. opening the workbook, changing a cell, saving a file, etc).

And there are Modules which you can create yourself. The code in these will not run automatically, they are usually called by something (button, running the code manually, a function call, etc).