• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 737
  • Last Modified:

Error: The selection is not valid

Hi Experts,

The attached script shows error as “The selection is not valid” at this line:

.Range("A13:I" & n & ",M13:M" & n & ",Y13:AC" & n).Copy Sheets("Process").Range("A" & Rows.Count).End(xlUp)(2)

How to rectify this error? I have attached the workbook for Experts perusal. Hope Experts will help me.

Sub ArchiveData()

    Dim n As Long

With Sheets("Process")
    n = .Range("A" & Rows.Count).End(xlUp).Row
     .Range("A13:I" & n & ",M13:M" & n & ",Y13:AC" & n).Copy Sheets("Process").Range("A" & Rows.Count).End(xlUp)(2)
End With
    MsgBox "OK"
    
End Sub

Open in new window

CopyPaste.xls
0
Theva
Asked:
Theva
2 Solutions
 
StephenJRCommented:
Line 5 should be Sheets("Request")
0
 
TracyVBA DeveloperCommented:
Try this instead, you are copy values to row 13, so you can't repaste them in that section the way you are trying.  Paste some where after row 13.

Sub ArchiveData()

    Dim n As Long

With Sheets("Process")
    n = .Range("A" & Rows.Count).End(xlUp).Row
     .Range("A13:I" & n & ",M13:M" & n & ",Y13:AC" & n).Copy .Range("A14")
End With
    MsgBox "OK"
    
End Sub

Open in new window

0
 
gbanikCommented:
Your paste is happening on the same range as your source...
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
ThevaAuthor Commented:
Hi StephenJR,

Need your help. How to set the data copied from row  13 to 37 only. When I add title at row 39, its copying that row as well which is shouldn't. Hope we can fix the range.

Hi broomee9,
Tested but its not copying data from request sheet to process sheet.
0
 
TracyVBA DeveloperCommented:
Your request is extremely unclear.  No where did you say you need to copy data from one sheet to another.  You asked to fix an error, which my original post fixes.  If you're looking to copy the data from Process to the end of the data in Request then try this.

Sub ArchiveData()

    Dim n As Long
    Dim p As Long
    
With Sheets("Process")
    n = .Range("A" & Rows.Count).End(xlUp).Row + 1
    p = Sheets("Request").Range("A" & Rows.Count).End(xlUp).Row + 1

    .Range("A3:I" & n & ",M3:M" & n & ",Y3:AC" & n).Copy Sheets("Request").Cells(p, 1)
End With
    MsgBox "OK"
    
End Sub

Open in new window

CopyPaste-2-.xls
0
 
ThevaAuthor Commented:
Hi broomee9,

Apology for off track the question. Indeed yours and JR solution have resolved the problem, but I just realized that its also copying other cell data (after row 37) if column A  has a value.
0
 
ThevaAuthor Commented:
Hi,

Thanks for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now