Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    pasting an area one workbook to another (2000)

    The following 2 subproceedures are run one after the other. Columns A:E within strCurrentTimeShortFileName hold employee data and are hidden to protect the information.

    The first sub procedure correctly copies the data and the second selects the file into which it is to be pasted. However the second sub procedure will not paste the data.

    I have tried various different paste statements such as activesheet.paste and various different methods for selecting the relevant paste area without success.

    I will need to hide and protect columns A:E within strDestinationSheet2. However currently they remain unhidden while I am trying to complete the program.

    Any ideas?


    Private Sub GetTimeRates()
    Workbooks(strCurrentTimeShortFileName).Activate

    Worksheets("Rates").Select
    Worksheets("Rates").Unprotect (xx.x)
    Worksheets("Rates").AutoFilterMode = False

    Worksheets("Rates").Activate

    Range("a1:e50000").Select
    Selection.Copy
    Worksheets("Rates").Protect (xx.x)

    End Sub
    Private Sub PasteTimeRates()
    ThisWorkbook.Activate
    Worksheets(strDestinationSheet2).Activate
    Range("a1:e50000").Select
    Selection.Paste

    Application.CutCopyMode = False
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: pasting an area one workbook to another (2000)

    Hi Alex,

    Excel's clipboard is rather volatile. If you copy something, then do something else instead of pasting immediately, the clipboard will be cleared. In this case, restoring the protection is the culprit. You will have to paste the data immediately after copying them, then restore protection.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pasting an area one workbook to another (2000)

    Selecting things is hardly ever necessary and getting rid of activating and selecting obsoletes your unprotecting code. This should work:

    Private Sub GetTimeRates()
    Workbooks(strCurrentTimeShortFileName).Worksheets( "Rates").Range("a1:e50000").Copy
    End Sub

    Private Sub PasteTimeRates()
    ThisWorkbook.Worksheets(strDestinationSheet2).Rang e("a1:e50000").Paste
    Application.CutCopyMode = False
    End Sub

    Or even shorter:

    Private Sub GetTimeRatesAndPaste()
    Workbooks(strCurrentTimeShortFileName).Worksheets( "Rates").Range("a1:e50000").Copy _
    Destination:=ThisWorkbook.Worksheets(strDestinatio nSheet2).Range("a1:e50000")
    End Sub

    Furthermore, be aware that Excel's protection can *very* easily be broken, leaving the information you wish to stay hidden open for anyone to see. It most definitely is not secure enough for Confidential information about personnel...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •