+ Reply to Thread
Results 1 to 7 of 7

Find and Copy ONLY Last full Row to Different Workbook Sheet

Hybrid View

  1. #1
    Registered User
    Join Date
    08-28-2019
    Location
    US
    MS-Off Ver
    365
    Posts
    6

    Question Find and Copy ONLY Last full Row to Different Workbook Sheet

    This copies last row and everything above it but I only want to capture the last row; how do I fix this (or if there's another method altogether, I'm good w/ that too!)

    Need help knowing how to paste it into Sheet 2 of a different workbook too as this only puts it into Sheet2 of same workbook.
    Other workbook would be found here: c:/users/me/Documents/Reports/MasterReport.xlsm (Sheet2)

    I can either have both open at same time when I hit RUN or I can keep the MasterReport closed if that's easier -- whatever is easiest to script..

    Sub CopyLastLinetoNewSheet()
    Sheets("Sheet1").Range("A2:T" & Cells(Rows.Count, "A").End(xlUp).Row).Copy Sheets("Sheet2").Range("A1")
    End Sub
    Thank you
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,700

    Re: Find and Copy ONLY Last full Row to Different Workbook Sheet

    For the first part, try:

    
    Sub CopyLastLinetoNewSheet()
    Sheets("Sheet1").Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Resize(1, 20).Copy Sheets("Sheet2").Range("A1")
    End Sub
    For the second part, fully qualify the target range with the workbook reference ... easiest if the target workbook is open.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    08-28-2019
    Location
    US
    MS-Off Ver
    365
    Posts
    6

    Re: Find and Copy ONLY Last full Row to Different Workbook Sheet

    TMS, thanks greatly! That first part works great! Grabs only the last full row as intended! Very happy!
    As for the copying to another workbook, I'll have to try to figure out where/how to put that workbook reference -- not yet a vba pro

    Chris

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,545

    Re: Find and Copy ONLY Last full Row to Different Workbook Sheet

    You could try this.
    Sub Maybe()
    Dim wb1 As Workbook, wb2 As Workbook
        Application.ScreenUpdating = False
        Set wb1 = ThisWorkbook
        On Error Resume Next
        Set wb2 = Workbooks("C:/users/me/Documents/Reports/MasterReport.xlsm")
        'If it is not open, open it	
        If Err Then Set wb2 = Workbooks.Open("C:/users/me/Documents/Reports/MasterReport.xlsm")
        On Error GoTo 0
            With wb1.Sheets("Sheet1")
                'From TMS
                .Range("A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Resize(1, 20).Copy wb2.Sheets("Sheet2").Range("A1")
                'or if it needs to go in the first empty row
                '.Range("A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Resize(1, 20).Copy wb2.Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
            End With
        wb2.Close True    '<---- If it needs saving. If not, use False instead of True
    End Sub

  5. #5
    Registered User
    Join Date
    08-28-2019
    Location
    US
    MS-Off Ver
    365
    Posts
    6

    Question Re: Find and Copy ONLY Last full Row to Different Workbook Sheet

    Jolivanes , thank you for the help on adding workbook! very kool - but - had issues..
    Ran it with wb1 open and wb2 closed (ran code from the wb1) and got the 1st error.
    So, I changed the code to comment out the first one and use the 2nd line option, still got error.

    Third, I opened wb1 and wb2 at same time, ran it again and it ran successfully, then it closed the wb2 out automatically however nothing was saved. Based on your notes, the way it sits now, it should save with "True" still in tact... but don't we need to add something to command it to both SAVE and CLOSE? Again, I'm not an expert but it seems like it only says: "Close=True" and seems like it needs both Save and Close

    If we can get the saving part working I think we might be golden!?
    (tried to attach pic but it said it would not let me until I've done a few other posts)
    (tried to place a copy of the PICTURE into an excel file but won't allow me to upload that either! ARGH!)...


    Here's the lower section of the code as it exists at this moment (where it runs successfully without turning anything yellow - but just doesn't save anything)

        On Error GoTo 0
            With wb1.Sheets("Sheet1")
                'From TMS
                '.Range("A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Resize(1, 20).Copy wb2.Sheets("Sheet2").Range("A1")
                'or if it needs to go in the first empty row
                .Range("A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Resize(1, 20).Copy wb2.Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
            End With
        wb2.Close True    '<---- If it needs saving. If not, use False instead of True
    End Sub

  6. #6
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,545

    Re: Find and Copy ONLY Last full Row to Different Workbook Sheet

    You don't show your whole code.
    Is the path changed properly?
    It does not say "Close =True"
    It states "wb2.Close" and after that a space and then True. The "True" means to save it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sheet full of formulas, one has a glitch. How to find?
    By vfr700f2 in forum Excel General
    Replies: 2
    Last Post: 11-09-2016, 08:15 AM
  2. Excel not opening workbook to show full sheet.
    By terric2 in forum Excel General
    Replies: 4
    Last Post: 09-12-2013, 11:58 AM
  3. Find name(s) within text string that matches a list, then copy full name
    By juhaszp in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2013, 08:48 AM
  4. [SOLVED] If cell is full, copy row to another sheet and workbook
    By pucval in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-21-2012, 08:14 AM
  5. Copy full row then paste to another workbook horizontally last 5 copied cells
    By raemon in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-05-2012, 01:41 PM
  6. [SOLVED] On selection, find dups in two colums across workbook, copy duplicates to existing sheet.
    By Joshuarat in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-21-2012, 07:54 PM
  7. Find and Open Workbook then copy and move sheet
    By cwilson in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-02-2005, 05:54 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