Copy rows and paste text not formulas.

[sdsdub]

I have code that works exactly how I want it to EXCEPT it pastes formulas (vlookups, lookups, concatenates, adding vales from one column to another, etc...) instead of the text/values in the cells.
I am copying rows from a worksheet into a different worksheet and pasting it in the first empty row.

Sub GetData()
Dim sh4 As Worksheet, sh5 As Worksheet, lr As Long, rng As Range
Set sh4 = Sheets("CopySheet")
Set sh5 = Sheets("PasteSheet")
lr = sh4.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh4.Range("A2:A" & lr)
rng.EntireRow.Copy sh5.Cells(Rows.Count, 1).End(xlUp)(2)
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How many columns are you copying?

 

[sdsdub]

Hello! Thanks for the welcome. I've been using Mr. Excel for a long time and this is the first time I wasn't able to find the answer to a question.

There are 25 columns and 46 rows if that matters as well. I don't think I'll ever have more than 46 rows to copy.

 

[Fluff]

Ok, how about

Sub GetData()

   Dim sh4 As Worksheet, sh5 As Worksheet, lr As Long, rng As Range
   Set sh4 = Sheets("CopySheet")
   Set sh5 = Sheets("PasteSheet")
   lr = sh4.Cells(Rows.Count, 1).End(xlUp).Row
   Set rng = sh4.Range("A2:A" & lr).Resize(, 25)
   sh5.Cells(Rows.Count, 1).End(xlUp)(2).Resize(rng.Rows.Count, 25).Value = rng.Value
End Sub

 

[rollis13]

Change your Copy/Paste code from:

rng.EntireRow.Copy sh5.Cells(Rows.Count, 1).End(xlUp)(2)

to this:

rng.EntireRow.Copy
sh5.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

Oh, sorry, didn't update my page. Hi to all .

 

[Fluff]

It's always best to use the Value=Value approach, otherwise you can end-up with "blank" cells that contain null-strings.

 

[sdsdub]

It's always best to use the Value=Value approach, otherwise you can end-up with "blank" cells that contain null-strings.

Thanks! This did exactly what you said it would. It wasn't essential for what I'm doing but it is a nice bonus.

 

[sdsdub]

Change your Copy/Paste code from:

rng.EntireRow.Copy sh5.Cells(Rows.Count, 1).End(xlUp)(2)

to this:

rng.EntireRow.Copy
sh5.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

Oh, sorry, didn't update my page. Hi to all .

Thanks! this worked as well as the code in a previous reply. I appreciate your help!

 

[Fluff]

Glad we could help & thanks for the feedback.