Summary Page

StevenH

New Member
Joined
Mar 10, 2004
Messages
12
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!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Travis

Well-known Member
Joined
Feb 26, 2002
Messages
1,711
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

New Member
Joined
Mar 10, 2004
Messages
12
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

Well-known Member
Joined
Feb 26, 2002
Messages
1,711
try this

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,621
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top