Summary Page

[StevenH]

I am using the following code to copy data into a summary page....

Sub Summary()

Dim i As Integer
Dim myVal As String

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Summary").Delete
Application.DisplayAlerts = True

Worksheets.Add(Worksheets(1)).Name = "Summary"
For i = Worksheets("Start").Index + 1 To Worksheets("End").Index - 1
myVal = Worksheets(i).Range("P13").Text
If UCase(Mid(myVal, 2, 1)) = "x" Or myVal = "Dog" Then
Worksheets(i).Range("A1:a50").Copy _
Worksheets("Summary").Range("IV1").End(xlToLeft)(1, 2)
End If
Next i

End Sub


The values in A1:A50 are derived through a formula. The code works to the extent but I am getting #REF on the summary page. I know the problem is somehow related to the formulas in A1:A50 but I am not sure how to fix it.
Thanks for the help!

 

[Travis]

Whats the formula in column A

Also there looks to be an error in your code. The Ucase function is changing the value found in your mid to an Uppercase, and your testing to see if it matches a lower case "x" which it never will. But this is unrelated to your Ref error

 

[Damon Ostrander]

Hi Steven,

Your code looks okay, so I suspect that the problem is that the problem has to do with the fact that you are copying the entire range, which includes formulas, formatting, validation, etc. This can cause a #REF if fomulas, conditional formatting, or validation reference cells in a way that is inappropriate in the new (pasted) context. If this is the cause in your case it is difficult to advise you without seeing the spreadsheet. But if you are attempting to transfer the cell contents and really don't care about the cell formatting, formulas, etc., you should be able to solve it by copying only the cell contents.

Damon

 

[StevenH]

The x should have been X (uppercase) Cell contents are the only thing that is important. how do I modify to get the contents only???
Thanks!

 

[Travis]

try this

Sub Summary()
Dim i As Integer
Dim myVal As String
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Summary").Delete
Application.DisplayAlerts = True
Worksheets.Add(Worksheets(1)).Name = "Summary"
For i = Worksheets("Start").Index + 1 To Worksheets("End").Index - 1
myVal = Worksheets(i).Range("P13").Text
If UCase(Mid(myVal, 2, 1)) = "X" Or myVal = "Dog" Then
Worksheets(i).Select
Range("A1:A50").Copy
Worksheets("Summary").Select
Range("IV1").End(xlToLeft)(1, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
Next i
End Sub