VBA Excel Copy/Paste cells only if non-empty
BillRussell
Posted messages
7
Status
Member
-
ThauTheme Posted messages 1564 Status Member -
ThauTheme Posted messages 1564 Status Member -
Hello everyone,
I’m asking for your help on a topic that may seem basic but I can’t find the exact code I need on the forum.
My setup: Excel 2013
My requirement:
Copy a range of cells from one sheet to another summary sheet, but only copy cells that have values, i.e., non-empty. The tricky part is that I need to paste only the filled cells from the "Option 1" sheet into the "Budget tracking" sheet, and paste the values from the "Option 2" sheet just on the next line.
Sub Test_2() 'Loop until not empty
Do While Not (IsEmpty(ActiveCell))
'Copy row
With Sheets("Budget Tracking")
Sheets("Option 1").Range("A7:A26").Copy _
Destination = .Cells(.Cells(655636, 8).End(xlUp).Row + 1, 1)
Application.CutCopyMode = False
End With
Loop
End Sub
Could you tell me what’s wrong with my code, because it doesn’t work...
Do you have perhaps a different approach to achieve this?
Thank you, and feel free to ask if you want more information or if I should provide a test file.
--
The world began without man, and it will end without him.
I’m asking for your help on a topic that may seem basic but I can’t find the exact code I need on the forum.
My setup: Excel 2013
My requirement:
Copy a range of cells from one sheet to another summary sheet, but only copy cells that have values, i.e., non-empty. The tricky part is that I need to paste only the filled cells from the "Option 1" sheet into the "Budget tracking" sheet, and paste the values from the "Option 2" sheet just on the next line.
Sub Test_2() 'Loop until not empty
Do While Not (IsEmpty(ActiveCell))
'Copy row
With Sheets("Budget Tracking")
Sheets("Option 1").Range("A7:A26").Copy _
Destination = .Cells(.Cells(655636, 8).End(xlUp).Row + 1, 1)
Application.CutCopyMode = False
End With
Loop
End Sub
Could you tell me what’s wrong with my code, because it doesn’t work...
Do you have perhaps a different approach to achieve this?
Thank you, and feel free to ask if you want more information or if I should provide a test file.
--
The world began without man, and it will end without him.
1 answer
Hello Bill, hello forum,
Perhaps like this:
See you later,
ThauTheme
Perhaps like this:
Sub Test_2() Dim OS As Worksheet 'declare variable OS (Source Sheet) Dim OD As Worksheet 'declare variable OD (Destination Sheet) Dim CEL As Range 'declare variable CEL (CELL) Dim DEST As Range 'declare variable DESTINATION cell Set OS = ActiveCell 'define the source sheet OS Set OD = Sheets("Suivi Budget") 'define the destination sheet OD For Each CEL In OS.Range("A7:A26") 'loop through all CELLS in the range A7:A26 If CEL <> "" Then 'condition: if the cell is not empty Set DEST = OD.Cells(Application.Rows.Count, 8).End(xlUp).Offset(1, 0) 'define the destination cell DEST CEL.Copy DEST 'copy the CEL cell to DEST End If 'end of the condition Next CEL 'next cell in the loop End Sub See you later,
ThauTheme