How to paste a line at a time?

Vba to copy paste

• QUESTION: I have seen here Damon's code but my requirement is bit different although almost similar: http://en.allexperts.com/q/Excel-1059/2009/10/Copy-paste-Worksheets-conditions-1.htm I have three workbooks book1, book2 and book3 in a folder called 2010. I want to copy the sheet1 and sheet2 of last month of book3 to book1 and book2 respectively if i open book3 for the first time in a given month. I am thinking to have following options: 1. The copy should be done without having to open book1 and book2 manually. 2. I will be using this workbooks for about four to five years. So, the sheet name of book1 and book2 may be named as Nov-2010, Dec-2010, Jan-2011 etc. (This option is missing in Damon's code) 3. Suppose if i open book3 in April then the March month's sheet1 and sheet2 should be copied to book1 and book2 respectively. The book1 and book2 worksheets should not get overwrite. I use to edit Book3 sheet1 and sheet2 every month. Can you provide a code to accomplish my need? ANSWER: See if this fits your need: Private Sub Workbook_Open() Dim LastOpened As String LastOpened = Worksheets("Sheet1").Range("B1").Text If LastOpened = "" Then LastOpened = ThisWorkbook.BuiltinDocumentProperties("Last Save Time") End If If Month(Now()) > Month(CDate(LastOpened)) Then If MsgBox("New month. Would you like to copy the sheets?", _ vbQuestion + vbYesNo) = vbYes Then Workbooks.Open "c:\data\book1.xls" ThisWorkbook.Worksheets("Sheet1").Copy after: ActiveWorkbook.Worksheets (ActiveWorkbook.Worksheets.Count) ActiveSheet.Name = Format(Now, "mmm-yyyy") & "_1" ThisWorkbook.Worksheets("Sheet2").Copy after: ActiveWorkbook.Worksheets (ActiveWorkbook.Worksheets.Count) ActiveSheet.Name = Format(Now, "mmm-yyyy") & "_2" ActiveWorkbook.Close savechanges:=True Workbooks.Open "c:\data\book2.xls" ThisWorkbook.Worksheets("Sheet1").Copy after: ActiveWorkbook.Worksheets (ActiveWorkbook.Worksheets.Count) ActiveSheet.Name = Format(Now, "mmm-yyyy") & "_1" ThisWorkbook.Worksheets("Sheet2").Copy after: ActiveWorkbook.Worksheets (ActiveWorkbook.Worksheets.Count) ActiveSheet.Name = Format(Now, "mmm-yyyy") & "_2" ActiveWorkbook.Close savechanges:=True End If End If 'update date Worksheets("Sheet1").Range("B1") = CStr(Date) End Sub Note that you will hve to update workbook names and sheet names yourself. ---------- FOLLOW-UP ---------- QUESTION: Thanks Jan for quick reply. The code gives "Compile Error:Invalid use of property" and error points to ".Worksheets" in this line: ThisWorkbook.Worksheets("Sheet1").Copy after: ActiveWorkbook.Worksheets (ActiveWorkbook.Worksheets.Count) I pasted your code at "ThisWorkbook" code window in book3 and date are stored at sheet1 B1. book1, book2 and book3 are in c:\data\ ANSWER: Apologies, there is a typo in the code. It should read After:= instead of just After: (everywhere in the code) ---------- FOLLOW-UP ---------- QUESTION: Thats what i already tried but forgot to tell you. Still gives "Run-time error'9' Subscript out of range". I have Seen that copy is being made to current workbook(book3) instead of book1 and book2. Could you test the code before passing it to me?. Because i fell i am taking your valuable time.

• Answer:

  No, *I* am sorry for wasting your time! Apparently, my code does not work properly in the Thisworkbook module. Instead, insert a normal module and paste in this code: Sub DoCopy() Dim LastOpened As String LastOpened = Worksheets("Sheet1").Range("B1").Text If LastOpened = "" Then LastOpened = ThisWorkbook.BuiltinDocumentProperties("Last Save Time") End If If Month(Now()) > Month(CDate(LastOpened)) Then If MsgBox("New month. Would you like to copy the sheets?", _ vbQuestion + vbYesNo) = vbYes Then Workbooks.Open "c:\data\book1.xls" ThisWorkbook.Worksheets("Sheet1").Copy after:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count) ActiveSheet.Name = Format(Now, "mmm-yyyy") & "_1" ThisWorkbook.Worksheets("Sheet2").Copy after:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count) ActiveSheet.Name = Format(Now, "mmm-yyyy") & "_2" ActiveWorkbook.Close savechanges:=True Workbooks.Open "c:\data\book2.xls" ThisWorkbook.Worksheets("Sheet1").Copy after:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count) ActiveSheet.Name = Format(Now, "mmm-yyyy") & "_1" ThisWorkbook.Worksheets("Sheet2").Copy after:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count) ActiveSheet.Name = Format(Now, "mmm-yyyy") & "_2" ActiveWorkbook.Close savechanges:=True End If End If 'update date Worksheets("Sheet1").Range("B1") = CStr(Date) End Sub Now modify the Workbook_Open code in the ThisWorkbook module to: Private Sub Workbook_Open() DoCopy End Sub