Excel macro – paste only non empty cells from one sheet to another

by

Looks like you may be making some common rookie mistakes here (it’s okay we all did it).

VBA example with line-by-line explanations

TIP: Try not to use “Select” or “Copy”. Why use select when all you have to do is reference the cells themselves? For example, instead of using

Sheets("codes").Select
Range("A5:A100").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B28").Select
ActiveSheet.Paste

Just use

dim mySheet as Worksheet, myOtherSheet as Worksheet, myBook as Workbook 'Define your workbooks and worksheets as variables
set myBook = Excel.ActiveWorkbook
set mySheet = myBook.Sheets("codes")
set myOtherSheet = myBook.Sheets("Sheet2")

dim i as integer, j as integer 'Define a couple integer variables for counting

j = 28 'This variable will keep track of which row we're on in Sheet2 (I'm assuming you want to start on line 28)
for i = 5 to 100 'This is the beginning the the loop which will repeat from 5 to 100 . . .
   if mySheet.Cells(i,1).value <> "" then ' . . . for each digit, it will check if the cell's value is blank. If it isn't then it will . . .
      myOtherSheet.Cells(j,2).value = mySheet.Cells(i,1).value ' . . . Copy that value into the cell on Sheet2 in the row specified by our "j" variable.
      j = j + 1 'Then we add one to the "j" variable so the next time it copies, we will be on the next available row in Sheet2.
   end if
next i 'This triggers the end of the loop and moves on to the next value of "i".

I did the same thing all the time when I first started, and it never works out right. “Select” causes errors left and right. Use my code, read the comments, and you’ll be fine. A quick WARNING: I don’t have Excel on this computer so I couldn’t test the code. If it doesn’t work for some reason, leave me a comment and tomorrow I’ll fix it at work.

The above code will omit blank cells completely when copying the data over to your second sheet. If you want to input a certain text for blank cells instead (like “N/A”), then you can use the following:

 dim mySheet as Worksheet, myOtherSheet as Worksheet, myBook as Workbook 'Define your workbooks and worksheets as variables
 set myBook = Excel.ActiveWorkbook
 set mySheet = myBook.Sheets("codes")
 set myOtherSheet = myBook.Sheets("Sheet2")

 dim i as integer, j as integer 'Define a couple integer variables for counting

 j = 28 'This variable will keep track of which row we're on in Sheet2 (I'm assuming you want to start on line 28)
 for i = 5 to 100 'This is the beginning the the loop which will repeat from 5 to 100 . . .
    if mySheet.Cells(i,1).value <> "" then ' . . . for each digit, it will check if the cell's value is blank. If it isn't then it will . . .
       myOtherSheet.Cells(j,2).value = mySheet.Cells(i,1).value ' . . . Copy that value into the cell on Sheet2 in the row specified by our "j" variable.
    else 'If the cell is blank, then . . .
       myOtherSheet.Cells(j,2).value = "N/A" ' . . . place the text "N/A" into the cell in row "j" in Sheet2.
    end if 'NOTICE we moved the "end if" statement up a line, so that it closes the "if" statement before the "j = j + 1" statement. _
      This is because now we want to add one to the "j" variable (i.e., move to the next available row in Sheet2) regardless of whether the cell in the "codes" sheet is blank or not.
       j = j + 1 'Then we add one to the "j" variable so the next time it copies, we will be on the next available row in Sheet2.
 next i 'This triggers the end of the loop and moves on to the next value of "i".

More Related Contents:

  1. Copy Paste Values only( xlPasteValues )
  2. Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table
  3. Get text from clipboard using GetText – avoid error on empty clipboard
  4. VBA copy rows that meet criteria to another sheet
  5. SumIf using Excel VBA
  6. How can I call a macro which contains a loop but only run one iteration of that loop?
  7. Test or check if sheet exists
  8. Copy from one workbook and paste into another
  9. How to copy Outlook mail message into excel using VBA or Macros
  10. For Loop not fully cycling in excel VBA [duplicate]
  11. Unique Random Numbers using VBA
  12. Populate unique values into a VBA array from Excel
  13. Excel VBA App stops spontaneously with message “Code execution has been halted”
  14. How to remove all non alphanumeric characters from a string except period and space in excel?
  15. Closing Excel Application using VBA
  16. How to clear memory to prevent “out of memory error” in excel vba?
  17. What are the benefits of setting objects to “Nothing”
  18. How can I kill task manager processes through VBA code?
  19. VBA: Usage of parentheses for a method
  20. Fully reference a worksheet by codename
  21. OnTime for less than 1 second without becoming Unresponsive
  22. Get Unicode characters with charcode values greater hex `FFFF`
  23. Pointers needed for speeding up nested loop macro in VBA
  24. ‘1004’: “The sort reference is not valid.”
  25. Select entire column in table using Excel VBA
  26. Create hyperlink to another sheet
  27. Reducing WithEvent declarations and subs with VBA and ActiveX
  28. VBA Excel simple Error Handling
  29. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?
  30. How to save semi-colon delimited csv file using VBA?

Leave a Comment