If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Protecting sheets but allowing them to be accessible...
Hi
I have a workbook of 13 sheets. The first sheet is an entry page where information is entered across the year. It isn't protected as people need to have access to it to make their entries. The other 12 sheets are one for each month of the year. They fill up month by month as entries are made on sheet 1. They hold summaries of the information entered on the first page. People don't need direct access to them. Here's the problem. If I protect sheets 2-13 , then when I enter information on sheet 1 I get an access error saying that they cannot be overwritten . However , as nobody need to have direct access to the information on these pages , I would rather protect them. Is it possible to update these sheets when I'm sending data from sheet one , but still have the sheets protected so that the data and coding etc. cannot be overwritten by accessing them via the tabs? Hope you can help. Best Wishes Drno |
#2
|
|||
|
|||
Colin
you don't say how you are updating sheets 2 to 13. If you are using VBA code you could try the approach: Unprotect ... ' your code Protect ... Look up the syntax for Unprotect and Protect in the Help or just record the macro. I often protect sheets with a blank password ... just to protect the formula but to make it easy for me, or anyone else, to access the data when it needs to be modified. That would look something like: ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ' your code ActiveSheet.Unprotect Regards Trevor "Colin Hayes" wrote in message ... Hi I have a workbook of 13 sheets. The first sheet is an entry page where information is entered across the year. It isn't protected as people need to have access to it to make their entries. The other 12 sheets are one for each month of the year. They fill up month by month as entries are made on sheet 1. They hold summaries of the information entered on the first page. People don't need direct access to them. Here's the problem. If I protect sheets 2-13 , then when I enter information on sheet 1 I get an access error saying that they cannot be overwritten . However , as nobody need to have direct access to the information on these pages , I would rather protect them. Is it possible to update these sheets when I'm sending data from sheet one , but still have the sheets protected so that the data and coding etc. cannot be overwritten by accessing them via the tabs? Hope you can help. Best Wishes Drno |
#3
|
|||
|
|||
In article , Trevor Shuttleworth
writes Colin you don't say how you are updating sheets 2 to 13. If you are using VBA code you could try the approach: HI Thanks for getting back. I do use some VBA code to do the transfer of information from sheet one to the correct month in 2 to 13. If I'm to build in unprotect / protect into the code it would need to be seamless and with no sign that it was doing it. That would be perfect. I'm imaging that it would unprotect at the very beginning and the protect at the very end of course. I did try to put some code in , but it gives pop ups asking for passwords etc. and brings the whole thing to a halt. Here's the VBA I use. My password is '12071956' - any ideas how I could put in code to protect and unprotect gracefully? Sub copy() Dim sourceRange As Range Dim destrange As Range Dim Lr As Long Dim monthdate As String monthdate = Format(Cells(50, 2), "mmmm") Lr = LastRow(Sheets(monthdate)) + 1 Set sourceRange = Sheets("Receipt ").Rows("50:50") Set destrange = Sheets(monthdate).Rows(Lr). _ Resize(sourceRange.Rows.Count) destrange.Value = sourceRange.Value 'Print Receipt ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Sub test() Dim monthdate As String Dim a As Integer For a = 1 To 12 monthdate = Format(DateSerial(2003, a, 1), "mmmm") MsgBox monthdate Next End Sub ' Format(Date, "dd-mm-yy") Thanks Drno |
#4
|
|||
|
|||
Colin
: Sheets(monthdate).Unprotect Password:="12071956" destrange.Value = sourceRange.Value Sheets(monthdate).Protect Password:="12071956", DrawingObjects:=True, Contents:=True, Scenarios:=True : Regards Trevor "Colin Hayes" wrote in message ... In article , Trevor Shuttleworth writes Colin you don't say how you are updating sheets 2 to 13. If you are using VBA code you could try the approach: HI Thanks for getting back. I do use some VBA code to do the transfer of information from sheet one to the correct month in 2 to 13. If I'm to build in unprotect / protect into the code it would need to be seamless and with no sign that it was doing it. That would be perfect. I'm imaging that it would unprotect at the very beginning and the protect at the very end of course. I did try to put some code in , but it gives pop ups asking for passwords etc. and brings the whole thing to a halt. Here's the VBA I use. My password is '12071956' - any ideas how I could put in code to protect and unprotect gracefully? Sub copy() Dim sourceRange As Range Dim destrange As Range Dim Lr As Long Dim monthdate As String monthdate = Format(Cells(50, 2), "mmmm") Lr = LastRow(Sheets(monthdate)) + 1 Set sourceRange = Sheets("Receipt ").Rows("50:50") Set destrange = Sheets(monthdate).Rows(Lr). _ Resize(sourceRange.Rows.Count) destrange.Value = sourceRange.Value 'Print Receipt ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Sub test() Dim monthdate As String Dim a As Integer For a = 1 To 12 monthdate = Format(DateSerial(2003, a, 1), "mmmm") MsgBox monthdate Next End Sub ' Format(Date, "dd-mm-yy") Thanks Drno |
#5
|
|||
|
|||
In article , Trevor Shuttleworth
writes Colin : Sheets(monthdate).Unprotect Password:="12071956" destrange.Value = sourceRange.Value Sheets(monthdate).Protect Password:="12071956", DrawingObjects:=True, Contents:=True, Scenarios:=True : Regards Trevor Hi Trevor OK thanks for that. I see the logic of what you suggest. I couldn't make it work however , as I'm getting red errors in the coding. Maybe I'm putting the code in the wrong place in the routine. You're plainly better at this than I am - where would you put these lines in the code so that they protect / unprotect at the right time and don't cause errors? Best Wishes Colin Sub copy() Dim sourceRange As Range Dim destrange As Range Dim Lr As Long Dim monthdate As String monthdate = Format(Cells(50, 2), "mmmm") Lr = LastRow(Sheets(monthdate)) + 1 Set sourceRange = Sheets("Receipt ").Rows("50:50") Set destrange = Sheets(monthdate).Rows(Lr). _ Resize(sourceRange.Rows.Count) destrange.Value = sourceRange.Value 'Print Receipt ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Sub test() Dim monthdate As String Dim a As Integer For a = 1 To 12 monthdate = Format(DateSerial(2003, a, 1), "mmmm") MsgBox monthdate Next End Sub ' Format(Date, "dd-mm-yy") |
#6
|
|||
|
|||
Colin
looks like you are suffering from "line wrap". The last line should all be on one line. Alternatively: : Sheets(monthdate).Unprotect Password:="12071956" destrange.Value = sourceRange.Value Sheets(monthdate).Protect _ Password:="12071956", _ DrawingObjects:=True, _ Contents:=True, Scenarios:=True : Note that the "_" is a line continuation flag. Makes for easier reading and gets over the problem you have experienced. So, your code would look like: Sub copy() Dim sourceRange As Range Dim destrange As Range Dim Lr As Long Dim monthdate As String monthdate = Format(Cells(50, 2), "mmmm") Lr = LastRow(Sheets(monthdate)) + 1 Set sourceRange = Sheets("Receipt ").Rows("50:50") Set destrange = Sheets(monthdate).Rows(Lr). _ Resize(sourceRange.Rows.Count) Sheets(monthdate).Unprotect _ Password:="12071956" destrange.Value = sourceRange.Value Sheets(monthdate).Protect _ Password:="12071956", _ DrawingObjects:=True, _ Contents:=True, Scenarios:=True 'Print Receipt ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True End Sub Regards Trevor "Colin Hayes" wrote in message ... In article , Trevor Shuttleworth writes Colin : Sheets(monthdate).Unprotect Password:="12071956" destrange.Value = sourceRange.Value Sheets(monthdate).Protect Password:="12071956", DrawingObjects:=True, Contents:=True, Scenarios:=True : Regards Trevor Hi Trevor OK thanks for that. I see the logic of what you suggest. I couldn't make it work however , as I'm getting red errors in the coding. Maybe I'm putting the code in the wrong place in the routine. You're plainly better at this than I am - where would you put these lines in the code so that they protect / unprotect at the right time and don't cause errors? Best Wishes Colin Sub copy() Dim sourceRange As Range Dim destrange As Range Dim Lr As Long Dim monthdate As String monthdate = Format(Cells(50, 2), "mmmm") Lr = LastRow(Sheets(monthdate)) + 1 Set sourceRange = Sheets("Receipt ").Rows("50:50") Set destrange = Sheets(monthdate).Rows(Lr). _ Resize(sourceRange.Rows.Count) destrange.Value = sourceRange.Value 'Print Receipt ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Sub test() Dim monthdate As String Dim a As Integer For a = 1 To 12 monthdate = Format(DateSerial(2003, a, 1), "mmmm") MsgBox monthdate Next End Sub ' Format(Date, "dd-mm-yy") |
#7
|
|||
|
|||
In article , Trevor Shuttleworth
writes Colin looks like you are suffering from "line wrap". The last line should all be on one line. Hi Trevor OK that works a treat now. Thanks - I'm grateful for your trouble and for your expertise. You've really helped me out. Best Wishes Colin Alternatively: : Sheets(monthdate).Unprotect Password:="12071956" destrange.Value = sourceRange.Value Sheets(monthdate).Protect _ Password:="12071956", _ DrawingObjects:=True, _ Contents:=True, Scenarios:=True : Note that the "_" is a line continuation flag. Makes for easier reading and gets over the problem you have experienced. So, your code would look like: Sub copy() Dim sourceRange As Range Dim destrange As Range Dim Lr As Long Dim monthdate As String monthdate = Format(Cells(50, 2), "mmmm") Lr = LastRow(Sheets(monthdate)) + 1 Set sourceRange = Sheets("Receipt ").Rows("50:50") Set destrange = Sheets(monthdate).Rows(Lr). _ Resize(sourceRange.Rows.Count) Sheets(monthdate).Unprotect _ Password:="12071956" destrange.Value = sourceRange.Value Sheets(monthdate).Protect _ Password:="12071956", _ DrawingObjects:=True, _ Contents:=True, Scenarios:=True 'Print Receipt ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True End Sub Regards Trevor "Colin Hayes" wrote in message ... In article , Trevor Shuttleworth writes Colin : Sheets(monthdate).Unprotect Password:="12071956" destrange.Value = sourceRange.Value Sheets(monthdate).Protect Password:="12071956", DrawingObjects:=True, Contents:=True, Scenarios:=True : Regards Trevor Hi Trevor OK thanks for that. I see the logic of what you suggest. I couldn't make it work however , as I'm getting red errors in the coding. Maybe I'm putting the code in the wrong place in the routine. You're plainly better at this than I am - where would you put these lines in the code so that they protect / unprotect at the right time and don't cause errors? Best Wishes Colin Sub copy() Dim sourceRange As Range Dim destrange As Range Dim Lr As Long Dim monthdate As String monthdate = Format(Cells(50, 2), "mmmm") Lr = LastRow(Sheets(monthdate)) + 1 Set sourceRange = Sheets("Receipt ").Rows("50:50") Set destrange = Sheets(monthdate).Rows(Lr). _ Resize(sourceRange.Rows.Count) destrange.Value = sourceRange.Value 'Print Receipt ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Sub test() Dim monthdate As String Dim a As Integer For a = 1 To 12 monthdate = Format(DateSerial(2003, a, 1), "mmmm") MsgBox monthdate Next End Sub ' Format(Date, "dd-mm-yy") |
#8
|
|||
|
|||
In article , Colin Hayes
writes In article , Trevor Shuttleworth writes Colin looks like you are suffering from "line wrap". The last line should all be on one line. Trevor BTW , do you know if it's possible to hide a tab or tabs in a workbook from view? Maybe I could run a macro from a key press to hide / unhide certain worksheet tabs in a workbook. Best Wishes Colin Hi Trevor OK that works a treat now. Thanks - I'm grateful for your trouble and for your expertise. You've really helped me out. Best Wishes Colin Alternatively: : Sheets(monthdate).Unprotect Password:="12071956" destrange.Value = sourceRange.Value Sheets(monthdate).Protect _ Password:="12071956", _ DrawingObjects:=True, _ Contents:=True, Scenarios:=True : Note that the "_" is a line continuation flag. Makes for easier reading and gets over the problem you have experienced. So, your code would look like: Sub copy() Dim sourceRange As Range Dim destrange As Range Dim Lr As Long Dim monthdate As String monthdate = Format(Cells(50, 2), "mmmm") Lr = LastRow(Sheets(monthdate)) + 1 Set sourceRange = Sheets("Receipt ").Rows("50:50") Set destrange = Sheets(monthdate).Rows(Lr). _ Resize(sourceRange.Rows.Count) Sheets(monthdate).Unprotect _ Password:="12071956" destrange.Value = sourceRange.Value Sheets(monthdate).Protect _ Password:="12071956", _ DrawingObjects:=True, _ Contents:=True, Scenarios:=True 'Print Receipt ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True End Sub Regards Trevor "Colin Hayes" wrote in message ... In article , Trevor Shuttleworth writes Colin : Sheets(monthdate).Unprotect Password:="12071956" destrange.Value = sourceRange.Value Sheets(monthdate).Protect Password:="12071956", DrawingObjects:=True, Contents:=True, Scenarios:=True : Regards Trevor Hi Trevor OK thanks for that. I see the logic of what you suggest. I couldn't make it work however , as I'm getting red errors in the coding. Maybe I'm putting the code in the wrong place in the routine. You're plainly better at this than I am - where would you put these lines in the code so that they protect / unprotect at the right time and don't cause errors? Best Wishes Colin Sub copy() Dim sourceRange As Range Dim destrange As Range Dim Lr As Long Dim monthdate As String monthdate = Format(Cells(50, 2), "mmmm") Lr = LastRow(Sheets(monthdate)) + 1 Set sourceRange = Sheets("Receipt ").Rows("50:50") Set destrange = Sheets(monthdate).Rows(Lr). _ Resize(sourceRange.Rows.Count) destrange.Value = sourceRange.Value 'Print Receipt ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Sub test() Dim monthdate As String Dim a As Integer For a = 1 To 12 monthdate = Format(DateSerial(2003, a, 1), "mmmm") MsgBox monthdate Next End Sub ' Format(Date, "dd-mm-yy") |
#9
|
|||
|
|||
Colin
Sheets("Sheet3").Visible = True MsgBox "now you see it" Sheets("Sheet3").Visible = False MsgBox "now you don't" Regards Trevor "Colin Hayes" wrote in message ... In article , Colin Hayes writes In article , Trevor Shuttleworth writes Colin looks like you are suffering from "line wrap". The last line should all be on one line. Trevor BTW , do you know if it's possible to hide a tab or tabs in a workbook from view? Maybe I could run a macro from a key press to hide / unhide certain worksheet tabs in a workbook. Best Wishes Colin Hi Trevor OK that works a treat now. Thanks - I'm grateful for your trouble and for your expertise. You've really helped me out. Best Wishes Colin Alternatively: : Sheets(monthdate).Unprotect Password:="12071956" destrange.Value = sourceRange.Value Sheets(monthdate).Protect _ Password:="12071956", _ DrawingObjects:=True, _ Contents:=True, Scenarios:=True : Note that the "_" is a line continuation flag. Makes for easier reading and gets over the problem you have experienced. So, your code would look like: Sub copy() Dim sourceRange As Range Dim destrange As Range Dim Lr As Long Dim monthdate As String monthdate = Format(Cells(50, 2), "mmmm") Lr = LastRow(Sheets(monthdate)) + 1 Set sourceRange = Sheets("Receipt ").Rows("50:50") Set destrange = Sheets(monthdate).Rows(Lr). _ Resize(sourceRange.Rows.Count) Sheets(monthdate).Unprotect _ Password:="12071956" destrange.Value = sourceRange.Value Sheets(monthdate).Protect _ Password:="12071956", _ DrawingObjects:=True, _ Contents:=True, Scenarios:=True 'Print Receipt ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True End Sub Regards Trevor "Colin Hayes" wrote in message ... In article , Trevor Shuttleworth writes Colin : Sheets(monthdate).Unprotect Password:="12071956" destrange.Value = sourceRange.Value Sheets(monthdate).Protect Password:="12071956", DrawingObjects:=True, Contents:=True, Scenarios:=True : Regards Trevor Hi Trevor OK thanks for that. I see the logic of what you suggest. I couldn't make it work however , as I'm getting red errors in the coding. Maybe I'm putting the code in the wrong place in the routine. You're plainly better at this than I am - where would you put these lines in the code so that they protect / unprotect at the right time and don't cause errors? Best Wishes Colin Sub copy() Dim sourceRange As Range Dim destrange As Range Dim Lr As Long Dim monthdate As String monthdate = Format(Cells(50, 2), "mmmm") Lr = LastRow(Sheets(monthdate)) + 1 Set sourceRange = Sheets("Receipt ").Rows("50:50") Set destrange = Sheets(monthdate).Rows(Lr). _ Resize(sourceRange.Rows.Count) destrange.Value = sourceRange.Value 'Print Receipt ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Sub test() Dim monthdate As String Dim a As Integer For a = 1 To 12 monthdate = Format(DateSerial(2003, a, 1), "mmmm") MsgBox monthdate Next End Sub ' Format(Date, "dd-mm-yy") |
#10
|
|||
|
|||
In article , Trevor Shuttleworth
writes Colin Sheets("Sheet3").Visible = True MsgBox "now you see it" Sheets("Sheet3").Visible = False MsgBox "now you don't" Regards Trevor Hi Trevor Thanks for that. It works a treat - up to a point! I made two macros - one to hide the tabs : Sub Hide_Tabs() ' ' Hide_Tabs Macro ' Keyboard Shortcut: Ctrl+q ' Sheets("January").Visible = False Sheets("February").Visible = False Sheets("March").Visible = False Sheets("April").Visible = False Sheets("May").Visible = False Sheets("June").Visible = False Sheets("July").Visible = False Sheets("August").Visible = False Sheets("September").Visible = False Sheets("October").Visible = False Sheets("November").Visible = False Sheets("December").Visible = False End Sub and one to Unhide them : Sub Unhide_Tabs() ' ' Unhide_Tabs Macro ' ' Keyboard Shortcut: Ctrl+a Sheets("January").Visible = True Sheets("February").Visible = True Sheets("March").Visible = True Sheets("April").Visible = True Sheets("May").Visible = True Sheets("June").Visible = True Sheets("July").Visible = True Sheets("August").Visible = True Sheets("September").Visible = True Sheets("October").Visible = True Sheets("November").Visible = True Sheets("December").Visible = True ' End Sub The Hide macro works perfectly and hides all the named sheets. When I first ran the unhide macro it worked and showed them all again. I saved and closed excel. Now when i open the program again and run the Unhide macro , it doesn't work! All my sheets are now hidden and refuse to be revealed. I can't see anything wrong with the macro - it's simple enough - any ideas? Excel can be very mysterious at times! Best Wishes Colin "Colin Hayes" wrote in message ... In article , Colin Hayes writes In article , Trevor Shuttleworth writes Colin looks like you are suffering from "line wrap". The last line should all be on one line. Trevor BTW , do you know if it's possible to hide a tab or tabs in a workbook from view? Maybe I could run a macro from a key press to hide / unhide certain worksheet tabs in a workbook. Best Wishes Colin Hi Trevor OK that works a treat now. Thanks - I'm grateful for your trouble and for your expertise. You've really helped me out. Best Wishes Colin Alternatively: : Sheets(monthdate).Unprotect Password:="12071956" destrange.Value = sourceRange.Value Sheets(monthdate).Protect _ Password:="12071956", _ DrawingObjects:=True, _ Contents:=True, Scenarios:=True : Note that the "_" is a line continuation flag. Makes for easier reading and gets over the problem you have experienced. So, your code would look like: Sub copy() Dim sourceRange As Range Dim destrange As Range Dim Lr As Long Dim monthdate As String monthdate = Format(Cells(50, 2), "mmmm") Lr = LastRow(Sheets(monthdate)) + 1 Set sourceRange = Sheets("Receipt ").Rows("50:50") Set destrange = Sheets(monthdate).Rows(Lr). _ Resize(sourceRange.Rows.Count) Sheets(monthdate).Unprotect _ Password:="12071956" destrange.Value = sourceRange.Value Sheets(monthdate).Protect _ Password:="12071956", _ DrawingObjects:=True, _ Contents:=True, Scenarios:=True 'Print Receipt ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True End Sub Regards Trevor "Colin Hayes" wrote in message ... In article , Trevor Shuttleworth writes Colin : Sheets(monthdate).Unprotect Password:="12071956" destrange.Value = sourceRange.Value Sheets(monthdate).Protect Password:="12071956", DrawingObjects:=True, Contents:=True, Scenarios:=True : Regards Trevor Hi Trevor OK thanks for that. I see the logic of what you suggest. I couldn't make it work however , as I'm getting red errors in the coding. Maybe I'm putting the code in the wrong place in the routine. You're plainly better at this than I am - where would you put these lines in the code so that they protect / unprotect at the right time and don't cause errors? Best Wishes Colin Sub copy() Dim sourceRange As Range Dim destrange As Range Dim Lr As Long Dim monthdate As String monthdate = Format(Cells(50, 2), "mmmm") Lr = LastRow(Sheets(monthdate)) + 1 Set sourceRange = Sheets("Receipt ").Rows("50:50") Set destrange = Sheets(monthdate).Rows(Lr). _ Resize(sourceRange.Rows.Count) destrange.Value = sourceRange.Value 'Print Receipt ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True End Sub Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Sub test() Dim monthdate As String Dim a As Integer For a = 1 To 12 monthdate = Format(DateSerial(2003, a, 1), "mmmm") MsgBox monthdate Next End Sub ' Format(Date, "dd-mm-yy") |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Protecting Sheets within a workbook | [email protected] | Worksheet Functions | 1 | August 2nd, 2004 12:04 AM |
Hi! Urgent pls: Protecting sheets so macros/buttons still work? | StargateFanFromWork | General Discussion | 7 | July 3rd, 2004 02:25 PM |
protecting sheets | steve | Worksheet Functions | 6 | March 6th, 2004 10:18 PM |
Password protecting multiple sheets | Allison | Worksheet Functions | 1 | February 27th, 2004 03:15 AM |
Protecting Selected Sheets | Steve Klenner | Worksheet Functions | 1 | January 22nd, 2004 10:55 PM |