+ Reply to Thread
Results 1 to 8 of 8

Copy first row if only first row of data available (from one workbook to another workbook)

  1. #1
    Registered User
    Join Date
    02-29-2020
    Location
    Malaysia
    MS-Off Ver
    Office 365
    Posts
    63

    Copy first row if only first row of data available (from one workbook to another workbook)

    Hi,

    i have a workbook as a masterfile and want to open another workbook to copy the data and paste it to my masterfile.
    here's the problem if my data only have the first row and the second row onwards is empty data. my current VBA code will copy the empty data and will cause the error when i paste the empty data into my masterfile.

    what is the VBA code to copy only the first row if only the first row of data available? then copy the entire row if the data is more than 1 row. the data is only available from column A to column T.

    My current code is below. Hope can assist to correct my code below. Also please check my Application.ScreenUpdating's code is it in the correct sequence?

    Please Login or Register  to view this content.
    Last edited by babychai; 05-27-2020 at 02:03 PM.

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 2016
    Posts
    4,877

    Re: Copy first row if only first row of data available (from one workbook to another workb

    Try replacing:
    Please Login or Register  to view this content.
    with:
    Please Login or Register  to view this content.
    I should point out that instead of Range.Select and then Selection.Copy, it is much easier to just use Range.Copy so it would be better to use

    Please Login or Register  to view this content.
    and then remove Selection.Copy
    Last edited by Arkadi; 05-27-2020 at 11:05 AM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    574

    Re: Copy first row if only first row of data available (from one workbook to another workb

    what is the VBA code to copy only the first row if only the first row of data available?
    then copy the entire row if the data is more than 1 row.
    the data is only available from column A to column T.
    Hi Babychai,

    I think you might try like this :
    Check if the row below cell A1 is empty or not, if empty, then just copy A1 to T1

    Please Login or Register  to view this content.
    I think we don't need the "ActiveCell" and select.

    From the three lines of your code below :
    Please Login or Register  to view this content.
    Maybe you can try to change with this :
    Please Login or Register  to view this content.
    Or you can use the End(xlup) instead

  4. #4
    Registered User
    Join Date
    02-29-2020
    Location
    Malaysia
    MS-Off Ver
    Office 365
    Posts
    63

    Re: Copy first row if only first row of data available (from one workbook to another workb

    Quote Originally Posted by Arkadi View Post
    Please Login or Register  to view this content.
    Hi, just want to confirm the code above is "A1:T" or "A1:T1" ?

  5. #5
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 2016
    Posts
    4,877

    Re: Copy first row if only first row of data available (from one workbook to another workb

    Range("A" & Rows.Count).End(xlUp).Row finds the last row... so "A1:T" & Range("A" & Rows.Count).End(xlUp).Row means A1:T20 if your last row of data is row 20... So yes, without the "1" after T

  6. #6
    Registered User
    Join Date
    02-29-2020
    Location
    Malaysia
    MS-Off Ver
    Office 365
    Posts
    63

    Re: Copy first row if only first row of data available (from one workbook to another workb

    Hi Arkadi, oh i understood now. your code is work. thank you so much

  7. #7
    Registered User
    Join Date
    02-29-2020
    Location
    Malaysia
    MS-Off Ver
    Office 365
    Posts
    63

    Re: Copy first row if only first row of data available (from one workbook to another workb

    Quote Originally Posted by karmapala View Post
    Maybe you can try to change with this :
    Please Login or Register  to view this content.
    Or you can use the End(xlup) instead
    Hi, i tested the code just now. the code is only work if the data is only available in first row.
    there's an error if the data is more than first row whether i use End(xlup) or End(xlDown)

  8. #8
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 2016
    Posts
    4,877

    Re: Copy first row if only first row of data available (from one workbook to another workb

    Thanks for the rep and feedback Once you are satisfied the problem is resolved, please remember to mark the thread as solved? Thanks!

+ 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. Replies: 3
    Last Post: 02-16-2018, 06:40 PM
  2. Replies: 12
    Last Post: 07-29-2014, 01:06 PM
  3. [SOLVED] Macro to find data in source workbook and copy paste to target workbook
    By D.Lovell in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 04-23-2014, 06:21 AM
  4. Copy data one workbook to another workbook without opening workbook
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-04-2014, 11:28 AM
  5. Replies: 6
    Last Post: 01-29-2013, 07:01 AM
  6. Macro to copy data from other workbook - let user choose which open workbook
    By ssu95bm in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-12-2011, 11:17 AM
  7. Replies: 1
    Last Post: 04-01-2006, 03:50 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