Excel/Copy/paste

Advertisement


Question
Tom

We used the code below and modified the second part to add some cells from the Interview Sheet to the interview sheet index but it gives a highlighted error and i am sure we did something wrong, can you look at the code below and see if you see something wrong, we were testing it in order to add more cells from the interview sheet, probably another 50 or so, so before we got to far into this we tried it and the error keeps coming up.....

i did the addition S3 and S4 along with R3 and R4 and the av

anyway .....here is the code, anything to help will be greatly appreicated Tom...

Sub customerindex1()
'
' customerindex Macro
'
Application.ScreenUpdating = False
Dim r1 As Range, ar As Range, r2 As Range
Dim r3 As Range, av As Range, r4 As Range
Dim s1 As String, s2 As String, s As String
Dim s3 As String, s4 As String, v As String

s1 = "Z5:Z17,AA21:AA25,U5:W14,p21:q21,p22,R12:R14,R9:R10,"
s2 = "R8:S8,R5:R6,P9:P14,P6:P7,v15:v16,G14,H12"
s = s1 & s2
Set r1 = Worksheets("Customer Index").Range(s)
For Each ar In r1.Areas
Set r2 = Worksheets("Customer").Range(ar(1).Address)
ar.Copy
r2.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

Next
s3 = "B15,B17,B19,C19,D19,F15,F17,F19,H15,H17,H19,H21,C38,C39:d39,C41,C42,J38,J39:L39,J41,J42,"
s4 = "B45,D45,C46:C49,F45,K45,J46:J49,C51:C58,J51:J58,C61:C67,C69:C78,D69:D70,J61:J78,K69:K70"
v = s3 & s4
Set r3 = Worksheets("Interview Sheet Index").Range(s)
For Each av In r3.Areas
Set r4 = Worksheets("Interview Sheet").Range(av(1).Address)
av.Copy
r4.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

Next
With Worksheets("Customer")
.Range("K3,k4").Value = 0
.Activate
.Range("H12").Select
End With
   
Application.ScreenUpdating = True
End Sub

Answer
Tony

Since you didn't say what line is highlighted, I really can't give you specific guidance.  I will make some comments in your code.  I tested this code and it worked for me with no errors in Excel 2010.  Now if you are using excel 2003 or earlier, you may have a problem with too may arguments for the Range object.

Sub customerindex1()
'
' customerindex Macro
'
Application.ScreenUpdating = False
Dim r1 As Range, ar As Range, r2 As Range
Dim r3 As Range, av As Range, r4 As Range
Dim s1 As String, s2 As String, s As String
Dim s3 As String, s4 As String, v As String

s1 = "Z5:Z17,AA21:AA25,U5:W14,p21:q21,p22,R12:R14,R9:R10,"
s2 = "R8:S8,R5:R6,P9:P14,P6:P7,v15:v16,G14,H12"
s = s1 & s2
Set r1 = Worksheets("Customer Index").Range(s)
For Each ar In r1.Areas
Set r2 = Worksheets("Customer").Range(ar(1).Address)
ar.Copy
r2.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

Next

s3 = "B15,B17,B19,C19,D19,F15,F17,F19,H15,H17,H19,H21,C38,C39:d39,C41,C42,J38,J39:L39,J41,J42,"
s4 = "B45,D45,C46:C49,F45,K45,J46:J49,C51:C58,J51:J58,C61:C67,C69:C78,D69:D70,J61:J78,K69:K70"
v = s3 & s4  ' you changed from s to v here
Set r3 = Worksheets("Interview Sheet Index").Range(v)  ' so s should be v here as well
For Each av In r3.Areas
Set r4 = Worksheets("Interview Sheet").Range(av(1).Address)
av.Copy
r4.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

Next
With Worksheets("Customer")
.Range("K3,k4").Value = 0
.Activate
.Range("H12").Select
End With
   
Application.ScreenUpdating = True
End Sub


If using excel 2003 you can do

Sub customerindex2()
'
' customerindex Macro
'
Application.ScreenUpdating = False
Dim r1 As Range, ar As Range, r2 As Range
Dim r3 As Range, av As Range, r4 As Range
Dim s1 As String, s2 As String, s As String
Dim s3 As String, s4 As String, v As String
Dim rS3 As Range, rS4 As Range
s1 = "Z5:Z17,AA21:AA25,U5:W14,p21:q21,p22,R12:R14,R9:R10,"
s2 = "R8:S8,R5:R6,P9:P14,P6:P7,v15:v16,G14,H12"
s = s1 & s2
Set r1 = Worksheets("Customer Index").Range(s)
For Each ar In r1.Areas
Set r2 = Worksheets("Customer").Range(ar(1).Address)
ar.Copy
r2.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

Next

s3 = "B15,B17,B19,C19,D19,F15,F17,F19,H15,H17,H19,H21,C38,C39:d39,C41,C42,J38,J39:L39,J41,J42" '<= remove last comma
s4 = "B45,D45,C46:C49,F45,K45,J46:J49,C51:C58,J51:J58,C61:C67,C69:C78,D69:D70,J61:J78,K69:K70"
With Worksheets("Interview Sheet Index")
 Set rS3 = .Range(s3)
 Set rS4 = .Range(s4)
End With
'v = s3 & s4  ' you changed from s to v here
Set r3 = Union(rS3, rS4)
For Each av In r3.Areas
Set r4 = Worksheets("Interview Sheet").Range(av(1).Address)
av.Copy
r4.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

Next
With Worksheets("Customer")
.Range("K3,k4").Value = 0
.Activate
.Range("H12").Select
End With
   
Application.ScreenUpdating = True
End Sub




--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.