Having a problem with my macro moving data from one sheet to another

[Yaku]

I'm about 95% of the way done but this last 5% is giving me some problems. I'm trying to copy data from "getPlays" worksheet to the first blank row in "Count" worksheet. So far so good, the macro below does the job.

The problem I'm having is that it is copying the formula over to "Count" rather than value. Instead of copying "=P2+q2", I want the answer (7). Seems like a simple problem but I'm having major brainlock with it.

Sub test2()With Application

.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
Sheets("getPlays").Select
Dim NextRow&, LastRow&, i%

Sheets.Add(After:=Sheets(ActiveSheet.Index)).Name = "ZZZtemp"
For i = 1 To 27
Cells(1, i).FormulaArray = "=MAX(IF(LEN(TRIM(getPlays!RC:R1000C))>0,ROW(getPlays!RC:R1000C),""""))"
Next i
LastRow = WorksheetFunction.Max(Range("A1").CurrentRegion)

ActiveSheet.Delete
With Sheets("count")
NextRow = .Cells.Find(What:="*", After:=.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
Sheets("getPlays").Range("A1:s" & LastRow).copy .Cells(NextRow, 1)
End With
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
End With
End Sub

 

[mumps]

Hi Yaku. Change this line:

Sheets("getPlays").Range("A1:s" & LastRow).copy .Cells(NextRow, 1)

to
this:

Sheets("getPlays").Range("A1:s" & LastRow).Copy
.Cells(NextRow, 1).PasteSpecial xlPasteValues

 

[kevatarvind]

Sub test2()
With Application

.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
Sheets("getPlays").Select
Dim NextRow&, LastRow&, i%

Sheets.Add(After:=Sheets(ActiveSheet.Index)).Name = "ZZZtemp"
For i = 1 To 27
Cells(1, i).FormulaArray = "=MAX(IF(LEN(TRIM(getPlays!RC:R1000C))>0,ROW(getPlays!RC:R1000C),""""))"
Next i
LastRow = WorksheetFunction.Max(Range("A1").CurrentRegion)

ActiveSheet.Delete
With Sheets("count")
NextRow = .Cells.Find(What:="*", After:=.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
Sheets("getPlays").Range("A1:s" & LastRow).Copy
.Cells(NextRow, 1).PasteSpecial Paste:=xlPasteValues
End With
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
End With
End Sub

 

[mumps]

It looks like you're getting an error on the highlighted line. Try changing this:

NextRow = .Cells.Find(What:="*", After:=.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
Sheets("getPlays").Range("A1:s" & LastRow).Copy
[COLOR=#ff0000].Cells(NextRow, 1).PasteSpecial Paste:=xlPasteValues[/COLOR]

to this:

Dim bottomA As Integer
bottomA = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet2").Range("A1").Copy
.Cells(bottomA + 1, 1).PasteSpecial Paste:=xlPasteValues

 

[Yaku]

Awesome, that works. Thanks for all the help guys!

 

[mumps]

My pleasure.