How to output the result from existing sheets to new worksheet

[ryan8200]

How to output the result from existing sheets to new worksheet ?

What is the code in need to add in ?


[Sub MissingHobbies()Dim c As Integer, h As Integer, ans As Integer
ans = 9
For c = 2 To 11 ' candidates
For h = 3 To 6 ' hobbies
Cells(8, 1) = "Hobbies"
Cells(8, 2) = "Candidates"
If Cells(h, c) = "N" Then
Cells(ans, 1) = Cells(h, 1)
Cells(ans, 2) = Cells(1, c)
ans = ans + 1
Else
End If
Next h
Next c
End
End End Sub][/CODE]

Your help will be greatly appreciated. Thanks.

 

[sijpie]

If the output table is in a different workbook then the easiest is to set a workbook object to that workbook. To do it correctly and catch errors makes it a bit more complicated

Sub MissingHobbies()

    Dim iC As Integer, iH As Integer, iAns As Integer
    Dim wbOutp As Workbook, wbThis As Workbook
    Dim wsOutp As Worksheet, wsSource As Worksheet
    Const sWBOutpName As String = "YourOutput.xlsx" '<<<< Modify destination file name
    Const sWSName As String = "Sheet1"              '<<<< Modify destination sheet name
    Dim sPath As String
    
    Set wbThis = ThisWorkbook
    Set wsSource = wbThis.ActiveSheet
    sPath = wbThis.Path
    'sPath = C:\MyDocs\123          '<<<< Modify and uncomment if the output file _
                                          is in a different folder than this workbook
    
    
    'Check if output workbook is open
    On Error Resume Next        'capture error in case not open
    Set wbOutp = Workbooks(sWBOutpName)
    On Error GoTo 0             ' go back to normal error behaviour
    
    'check if open, else open it
    If wbOutp Is Nothing Then   'not set, file is closed
        Set wbOutp = Workbooks.Open(sPath & "\" & sWBOutpName)
    End If
    If wbOutp Is Nothing Then   'not set, couldn't open file
        MsgBox prompt:="Could not find file:" & vbLf & _
                sPath & "\" & sWBOutpName, Buttons:=vbOKOnly + vbCritical
        Exit Sub
    End If
    'Output file is open, store dat there
    Set wsOutp = wbOutp.Sheets(sWSName)
    
    iAns = 9
    For iC = 2 To 11 ' candidates
        For iH = 3 To 6 ' hobbies
            wsSource.Cells(8, 1) = "Hobbies"
            wsSource.Cells(8, 2) = "Candidates"
            If wsSourceCells(h, c) = "N" Then
                wsOutp.Cells(iAns, 1) = wsSource.Cells(iH, 1)
                wsOutp.Cells(iAns, 2) = wsSource.Cells(1, iC)
                iAns = iAns + 1
            Else
            End If
        Next iH
    Next iC
    
    'Cleanup
    Set wbOutp = Nothing
    Set wbThis = Nothing
    Set wsOutp = Nothing
    Set wsSource = Nothing
    
End Sub

 

[sijpie]

Also note that I always use a capital letter when declaring my variables. Where you use c, h, ans, i use iC, iH and iAns. When I type my variables I don't use the capital, because the VBA editor should change it automatically. if it didn''t change then I made a typo!


I also use a type leter in front of the variable (iC for integer, sName for string, wbThis for workbook. That way it is ieasier to check that i am using a correct variable, and with larger code it makes it easier to follow what the code is doing, next year when you are trying to understand it...