Copy columns in a new workbook; Run-time error












0















At "New folder" I have the excel workbooks which will be opened by the loop; I want to copy 2 columns in each of these workbooks and paste it in another workbook called "new"



When I run the code I get the Run-time error '91': Object variable or With block variable not set



at line With wb.Worksheets(5) and only data of the first workbook are copied.



How can I fix it?



Option Explicit

Sub ProcessFiles()

Dim Filename, Pathname As String
Dim wb As Workbook
Dim wbMain As Workbook
Dim i As Integer

Set wbMain = Workbooks.Open("C:UsersADesktopVBAnew.xlsx")
Pathname = "C:UsersADesktopVBANew folder"

Filename = Dir(Pathname)
i = 1

Do While Filename <> ""
Set wb = Workbooks.Open(Pathname & Filename)
Enter_Formulas wb, wbMain, i

wb.Close SaveChanges:=True
Filename = Dir()
i = i + 2
Loop
End Sub


Sub Enter_Formulas(wb As Workbook, wbMain As Workbook, i)
With wb.Worksheets(5)
.Columns(1).Copy Destination:=wbMain.Worksheets(2).Columns(i)
.Columns(3).Copy Destination:=wbMain.Worksheets(2).Columns(i + 1)
End With
End Sub









share|improve this question



























    0















    At "New folder" I have the excel workbooks which will be opened by the loop; I want to copy 2 columns in each of these workbooks and paste it in another workbook called "new"



    When I run the code I get the Run-time error '91': Object variable or With block variable not set



    at line With wb.Worksheets(5) and only data of the first workbook are copied.



    How can I fix it?



    Option Explicit

    Sub ProcessFiles()

    Dim Filename, Pathname As String
    Dim wb As Workbook
    Dim wbMain As Workbook
    Dim i As Integer

    Set wbMain = Workbooks.Open("C:UsersADesktopVBAnew.xlsx")
    Pathname = "C:UsersADesktopVBANew folder"

    Filename = Dir(Pathname)
    i = 1

    Do While Filename <> ""
    Set wb = Workbooks.Open(Pathname & Filename)
    Enter_Formulas wb, wbMain, i

    wb.Close SaveChanges:=True
    Filename = Dir()
    i = i + 2
    Loop
    End Sub


    Sub Enter_Formulas(wb As Workbook, wbMain As Workbook, i)
    With wb.Worksheets(5)
    .Columns(1).Copy Destination:=wbMain.Worksheets(2).Columns(i)
    .Columns(3).Copy Destination:=wbMain.Worksheets(2).Columns(i + 1)
    End With
    End Sub









    share|improve this question

























      0












      0








      0








      At "New folder" I have the excel workbooks which will be opened by the loop; I want to copy 2 columns in each of these workbooks and paste it in another workbook called "new"



      When I run the code I get the Run-time error '91': Object variable or With block variable not set



      at line With wb.Worksheets(5) and only data of the first workbook are copied.



      How can I fix it?



      Option Explicit

      Sub ProcessFiles()

      Dim Filename, Pathname As String
      Dim wb As Workbook
      Dim wbMain As Workbook
      Dim i As Integer

      Set wbMain = Workbooks.Open("C:UsersADesktopVBAnew.xlsx")
      Pathname = "C:UsersADesktopVBANew folder"

      Filename = Dir(Pathname)
      i = 1

      Do While Filename <> ""
      Set wb = Workbooks.Open(Pathname & Filename)
      Enter_Formulas wb, wbMain, i

      wb.Close SaveChanges:=True
      Filename = Dir()
      i = i + 2
      Loop
      End Sub


      Sub Enter_Formulas(wb As Workbook, wbMain As Workbook, i)
      With wb.Worksheets(5)
      .Columns(1).Copy Destination:=wbMain.Worksheets(2).Columns(i)
      .Columns(3).Copy Destination:=wbMain.Worksheets(2).Columns(i + 1)
      End With
      End Sub









      share|improve this question














      At "New folder" I have the excel workbooks which will be opened by the loop; I want to copy 2 columns in each of these workbooks and paste it in another workbook called "new"



      When I run the code I get the Run-time error '91': Object variable or With block variable not set



      at line With wb.Worksheets(5) and only data of the first workbook are copied.



      How can I fix it?



      Option Explicit

      Sub ProcessFiles()

      Dim Filename, Pathname As String
      Dim wb As Workbook
      Dim wbMain As Workbook
      Dim i As Integer

      Set wbMain = Workbooks.Open("C:UsersADesktopVBAnew.xlsx")
      Pathname = "C:UsersADesktopVBANew folder"

      Filename = Dir(Pathname)
      i = 1

      Do While Filename <> ""
      Set wb = Workbooks.Open(Pathname & Filename)
      Enter_Formulas wb, wbMain, i

      wb.Close SaveChanges:=True
      Filename = Dir()
      i = i + 2
      Loop
      End Sub


      Sub Enter_Formulas(wb As Workbook, wbMain As Workbook, i)
      With wb.Worksheets(5)
      .Columns(1).Copy Destination:=wbMain.Worksheets(2).Columns(i)
      .Columns(3).Copy Destination:=wbMain.Worksheets(2).Columns(i + 1)
      End With
      End Sub






      excel vba excel-vba






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 21 '18 at 23:50









      M.JM.J

      51




      51
























          1 Answer
          1






          active

          oldest

          votes


















          0














          You are telling VBA to copy columns A and C from the 5th worksheet of the opened workbook. Seems like it doesn't have five or more sheets.






          share|improve this answer
























          • Yes, that is what I want to do so it has more than five sheets. But now I noticed that when a document with the same name as these worksheets is open, then it gives this error, otherwise it works ok.

            – M.J
            Nov 22 '18 at 0:53











          • You cannot have two documents with the same name opened at the same time.

            – Michal Rosa
            Nov 22 '18 at 0:54











          Your Answer






          StackExchange.ifUsing("editor", function () {
          StackExchange.using("externalEditor", function () {
          StackExchange.using("snippets", function () {
          StackExchange.snippets.init();
          });
          });
          }, "code-snippets");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "1"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53422048%2fcopy-columns-in-a-new-workbook-run-time-error%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          0














          You are telling VBA to copy columns A and C from the 5th worksheet of the opened workbook. Seems like it doesn't have five or more sheets.






          share|improve this answer
























          • Yes, that is what I want to do so it has more than five sheets. But now I noticed that when a document with the same name as these worksheets is open, then it gives this error, otherwise it works ok.

            – M.J
            Nov 22 '18 at 0:53











          • You cannot have two documents with the same name opened at the same time.

            – Michal Rosa
            Nov 22 '18 at 0:54
















          0














          You are telling VBA to copy columns A and C from the 5th worksheet of the opened workbook. Seems like it doesn't have five or more sheets.






          share|improve this answer
























          • Yes, that is what I want to do so it has more than five sheets. But now I noticed that when a document with the same name as these worksheets is open, then it gives this error, otherwise it works ok.

            – M.J
            Nov 22 '18 at 0:53











          • You cannot have two documents with the same name opened at the same time.

            – Michal Rosa
            Nov 22 '18 at 0:54














          0












          0








          0







          You are telling VBA to copy columns A and C from the 5th worksheet of the opened workbook. Seems like it doesn't have five or more sheets.






          share|improve this answer













          You are telling VBA to copy columns A and C from the 5th worksheet of the opened workbook. Seems like it doesn't have five or more sheets.







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 22 '18 at 0:16









          Michal RosaMichal Rosa

          1,3161814




          1,3161814













          • Yes, that is what I want to do so it has more than five sheets. But now I noticed that when a document with the same name as these worksheets is open, then it gives this error, otherwise it works ok.

            – M.J
            Nov 22 '18 at 0:53











          • You cannot have two documents with the same name opened at the same time.

            – Michal Rosa
            Nov 22 '18 at 0:54



















          • Yes, that is what I want to do so it has more than five sheets. But now I noticed that when a document with the same name as these worksheets is open, then it gives this error, otherwise it works ok.

            – M.J
            Nov 22 '18 at 0:53











          • You cannot have two documents with the same name opened at the same time.

            – Michal Rosa
            Nov 22 '18 at 0:54

















          Yes, that is what I want to do so it has more than five sheets. But now I noticed that when a document with the same name as these worksheets is open, then it gives this error, otherwise it works ok.

          – M.J
          Nov 22 '18 at 0:53





          Yes, that is what I want to do so it has more than five sheets. But now I noticed that when a document with the same name as these worksheets is open, then it gives this error, otherwise it works ok.

          – M.J
          Nov 22 '18 at 0:53













          You cannot have two documents with the same name opened at the same time.

          – Michal Rosa
          Nov 22 '18 at 0:54





          You cannot have two documents with the same name opened at the same time.

          – Michal Rosa
          Nov 22 '18 at 0:54


















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Stack Overflow!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53422048%2fcopy-columns-in-a-new-workbook-run-time-error%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          404 Error Contact Form 7 ajax form submitting

          How to know if a Active Directory user can login interactively

          TypeError: fit_transform() missing 1 required positional argument: 'X'