Paste to unused field

[jimgus]

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.

[vba]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[/vba]

[Bob Phillips]

[vba]

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)
With wbOpen.Sheets("Sheet3")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If LastRow <> 1 Or .Range("A1").Value <> "" Then
LastRow = LastRow + 1
End If
wbMe.Sheets(strSheet).Range(rng).Copy _
Destination:=.Range("A" & LastRow)
End With
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
[/vba]

[jimgus]

Thanks XLD, This works great, I should have been able to do this but I am still learning. I thought it would be easy to paste to the first open column on a worksheet but I am obviously missing something. Below is a copy of the code I am using. Any suggestions. jrg Thanks.

[vba]
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)
With wbOpen.Sheets("Sheet3")
LastColumn = Cells(1,Column.Count).End(xlToLeft).Column
If LastColumn <> 1 Or .Range("A1").Value <> "" Then
LastColumn = LastColumn + 1
End If
wbMe.Sheets(strSheet).Range(rng).Copy _
Destination:=.Range("A" & LastColumn)
End With
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
[/vba]

[Bob Phillips]

Sorry, I am missing this. What exactly can you/can't you do with this code?