+ Reply to Thread
Results 1 to 17 of 17

Copy cells from one worksheet to another while skipping blanks

  1. #1
    Registered User
    Join Date
    02-03-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2013
    Posts
    22

    Copy cells from one worksheet to another while skipping blanks

    Hi. I hope you all can help me. I have this code that will copy cells while skipping blanks, but it drops everything into column A. I need it to go across multiple columns.

    I need it to copy a larger range.

    I have a range (CZ9:DD34 & CZ363:DD365) on a worksheet called K-5 (5 Week FV Bar).

    I want it to copy the cells but skip blanks. I want it to copy to this range (A8:E33) on worksheet K-5 (5 Week FV Bar) PR.

    My macro is below, but when I run it, it gives me run-time error of 13, "Type mismatch".

    Please help. Thanks in advance.

    Here's an example of what I need.

    table.jpg


    Sub Copy_Recipes_PRW1Monday()
    Dim cell As Range, r As Long
    r = 8
    Application.ScreenUpdating = False
    Sheets("K-5 (5 Week FV Bar) PR").Range("A8:E33").ClearContents
    For Each cell In Sheets("K-5 (5 Week FV Bar)").Range("CZ9:DD34, CZ363:DD365")
    If Len(cell) > 0 Then
    Sheets("K-5 (5 Week FV Bar) PR").Range("A" & r).Value = cell.Value
    r = r + 1
    End If
    Next cell
    Application.ScreenUpdating = True
    End Sub
    Last edited by gawk1980; 02-23-2013 at 08:41 AM.

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy cells from one worksheet to another while skipping blanks

    Gawk,
    Please wrap your code with tags.
    A picture would not help to test a code. Please attach a sample book.

  3. #3
    Registered User
    Join Date
    02-03-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2013
    Posts
    22

    Re: Copy cells from one worksheet to another while skipping blanks

    Sorry! Here you go.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy cells from one worksheet to another while skipping blanks

    Try this code

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy cells from one worksheet to another while skipping blanks

    The reason that you are getting errors because some of the formulas are returning an error. To avoid error, you can use Iferror like this

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    02-03-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2013
    Posts
    22

    Re: Copy cells from one worksheet to another while skipping blanks

    Thank you for replying so quickly. Unfortunately, it still dumps everything into column A.

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy cells from one worksheet to another while skipping blanks

    That was the code supposed to do.
    This line
    Please Login or Register  to view this content.
    Dumped to column A

  8. #8
    Registered User
    Join Date
    02-03-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2013
    Posts
    22

    Re: Copy cells from one worksheet to another while skipping blanks

    I'm sorry. I guess I didn't explain myself very well. I want it to go across columns instead of being dumped all down 1 column. If you take a look at the picture on my very first post, that's what I want it to do.

  9. #9
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy cells from one worksheet to another while skipping blanks

    Gawk,
    I have not looked at the post-just checked your code. I will come back with a solution shortley

  10. #10
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy cells from one worksheet to another while skipping blanks

    Gawk,
    I have not looked at the post-just checked your code. I will come back with a solution shortley

  11. #11
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy cells from one worksheet to another while skipping blanks

    Gawk,
    Few issues with this code. My understanding is you want to copy all non zero starting from A8 across. The problem is you U8. The code loops correctly and copies all values in the next empty column, but the last empty column is U8

    Please Login or Register  to view this content.

  12. #12
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy cells from one worksheet to another while skipping blanks

    Gawk,
    As an option, this code skipps any cell with an error. When I run this code, it only finds 6 cells with data, did not even go in to Column U.
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    02-03-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2013
    Posts
    22

    Re: Copy cells from one worksheet to another while skipping blanks

    I only want it to go from column A to E in worksheet K-5 (5 Week FV Bar) PR. I just need for it to skip rows. There are going to be empty cells in column DC in worksheet K-5 (5 Week FV Bar).

    If it won't work, I can use my original code that does, but it only copies over 1 column at a time. I was trying to save myself from writing a bunch of code, but if there's no way else, then I'll have to do it.

    I really do appreciate your help. Your code "If Len(cell) > 0 Or Len(cell.Text) > 0 Then" helped make the code work for some problem cells that were giving me run-time errors.

    Please Login or Register  to view this content.

  14. #14
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy cells from one worksheet to another while skipping blanks

    I think my last code #12 does copy only up to E. If a cell is empty, or show an error, the code skipps that cell. Have you tried it?

  15. #15
    Registered User
    Join Date
    02-03-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2013
    Posts
    22

    Re: Copy cells from one worksheet to another while skipping blanks

    I tried it, but it still goes across. Thanks for trying. I really do appreciate it.

    Untitled.jpg

  16. #16
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy cells from one worksheet to another while skipping blanks

    I am now confused. One minute you want to copy down then across and now down again.Please note that I have not looked at the range you want to copy, but I did not see any issue with the above code. Now, I have reversed it going down, you have two options-Down and Across

    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    02-03-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2013
    Posts
    22

    Re: Copy cells from one worksheet to another while skipping blanks

    Thank you for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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