How to output the result from existing sheets to new worksheet

ryan8200

Active Member
Joined
Aug 21, 2011
Messages
299
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.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,661
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

Code:
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,661
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,099,113
Messages
5,466,752
Members
406,496
Latest member
Arlind Elezi

This Week's Hot Topics

Top