jimgus
08-29-2008, 01:41 PM
I can not figure out how to paste to the 1st unused row in a new workbook worksheet. Currently I am pasting to a specific row (A5) but I need it to be more dynamic so that I can put a history of worksheets on one worksheet. Here is what I have got so far. Help! Thanks.
Sub Lock_n_Paste()
If MsgBox("Is your Timesheet Complete? You will not be able to reopen without adminsitrative assistance", _
vbYesNo) <> vbYes Then Exit Sub
Cancel = True
Dim wbMe As Workbook, wbOpen As Workbook
Dim strSheet As String
Dim rng As String
ActiveSheet.Unprotect Password:="emsbear"
rng = ("A1:ak47")
strSheet = ActiveSheet.Name 'sheet your working on
Set wbMe = ThisWorkbook
Set wbOpen = Workbooks.Open _
(Filename:="C:\Documents and Settings\jim gusler\My Documents\opstimesheetcompiled.xls", Editable:=True)
wbMe.Sheets(strSheet).Range(rng).Copy _
Destination:=wbOpen.Sheets("Sheet3").Range("A5")
ActiveWorkbook.Save 'save newly opened workbook
ActiveWorkbook.Close 'close newly opened workbook
ActiveSheet.Protect Password:="emsbear"
ThisWorkbook.Save 'save original workbook
Application.Quit 'close excel
End Sub
Sub Lock_n_Paste()
If MsgBox("Is your Timesheet Complete? You will not be able to reopen without adminsitrative assistance", _
vbYesNo) <> vbYes Then Exit Sub
Cancel = True
Dim wbMe As Workbook, wbOpen As Workbook
Dim strSheet As String
Dim rng As String
ActiveSheet.Unprotect Password:="emsbear"
rng = ("A1:ak47")
strSheet = ActiveSheet.Name 'sheet your working on
Set wbMe = ThisWorkbook
Set wbOpen = Workbooks.Open _
(Filename:="C:\Documents and Settings\jim gusler\My Documents\opstimesheetcompiled.xls", Editable:=True)
wbMe.Sheets(strSheet).Range(rng).Copy _
Destination:=wbOpen.Sheets("Sheet3").Range("A5")
ActiveWorkbook.Save 'save newly opened workbook
ActiveWorkbook.Close 'close newly opened workbook
ActiveSheet.Protect Password:="emsbear"
ThisWorkbook.Save 'save original workbook
Application.Quit 'close excel
End Sub