Results 1 to 6 of 6

Pasting Issues Between Workbooks

Threaded View

  1. #1
    Registered User
    Join Date
    06-18-2014
    Location
    Whatcom, Washington
    MS-Off Ver
    2010
    Posts
    7

    Pasting Issues Between Workbooks

    Oy.

    New poster here, moderate VBA user. Begging for some help with a project that has had me stumped for the last 48 hours.

    What I am attempting to do:

    When closing a Workbook (named "CIR") I would like the data on the "DATA" tab to be cut and then pasted to another Workbook ("CIR Master") in the first blank row. Code posted below goes well until the last line.

    The Issue:

    Rather than pasting to the target Workbook ("CIR Master"), the code below pastes to the source workbook. I have tried many different ways of pasting, but no joy.

    Any help would be greatly appreciated.

    A second question...any ideas on what code could be used to see if the target Workbook is open? (ie, whether it would open as "read-only".)

    'Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    Dim wbTarget As Workbook
    Dim wbThis As Workbook
    
    'Define Variables
    Set wbThis = ActiveWorkbook
    
    'Go to "DATA"
    Sheets("DATA").Select
    
    'Exit if "A2" is BLANK
    Range("A2").Select
    
    If IsEmpty(ActiveCell.Value) Then
        Exit Sub
    End If
    
    'Open CIR Master List
    Set wbTarget = Workbooks.Open("K:\CIR Master List.xlsm")
    
    'Clear Memory
    Application.CutCopyMode = False
    
    'Return to Source
    wbThis.Activate
    
    'Cut Rows
    Range("A62236").Select
    Selection.End(xlUp).Select
    intLastRow = ActiveCell.Row
    Range("$A$2:$S$" & intLastRow).Cut
    
    'Back to Target Workbook
    wbTarget.Activate
    
    'Find Next Empty Cell
    Range("A62236").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    
    '...and it FAILS here
    ActiveSheet.Paste
    Last edited by OwlChutney; 06-19-2014 at 05:18 PM. Reason: Typo

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy and pasting issues with mouse connected
    By kas106 in forum Excel General
    Replies: 2
    Last Post: 04-11-2012, 04:52 AM
  2. Pasting issues
    By NH Dan in forum Excel General
    Replies: 4
    Last Post: 08-26-2011, 08:15 AM
  3. Issues with Pasting Cells
    By sarahwilson1000 in forum Excel General
    Replies: 0
    Last Post: 06-22-2011, 02:13 PM
  4. Various pasting issues
    By AnthonyWB in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-31-2011, 12:51 PM
  5. Pasting Personality issues
    By BBFB in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-16-2005, 01:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1