VBA If cells in Range not blank, take non blank cell values and put into another worksheet range

[mbkinzer]

Hello,

I have two sheets: RTW Report and Charges.

On the RTW Report sheet I want to take any cells that are not blank in column U down to my last row in that column and pull only non blank values over to my Charges sheet in range C18 down to last row in column C.
I have to take 2 off the U column since the last two rows should not be included.
Currently nothing happens when I run this.

Sheets("RTW Report").Activate

Dim lr As Long
lr = Cells(Rows.Count, "U").End(xlUp).Row
For Each Cell In Range("U2:U" & lr - 2)
If Cell.Value <> "" Then
Sheets("Charges").Range("C18").Value = Sheets("RTW Report").Range("U2:U" & lr - 2).Value
End If
Next Cell

Any advice? Thanks!

 

[Joe4]

Your explanation is a bit confusing, and there are issues with how you have written the code. I think it might be more helpful if you show us a sample of what your data looks like, and then show us what should be copied to the other sheet.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[mbkinzer]

Can you explain to me what is confusing? I am aware that the code has issues... that is why I am asking for help.
I cannot use this tool as I am doing this for work and will not risk data being compromised.

 

[Joe4]

Remember, you are familiar with your structure and problem, but we are not. So providing detail is important.
Are you trying to copy whole rows of data, or just single cells?
Are you starting to paste in cell C18 on your "Charges" sheet, and then wanting to copy down from there?
If so, I don't think you need to do a loop. Just copy all the non-blank values in column U to the other sheet.

 

[Joe4]

If just copying from column U, this should work (Iif my assumptions are correct):

Sub MyCopy()

Dim lr As Long

Sheets("RTW Report").Activate
lr = Cells(Rows.Count, "U").End(xlUp).Row
Sheets("RTW Report").Range("U2:U" & lr - 2).SpecialCells(xlCellTypeConstants, 23).Copy Sheets("Charges").Range("C18")

End Sub

 

[mbkinzer]

If just copying from column U, this should work (Iif my assumptions are correct):

Sub MyCopy()

Dim lr As Long

Sheets("RTW Report").Activate
lr = Cells(Rows.Count, "U").End(xlUp).Row
Sheets("RTW Report").Range("U2:U" & lr - 2).SpecialCells(xlCellTypeConstants, 23).Copy Sheets("Charges").Range("C18")

End Sub

This worked perfectly. Thank you for your assistance.

 

[Joe4]

You are welcome.