Announcement

Collapse
No announcement yet.

Copy a standard sheet to a constantly changing workbook.

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

  • Copy a standard sheet to a constantly changing workbook.



    I want to be able to go to open a workbook that has a bunch of data in it. I want to run a macro that gets a standard form from another workbook and paste that sheet into the active work book. My hangup is that each book with data in it has a different name. I have tried


    Code:
    Sub Get_Diagram()
    '
    ' Get_Diagram Macro
    '
    
    Dim W As Workbook
    Set W = ActiveWorkbook
    
    W.Activate
    Windows("diagram macros.xlsm").Activate
    Sheets("Blank Diagram").Select
    ActiveSheet.Copy Before:=Workbooks(W).Sheets(1)
    'Sheets("Blank Diagram").Copy Before:=Workbooks().
    'Sheets (1)
    
    WB.Activate
    
    End Sub

    Thanks
    Last edited by AlanSidman; October 6th, 2018, 11:16.

  • #2
    Code Tags Added
    Your post does not comply with our Forum RULES. Use code tags around code.

    Posting code between tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window.

    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)
    Alan

    Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
    FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

    If someone has helped you, say "thank you" by clicking on the Like Button.

    Comment


    • #3
      Hello, does this code work?

      Assuming that your "standard form from another workbook" has the worksheet name "Blank Diagram". (if not, you can change the name in red below)

      Sub Get_Diagram()

      Code:
      Dim W As Workbook
      Dim X As Workbook
      Dim WS As Worksheet
      
      Set W = ActiveWorkbook
      
      For Each X In Workbooks
          If Not X Is W Then
              For Each WS In X.Worksheets
                  If WS.Name = "Blank Diagram" Then
                      WS.Copy After:=W.Worksheets(W.Worksheets.Count)
                  End If
              Next WS
          End If
      Next X
      
      End Sub

      Comment


      • #4


        Hello,

        There are two possibilities :

        1. You keep a complete List of your Workbook names

        or

        2. You store all these Target Workbooks ( with changing names ) in a dedicated Folder

        Then, it is only a matter of building a Loop ...

        Hope this will help
        If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

        Comment

        Working...
        X