Excel 2007 : [SOLVED]Paste Value to next empty cell

**GaidenFocus** · 10-20-2010, 10:22 AM

Hey guys quick question, i have a macro that pastes cells to the next empty row on another worksheet

If Range("F8").Value <> "SD" Or "RD" Then
   last = Worksheets(Range("G8").Value).Cells.Find(What:="*", _
   After:=[A1], _
   Lookat:=xlPart, _
   LookIn:=xlFormulas, _
   SearchOrder:=xlByRows, _
   SearchDirection:=xlPrevious, _
   MatchCase:=False).Row
   sheets("SHIPREC").Range("D8:E8").Copy sheets(Range("G8").Value).Cells(last + 1, 1)
End If

how do i get it to paste the value only instead of the formulas? i tried paste special.

**JBeaucaire** · 10-20-2010, 01:10 PM

This should do to replace all that:

If Range("F8").Value <> "SD" Or "RD" Then _
   Sheets(Range("G8").Value).Cells(Rows.Count, 1).End(xlUp) _
    .Offset(1).Resize(, 2).Value = Sheets("SHIPREC").Range("D8:E8")

**GaidenFocus** · 10-21-2010, 11:03 AM

thanks man, had to modify it a little didn't seem to work till i deleted that _ after the if statement. How would i do this with a non contiguous selection? i know how to select non contiguous cells but im not sure how to implement it into something like this

B6 will be in the A column and D7 will be in the B column.

Application.Union(Range("B6"), Range("D7")).Select

**GaidenFocus** · 10-21-2010, 11:09 AM

nevermind i got it to work, thanks so much for your help!

**JBeaucaire** · 10-21-2010, 11:10 AM

Huh, what?

Show use what you're doing on an actual sample workbook to alleviate headaches (ours). Hehe.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

**GaidenFocus** · 10-21-2010, 01:36 PM

Huh what to you too?

i got it to work man, if you look at your code you have an underscore after "then". it gave me an error till i deleted it.

so now it looks like this

   sheets(Range("E8").Value).Cells(Rows.Count, 1).End(xlUp) _
    .Offset(1).Resize(, 1).Value = Range("B6").Value
    
   sheets(Range("E8").Value).Cells(Rows.Count, 2).End(xlUp) _
    .Offset(1).Resize(, 1).Value = Range("D7").Value

and it works great.