+ Reply to Thread
Results 1 to 15 of 15

Combine 2 functions into 1 macro (VBA)

Hybrid View

  1. #1
    Registered User
    Join Date
    06-17-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2007
    Posts
    14

    Combine 2 functions into 1 macro (VBA)

    I have a macro that does exactly what I need to do namely transpose some data from one sheet and copy it to an other sheet within the workbook.

    Sub Transpose()
        Sheets("Inhoudelijke_Metadata").Select
        		Range("A2:B5").Select
        			Application.CutCopyMode = False
        			Selection.Copy
       		 	Sheets("Technische_Metadata").Select
       	 	Range("K3").Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    End Sub
    I have a macro that loops true several excel files in one directory (open/close) them to perform an action. That code does exactly that namely open en close every workbook in that directoy.

    Sub RunCodeOnAllXLSFiles()
    Dim lCount As Long
    Dim wbResults As Workbook
    Dim wbCodeBook As Workbook
    
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    
    On Error Resume Next
        Set wbCodeBook = ThisWorkbook
            With Application.FileSearch
                .NewSearch
                'Change path to suit
                .LookIn = "C:\MyDocuments\TestResults"
                .FileType = msoFileTypeExcelWorkbooks
                'Optional filter with wildcard
                '.Filename = "Book*.xls"
                    If .Execute > 0 Then 'Workbooks in folder
                        For lCount = 1 To .FoundFiles.Count 'Loop through all
                            'Open Workbook x and Set a Workbook variable to it
                            Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                            
                            'DO YOUR CODE HERE
    
                            wbResults.Close SaveChanges:=False
                        Next lCount
                    End If
            End With
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    End Sub
    My question is following;
    How to consolidate the two functions into one macro.

    Thanks for helping me.
    Last edited by NBVC; 09-09-2010 at 08:22 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    06-17-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003,excel 2007
    Posts
    678

    Re: Combine 2 functions into 1 macro (VBA)

    If the macros are independent try another macro in which these macros are embedded

    sub finalmacro()
    Transpose
    RunCodeOnAllXLSFiles
    end sub
    I could not test this.

  3. #3
    Registered User
    Join Date
    06-17-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Combine 2 functions into 1 macro (VBA)

    Thanks, ill try it and let you know...
    Tried it but it does not work, it gives an error on the transpose part, strange because in standalone it works!! I did swap transpose 1 line down because it had to open the workbook first.
    Last edited by romperstomper; 09-09-2010 at 08:03 AM.

  4. #4
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Combine 2 functions into 1 macro (VBA)

    Sub snb()
       sheets("Technische_Metadata").Range("K3").resize(2,4)=worksheetfunction.transpose(Sheets("Inhoudelijke_Metadata").Range("A2:B5").value)
        With Application.FileSearch
           .LookIn = "C:\MyDocuments\TestResults"
           .FileType = msoFileTypeExcelWorkbooks
           For j=1  To .Execute 
               with workbooks.open(.foundfiles(j))
                 .sheets(1).cells(1,1)="gevonden !"
                .Close true
               end with 
           Next
        End With
    End Sub

  5. #5
    Registered User
    Join Date
    06-17-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Combine 2 functions into 1 macro (VBA)

    Thanks snb, clean code to do just what i want. Got an error.
    It seems to open the files 1 by 1 but then i receive an error. Only last workbook in directory got changed. Than i received a out of reach error on this line "with workbooks.open(.foundfiles(j))"

    Ill reply tomorrow cause i have a meeting.
    Last edited by romperstomper; 09-09-2010 at 08:03 AM.

  6. #6
    Registered User
    Join Date
    06-17-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Combine 2 functions into 1 macro (VBA)

    i have attached a file...
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-17-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Combine 2 functions into 1 macro (VBA)

    snb,

    It seems to change the open workbook only. Other workbooks open but the copy/transpose don't work .
    I would like the script to open each workbook and do the copy/transpose and close that workbook again for each workbook in the directory.

    thx

  8. #8
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Combine 2 functions into 1 macro (VBA)

    It's very naughty to cross post without mentioning it.

    http://www.ozgrid.com/forum/showthread.php?t=145990

    Can you see why? I was looking at it at Ozgrid, snb is looking at it here.

  9. #9
    Registered User
    Join Date
    06-17-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Combine 2 functions into 1 macro (VBA)

    so! is it forbidden to ask for help on different forums?.
    neither solution given solve my problem at present but i like the way i learn from you guy's.
    Oh, next time i mention that i seek help on the different forums.
    Last edited by romperstomper; 09-09-2010 at 08:03 AM.

  10. #10
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Combine 2 functions into 1 macro (VBA)

    I would like the script to open each workbook and do the copy/transpose and close that workbook again for each workbook in the directory.
    That's a complete new question.
    Sub snb()
      c01="C:\MyDocuments\TestResults\"
      c02=dir(c01 & "*.xls")
      Do Until c02=""
        With workbooks.open(c01 & c02)
          .sheets("Technische_Metadata").Range("K3").resize(2,4)=worksheetfunction.transpose(.Sheets("Inhoudelijke_Metadata").Range("A2:B5").value)
          .Close true
        End With 
        c02 = Dir
      Loop
    End Sub

  11. #11
    Registered User
    Join Date
    06-17-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Combine 2 functions into 1 macro (VBA)

    bingo, solution given works perfect except for a little error at the end of the cycle. All workbooks reflect the transpose and copy just as i wanted. Thank you very much snb.
    Last edited by romperstomper; 09-09-2010 at 08:03 AM.

  12. #12
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Combine 2 functions into 1 macro (VBA)

    so! is it forbidden to ask for help on different forums?.
    No, it is not forbidden but if you read the rules you'll see it asks that you mention you have done this.

    You had two of us spending time on this question - had I not known it had been solved here I would have merrily carried on at Ozgrid wasting my time.

  13. #13
    Registered User
    Join Date
    06-17-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Combine 2 functions into 1 macro (VBA)

    I failed to read the rules thats true, yet i did learn from your samples so it was sure not waisting time from your part. next time i mention posting a question on diff; boards/forums.
    Last edited by romperstomper; 09-09-2010 at 08:02 AM.

  14. #14
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: [solved]Combine 2 functions into 1 macro (VBA)

    That's fine. I don't want to come across as some whingeing old git. Glad you got a solution, I don't think I interpreted your question quite right as it happens.

  15. #15
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,984

    Re: [solved]Combine 2 functions into 1 macro (VBA)

    dversa,
    Please don't quote entire posts every time you reply to them - it serves no purpose, unless the post is not the one immediately above your reply, or if you wish to address a particular part of that post. Use the Reply button instead.
    Everyone who confuses correlation and causation ends up dead.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1