Keep getting "Run-time error '91' Object variable or With block variable not set"
I put in all the comments I could to help explain what Im trying to do here. If anyone needs to see either sheet, ask and I will attach them.
Thanks.
Code
Sub BuildDiscrete()
Dim c As Range 'Loop variable for total lines
Dim i As Integer 'Loop variable for discrete creation
Dim wb As Workbook 'Workbook of current discrete in creation
Dim Diswb As Workbook 'Workbook containing all values to create with
Dim todate As String 'Date to use in file saving/opening
Dim Foldername As String 'Pathname where files saved
Dim Filename As String 'Name to save wb
Dim OsiNum As String 'OSI number of current line
todate = Format(Today, "mmddyy") 'Format Today() as specified
Diswb = ActiveWorkbook.Name 'Set Diswb
Foldername = "V:\SHARED\COMMON.P13\PROGRAMS\GE\Kelli\My Documents\DISCRETES\DISCRETES FOR 2005\"
For Each c In Range("G:G") 'Loops using each value in row G
OsiNum = Workbooks(Diswb).Sheets(1).Cells(G, c).Value 'Sets OsiNum for current line
Filename = "DISCRETE" & "_" & todate & "_" & OsiNum & ".xls" 'Creates filename to be used
If IsWorkbookOpen(Foldername & Filename) Then 'Check if Filename is already created
wb = Filename 'Pointless line, wb already equals Filename
Else
wb.Save 'Save discrete that was created
wb.Close False 'Close workbook, changes just saved
Workbooks.Open "V:\SHARED\COMMON.P13\PROGRAMS\GE\Kelli\My Documents\DISCRETES\DISCRETE_CHANGES_OSI Form.xls" 'Blank form for new Discrete
ActiveWorkbook.SaveAs Foldername & Filename 'Saves under proper filename
wb = ActiveWorkbook.Name 'Sets to active book
Workbooks(wb).Sheets(1).Cells(D5).Value = Workbooks(Diswb).Sheets(1).Cells(F, c).Value 'Cells filled in upon creation
Workbooks(wb).Sheets(1).Cells(D6).Value = Workbooks(Diswb).Sheets(1).Cells(G, c).Value 'Cells filled in upon creation
End If
For i = 15 To 27 'Lines where data is entered
If Workbooks(wb).Sheets(1).Cells(B, i).Value <> "" Then 'If line is empty insert there
Workbooks(wb).Sheets(1).Cells(B, i).Value = Workbooks(Diswb).Sheets(1).Cells(J, c).Value 'All these are data insertions
Workbooks(wb).Sheets(1).Cells(c, i).Value = Workbooks(Diswb).Sheets(1).Cells(A, c).Value
Workbooks(wb).Sheets(1).Cells(D, i).Value = Workbooks(Diswb).Sheets(1).Cells(E, c).Value
Workbooks(wb).Sheets(1).Cells(E, i).Value = Workbooks(Diswb).Sheets(1).Cells(i, c).Value
Workbooks(wb).Sheets(1).Cells(F, i).Value = Workbooks(Diswb).Sheets(1).Cells(B, c).Value
Workbooks(wb).Sheets(1).Cells(G, i).Value = Workbooks(Diswb).Sheets(1).Cells(c, c).Value
Workbooks(wb).Sheets(1).Cells(K, i).Value = Workbooks(Diswb).Sheets(1).Cells(A, c).Value
Workbooks(wb).Sheets(1).Cells(L, i).Value = Workbooks(Diswb).Sheets(1).Cells(E, c).Value
Workbooks(wb).Sheets(1).Cells(M, i).Value = Workbooks(Diswb).Sheets(1).Cells(i, c).Value
Workbooks(wb).Sheets(1).Cells(N, i).Value = Workbooks(Diswb).Sheets(1).Cells(B, c).Value
Workbooks(wb).Sheets(1).Cells(O, i).Value = Workbooks(Diswb).Sheets(1).Cells(D, c).Value
End If 'No else, loop until empty is found
'Need to insert code here that of no empty was found, saveas samename_1, delete current, create samename_2, etc...
'This isnt that important at the time because its very rare situation, mainly want current code to work
Next
Next
End Sub
Function IsWorkbookOpen(stName As String) As Boolean 'Checks for workbook being opened
Dim Wkb As Workbook
On Error Resume Next ' in case it isn't open
Set Wkb = Workbooks(stName) 'Sets Wkb to Workbook sent through function call
If Not Wkb Is Nothing Then IsWorkbookOpen = True 'True is workbook is open
End Function