Cut/paste with from one workbook to another

Question

I have a workbook called "Data" with raw data on the "Unprocessed" sheet.

I am trying to create a sheet for every agent, called "agent" (this will be changed for every agent but for ease we will call it this for now) that pulls raw data, one row at a time, into their work area from the data workbook.

I need cells A2:M2 cut from the "Unprocessed" sheet and pasted into A4:M4 of the "agent" sheet.

I get "out of range" error. I tie this sub to a button the agents hit to bring up a new row of data.

Sub newcancel_click()
    If Range("M4").Value = "EN" Then 
        MsgBox "You must Complete Previous cancellation.", vbCritical, "Error" 
    Else 
        Sheets("Uncompleted").Select 
        Range("A1:L1").Select 
        Application.CutCopyMode = False 
        Selection.Copy 
        Sheets("Jeremy").Select 
        Range("B4").Select 
        ActiveSheet.Paste 
        Sheets("Uncompleted").Select 
        Rows("1:1").Select 
        Application.CutCopyMode = False 
        Selection.Delete 
        Shift:=xlUp 
        Sheets("Jeremy").Select 
    End If 
End Sub

After this, I will need to create a sub to move the data from the work area to a "Processed" sheet on the "Data" book. I am comfortable that I will be able to write this sub once I get the first one working.

This is the code that I am using , this is drawing the data from a similar sheet to the "Unprocessed" sheet that I currently have in the "agent" book. I haven't been able to get this to draw from the other book — Jeremy Goyette, Oct 29 '19 at 15:06
I posted the code that works on the sheets contained within the agent workbook. I haven't ben able to get a single thing to work with accessing the other workbook, other than opening it up with Workbooks.Open"\filepath\Data.xlsx. Once I try the next lines of code it tells me out of range — Jeremy Goyette, Oct 29 '19 at 15:33

Plutian · Answer 1 · 2019-10-29T16:12:54.187

As for your current code, the following should work better.

Sub Newcancel_click2()
If ThisWorkbook.Sheets(1).Range("M4").Value = "EN" Then
        MsgBox "You must Complete Previous cancellation.", vbCritical, "Error"
        Else
        Sheets("Jeremy").Range("B4:M4").Value = Sheets("Uncompleted").Range("A1:L1").Value
        Sheets("Uncompleted").Rows(1).EntireRow.Delete shift:=xlUp
End If
End Sub

An interesting read for you: How to avoid using select in VBA

As for your issue, "Subscript out of range" might mean some of your ranges aren't defined properly. Your selection might not match the copy destination, or (most likely) the sheet you're referring to doesn't exist (either a typo or you've not created it yet).

To refer to another workbook you can use Workbooks("Workbook name").Sheets("Sheet name").Range etc. You can use activeworkbook to refer to the currently active workbook (not recommended as per above link to avoid using select) and you can refer to the workbook your VBA code is in with Thisworkbook, this is easier than using two statements: Workbook("Name for this one") and Workbook("Name for the other one")

In your case this would look something like:

Sub Newcancel_click2()
If ThisWorkbook.Sheets(1).Range("M4").Value = "EN" Then
        MsgBox "You must Complete Previous cancellation.", vbCritical, "Error"
        Else
        Workbook("Agent").Sheets("Jeremy").Range("B4:M4").Value = Thisworkbook.Sheets("Uncompleted").Range("A1:L1").Value
        Thisworkbook.Sheets("Uncompleted").Rows(1).EntireRow.Delete shift:=xlUp
End If
End Sub

(please note I made an approximation to most of your workbook and sheet names, please check and replace with your actual names)

400 error is normally you're trying to do a calculation on a set of data that doesn't exist. As this isn't what you're doing here, it's not obvious where it's going wrong. I recommend walking through your code step by step while pressing `F8` and checking where it goes wrong. Alternatively you could use `On Error Resume Next` to ignore errors, but I wouldn't recommend that as a permanent solution. I don't have access to a PC right now so can't help more until tomorrow sorry. — Plutian, Oct 29 '19 at 20:15
@JeremyGoyette (and Plutian, who's also relatively new to the site). Please do *not alter* the essence of your question once an Answer has been posted. This invalidates the answer and can render discussions in comments meaningless. Stack Overflow is a Q&A repository - the content here is meant to help others, not just the person who asks the question (IOW this is not a "help desk".) . I've rolled back the edit to the original version of code. When you run into a new problem, please post a *new question* rather than altering the content of the original question. — Cindy Meister, Oct 29 '19 at 20:58

Cut/paste with from one workbook to another

1 Answers1

Linked