Announcement

Collapse
No announcement yet.

Copy Worksheet From One Workbook To Another Workbook

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Copy Worksheet From One Workbook To Another Workbook



    Hello all,

    First off, I'm *very* new to macros, so please go easy on me. Thanks!

    Here's the deal: I need a macro that copies one worksheet (from a workbook containing only 1 worksheet) named "CME Case Questions" and pastes into another (existing) workbook that has several worksheets already. The names of both workbooks will change from day to day, however. I then need both workbooks to be saved in the their respective directories..

    Thanks in advance,
    David

  • #2
    Re: Copy Worksheet From One Workbook To Another Workbook

    Code:
        ' put copy after last sheet
        Workbooks("CME Case Questions").Sheets(1).Copy _
        After:=Workbooks("OtherWorkBook").Sheets.Count
        
        ' put copy before first sheet
        Workbooks("CME Case Questions").Sheets("Sheet1").Copy _
        Before:=Workbooks("OtherWorkBook").Sheets(1)
        
        ' copy before (or after) some particular sheet
        Workbooks("CME Case Questions").Sheets("Sheet1").Copy _
        Before:=Workbooks("OtherWorkBook").Sheets("SheetName")
    Entia non sunt multiplicanda sine necessitate.

    Comment


    • #3
      Re: Copy Worksheet From One Workbook To Another Workbook

      Thanks--I appreciate the help, but is there a generic code for Workbooks("OtherWorkBook")?? The name of the workbook I will be copying to (and from) will be changing hourly..

      Is there any way I can use a macro to copy a worksheet from one active workbook into another inactive workbook, regardless of the workbook's name?

      Thanks again for the help!

      David

      Comment


      • #4


        Re: Copy Worksheet From One Workbook To Another Workbook

        is there a generic code for Workbooks("OtherWorkBook")??
        Use a variable:

        Code:
        Dim strWB as string
        ' open this month's workbook (e.g., Revenue_Apr07) 
        strWB = "Revenue_" & format(today, "mmmyy")
        Workbooks.Open(strWB)
        With Workbooks(strWB)
           .Sheet1.Range("A1") = Hello World"
           ...
        Entia non sunt multiplicanda sine necessitate.

        Comment

        Working...
        X