jimgus
08-27-2008, 01:53 PM
I have been working on this code and I can not get it to work and I know that it is probably simple but not knowledgable enough yet to figure it out. I want to paste a worksheet from multiple workbooks to one workbook worksheet. I want the pasted worksheet to follow one after another with 1 row seperating them. I thought that the attached code might work but I am getting errors. Thanks for any help that can be provided. jrg
Sub Lock_n_Paste()
Dim wbMe As Workbook, wbOpen As Workbook
Dim strSheet As String
Dim rng As String
ActiveSheet.Unprotect Password:="emsbear"
rng = ActiveSheet.UsedRange.Address
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("Sheet1").Range("A" & Rows.Count).End(x1up).Row.Offset(1)
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()
Dim wbMe As Workbook, wbOpen As Workbook
Dim strSheet As String
Dim rng As String
ActiveSheet.Unprotect Password:="emsbear"
rng = ActiveSheet.UsedRange.Address
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("Sheet1").Range("A" & Rows.Count).End(x1up).Row.Offset(1)
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