Excel/Copy

Advertisement


Question
Tom

i tried to reply but couldn't.  So i am trying this.  I ran the code you gave me ...there are two issues

first...

even though we gave it a range of B2:N2 from the source WB, and a range of B3:n3 it still pastes in column A and there isn't anything in that column. so what happens is that the N column has nothing.  I tried changing the sh2.Range("a65536") to b but that didnt work either.  It simply doesnt paste to the right column which it should start at b3, the next line works great.

Second ....

Major issue is that once i protect the main sheet where i have this code, it opens it fine, but always send me that Cell is protected error when it comes back to the main page.  if its unprotected then it works fine. but when protect it keeps like i said giving me annoying cell is protected error.  that doesnt happen with any of the other double-click events i have on the main page.

any suggestions?


here is the code as a reminder that you sent me Tom

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   
    Dim sh2 As Worksheet, sh1 As Worksheet
 Dim bk2 As Workbook, bk1 As Workbook
 Dim r1 As Range, r2 As Range
 Dim NextRow As Long
 If Not Intersect(Target, Range("h14")) Is Nothing Then
 ' source workbook
 Set bk1 = Workbooks("ES600.xlsm")
 bk1.Activate
 Set sh1 = bk1.Worksheets("customers sold")
 Set r1 = sh1.Range("a2:N2")
 ' Target workbook
 Workbooks.Open Filename:= _
  "C:\Users\Administrator\Dropbox\ES Software\ES Software's Database v5.25.xlsm"
   Set bk2 = ActiveWorkbook
 Set sh2 = bk2.Worksheets("customers sold")
 Set r2 = sh2.Range("a3:n3")
 sh2.Unprotect
 NextRow = sh2.Range("a65536").End(xlUp).Row + 1
 If NextRow < 3 Then NextRow = 3
 r1.Copy
 sh2.Cells(NextRow, 1).PasteSpecial xlValues
 'r2.PasteSpecial xlValues  <== this line turned off; now a comment
 sh2.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
 bk2.Close SaveChanges:=True
 Set bk2 = Nothing
 End If

Answer
Tony,

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   
    Dim sh2 As Worksheet, sh1 As Worksheet
 Dim bk2 As Workbook, bk1 As Workbook
 Dim r1 As Range, r2 As Range
 Dim NextRow As Long
 Cancel = True
 If Not Intersect(Target, Range("h14")) Is Nothing Then
 ' source workbook
 Set bk1 = Workbooks("ES600.xlsm")
 bk1.Activate
 Set sh1 = bk1.Worksheets("customers sold")
 Set r1 = sh1.Range("B2:N2")
 ' Target workbook
 Workbooks.Open Filename:= _
  "C:\Users\Administrator\Dropbox\ES Software\ES Software's Database v5.25.xlsm"
   Set bk2 = ActiveWorkbook
 Set sh2 = bk2.Worksheets("customers sold")
 Set r2 = sh2.Range("B3:N3")
 sh2.Unprotect
 NextRow = sh2.Range("B65536").End(xlUp).Row + 1
 If NextRow < 3 Then NextRow = 3
 r1.Copy
 sh2.Cells(NextRow, "B").PasteSpecial xlValues
 'r2.PasteSpecial xlValues  <== this line turned off; now a comment
 sh2.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
 bk2.Close SaveChanges:=True
 Set bk2 = Nothing
 End If

It isn't clear to me what sheet you are talking about, but if it is the sheet where you doubleclick, then adding the cancel = True command should avoid the protection problem.  I tested that and confirmed that it eliminated the problem if the sheet with H7 where I doubleclicked was protected.

--
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.