No announcement yet.

Copy a standard sheet to a constantly changing workbook.

  • 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

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

    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.)

    Did you debug your code first?
    FORUM RULES----->

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


    • #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()

      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


      • #4


        There are two possibilities :

        1. You keep a complete List of your Workbook names


        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 ...