Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Unprotect sheet; copy with macro; protect; paste (Excel 2003)

    Hi all...I need help with a macro...I have a worksheet that is protected (without a password). I have a macro that will unprotect the sheet and copy certain data...I want to then paste that data into a different sheet..........the macro I am using looks like this:

    ActiveSheet.Unprotect
    Range("C8:O8").Select
    Selection.Copy

    .....if I use a password to protect the sheet, I know how to re-write the macro to unprotect and re-protect (with the same password)....the problem is that it won't permit me to 'keep' the copied data on the clipboard for pasting purposes---when I move to the next sheet and right click, I don't see any PASTE or PASTE SPECIAL options........any suggestions?? Thank you...

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

    Re: Unprotect sheet; copy with macro; protect; paste (Excel 2003)

    Do you need to unprotect the sheet in order to copy the cells? (It depends on what you allow on the protected sheet.)

    You can use code like this:

    Worksheets("ThisSheet').Range("C8:O8").Copy Destination:=Worksheets("ThatSheet").Range("A8")

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Unprotect sheet; copy with macro; protect; pas

    hmmm.....the data is copied from a sheet (called SUMMARY) in 1 workbook, and then pasted into a worksheet called OVERTIME in a different workbook....could I have a macro that reads Worksheets("SUMMARY").Range("C8:O8").Copy Destination:=Clipboard ????

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

    Re: Unprotect sheet; copy with macro; protect; pas

    If you don't specify a destination, the cells are copied to the clipboard. But it is also possible to copy / paste to a different workbook in one step:

    Workbooks("One.xls").Worksheets("Summary").Range(" C8:O8").Copy Destination:=Workbooks("Two.xls").Worksheets("Over time").Range("A8")

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Unprotect sheet; copy with macro; protect; pas

    ..boy, that would be great, altho I'm not sur eif it would actually work in this case b/c the destination range changes with each copy/paste operation (ie: the person would be copying from 40 or 50 different timesheet SUMMARY pages and then the pasting would go to a different workbook in the sheet called MONTHEND but each paste would be to a different range on the MONTHEND sheet....I would like to be able to do it in 1 operation, but don't see how if the destin. range changes with each copy&paste...??..??

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

    Re: Unprotect sheet; copy with macro; protect; pas

    The destination range can be variable.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Unprotect sheet; copy with macro; protect; pas

    So my macro would be something like this:

    Workbooks("TIME SHEET.xls").Worksheets("SUMMARY").Range("C8:O8").C opy Destination:=Workbooks("MONTH END.xls").Worksheets("OVERTIME").Range ???

    ...and so my bookkeeper could open the MONTH END workbook, go to the OVERTIME sheet, keep that book open.....then, in turn, open individual TIMESHEET from various employees, go to the SUMMARY page, hit the macro button and it would copy that range and the paste it to the MONTH END OVERTIME SHEET, close the first employee's time sheet, open an other and repeat the operation until all 40 or 50 people have been pasted from one to the other??....what do I call the destination range? Does it matter if people name their TIME SHEETS differently (eg: McNab Time Sheet, Hans Time Sheet), so long as the copy/paste macro is built into each TIME SHEET ??

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

    Re: Unprotect sheet; copy with macro; protect; pas

    If the code is called while the timesheet is active, you can use

    Range("C8:O8").Copy Destination:=Workbooks("MONTH END.xls").Worksheets("OVERTIME").Range(...)

    since if you don't specify a workbook/worksheet, Excel will assume that the source range is on the active worksheet.

    And yes, it's possible to change the destination range, but you'll have to provide more info.

  9. #9
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Unprotect sheet; copy with macro; protect; pas

    Hi Hans....I am attaching a sample of what I am trying to do...it is a representation of 3 workbooks on 1 sheet for ease of reading, with an explanation....hope this clarifies it a bit...
    Attached Files Attached Files

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

    Re: Unprotect sheet; copy with macro; protect; pas

    Perhaps something like this?

    Dim r As Long
    r = Workbooks("MONTH END.xls").Worksheets("OVERTIME").Range("C65536").E nd(xlUp).Row + 1
    Range("C8:O8").Copy Destination:=Workbooks("MONTH END.xls").Worksheets("OVERTIME").Range("C" & r)

    and similar for the INDIVIDUAL STATS sheet.

  11. #11
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Unprotect sheet; copy with macro; protect; pas

    Hi Hans….thank you for the code. I tried it but couldn’t get it to work (it would copy, but not paste) and so I thought it would be easier if you saw what I was working on…I have attached a zip folder with 3 small files. They are TimeSheet, Month-end and Overtime.

    The Timesheet is an abbreviated version of the actual Time Sheet, but it is the Summary that captures all data entered during the month. There are 3 parts to it~ the “Overtime” portion (range is B6:X6); there is also the “Monthly” part in range B14:N63……I am ignoring the “Individual Stats” portion (C8:O8) for the moment.

    The data from Overtime (B6:X6) is copied from the workbook ‘TimeSheet’, worksheet ‘Summary’ and pasted into the workbook ‘Overtime’, worksheet ‘Over-Time Report’….the data occupies 1 row when pasted as shown.

    The data from Monthly (B14:N63) is copied from workbook ‘Time Sheet’, worksheet “Summary’ and pasted into the workbook ‘Month-end’, worksheet ‘Month-end’….the #REFs that you see would normally link to the data entry worksheet and pick up various time codes that are entered by the employee as they go through the month. The data (from employee #1) is pasted into range B18:N68 (50 rows). Data from employee #2 is pasted below (B69:N119) the last row of employee #1, and so on and so on until all 75 employees’ data is copied (from their individual time sheets) and pasted into the Month-end report. In column A, you will see 0…I am working on a formula that will permit me to select column A, and ‘select’ any row with a 0 in column A and hide that row, based on the fact that 0 appears if (a) the number in column H = 0, or ([img]/forums/images/smilies/cool.gif[/img] the time/earning code in column E is “TLT”, or, © there is no surname in column B…this will hide rows that are TLT time/earning code is TLT or there are no hours claimed that month for a particular code (eg: SDN or STM). The Month-end report is several thousand rows b/c each employee requires 50 rows of ‘space’ to past their data into the worksheet (before hiding TLT rows or rows with 0 hours), but I shortened the size of the pages to allow me to post them.

    On the far right of the Month-end and the Overtime sheets, you will see a gray column (“Do Not Type here”)…this contains code that will re-alphabetize the names and data, so that I can paste data when I receive it, without having to wait for it all and do it alphabetically….

    What I am working on is an attempt to come up with code that will copy & paste (from 1 workbook to another) in 1 motion….then, at month end, I would open the Overtime report and the Month-end report; keep those open; and sequentially open individual time sheets and do the auto copy & paste from Time Sheet to Month-end and to Overtime; close employee #1’s sheet, open #2’s, copy & paste, close #2’s open #3’s and so on and so on….and when everything is pasted to the reports, re-align columns, re-alphabetize and (for the Month end report) hide the rows with TLT or 0 hours claimed……hope that this is clear and I really appreciate you taking a look at these samples. Thank you.
    Attached Files Attached Files

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

    Re: Unprotect sheet; copy with macro; protect; pas

    You calculated the next available row in the source sheet instead of in the destination sheet <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
    And having merged cells complicates things.
    Try the following macros. Adjust the workbook and worksheet names.

    Sub Copy_Paste_Month_end()
    Dim r As Long
    Const WorkbookName = "Month-end.xls"
    Const WorksheetName = "Month-end"
    Dim wsh As Worksheet
    Set wsh = Workbooks(WorkbookName).Worksheets(WorksheetName)
    r = wsh.Range("B65536").End(xlUp).Row + 1
    ' Handle nasty merged cell
    If r = 17 Then r = 18
    wsh.Unprotect
    Range("B14:N63").Copy Destination:=wsh.Range("B" & r)
    wsh.Protect
    End Sub

    Sub Copy_Paste_OT()
    Dim r As Long
    Const WorkbookName = "Overtime.xls"
    Const WorksheetName = "Over-Time Report"
    Dim wsh As Worksheet
    Set wsh = Workbooks(WorkbookName).Worksheets(WorksheetName)
    r = wsh.Range("B32").End(xlUp).Row + 1
    ' Handle nasty merged cell
    If r = 6 Then r = 8
    wsh.Unprotect
    Range("B6:X6").Copy Destination:=wsh.Range("B" & r)
    wsh.Protect
    End Sub

    Note: may I recommend that you clean up your modules? You have lots of empty, unused modules, and you appear to have multiple copies of the same macro with different names. This makes it more difficult than necessary to find things.

  13. #13
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Unprotect sheet; copy with macro; protect; pas

    Thank you, as always, Hans...you're amazing.....I will start to work on this; a couple of questions, if you don't mind:

    (1) if I get rid of all merged cells in any of these worksheets, I gather that will make things work more smoothly, and, that I can skip the code that says "Handle nasty merged cell, if r = 17, then r = 18" (..."if r = 6, then r = 8") ?

    (2) I want to remove modules, and there are indeed multiple macros...I'm never sure how these modules get there (I assume somehow I put them there?) but so long as they contain no code, then I gather that I am safe to delete them?...and the same as for the macros ?

    (3) Does the code you wrote also address the question of hiding certain rows in the destination Month-end (rows that either contain TLT as a time code, or have zero hours entered?....presently, I use an Excel utility called ASAP Utilities to 'find' rows that have a 0 at the start, and select & hide those rows (to clean up the sheet for paper printing)....your suggested code doesn't appear to do that, but I can't really tell....thanks?

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

    Re: Unprotect sheet; copy with macro; protect; pas

    1) Yes - those two lines are necessary because you have vertically merged cells, so if no rows have been added yet, the first available row is lower down than the code expects.

    2) I'd copy the macros you want to keep into a single module, you can then safely remove the other modules.

    3) The macros I posted only copy data from the timesheet to the overtime and month end workbooks.

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

    Re: Unprotect sheet; copy with macro; protect; pas

    I'd use AutoFilter to hide rows with a 0 in column A.

Page 1 of 3 123 LastLast

Posting Permissions

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