Using Variables between Module and User Form

[rbirch]

Hello,
I'm trying to create a macro that will copy and paste values from several workbooks into one sheet and create a chart. With the command button, I would like it always to use just the range and loop through the workbook's sheets. However, when trying to use the code I get an runtime error of 91 which says, "With block variable or object variable not set". The only way I could think of solving this would be to add the chart_magic macro into the command button, but that seems kind of ugly. There's got to be a better way.

Sub CommandButton1_Click()
Dim DW_Pending_cell As Range
Dim DW_Approved_cell As Range
Dim DW_KSO_Goal_cell As Range
Dim DW_Percent_KSO_cell As Range
Dim ITF_Pending_cell As Range
Dim ITF_Approved_cell As Range
Dim ITF_KSO_Goal_cell As Range
Dim ITF_Percent_KSO_cell As Range

If two_charts.OptionButton1.Value = True Then
    DW_Pending_cell = Range("C4")
    DW_Approved_cell = Range("D4")
    DW_KSO_Goal_cell = Range("F4")
    DW_Percent_KSO_cell = Range("G4")
    ITF_Pending_cell = Range("I4")
    ITF_Approved_cell = Range("J4")
    ITF_KSO_Goal_cell = Range("K4")
    ITF_Percent_KSO_cell = Range("L4")

Call charts_magic
End If
End Sub

Public Sub charts_magic()
Public DW_Pending As Integer
Public I As Integer
Public DW_Approved As Integer
Public KSO As Integer
Public KSOP As Integer
Public workweek_dw As Integer
Public workweek_itf As Integer
Public ITF_Approved As Integer
Public ITF_Pending As Integer
workweek_dw = 2
workweek_itf = 2
DW_Pending = 2
DW_Approved = 2
ITF_Pending = 2
ITF_Approved = 2
KSO_DW_dollars = 2
KSO_DWPercents = 2
KSO_ITF_dollars = 2
KSO_ITFPercents = 2





ThisWorkbook.Sheets("Chart").Cells(1, 1) = "Work Week"
ThisWorkbook.Sheets("Chart").Cells(1, 2) = "DW Pending"
ThisWorkbook.Sheets("Chart").Cells(1, 3) = "DW Approved"
ThisWorkbook.Sheets("Chart").Cells(1, 4) = "KSO [$k]"
ThisWorkbook.Sheets("Chart").Cells(1, 5) = "% to KSO"

For I = 2 To 12
    ThisWorkbook.Worksheets("Chart").Cells(workweek_dw, 1).Value = "ww" & workweek_dw
    workweek_dw = workweek_dw + 1
Next I
For I = 2 To 12
    ThisWorkbook.Worksheets("Chart").Cells(DW_Pending, 2).Value = ThisWorkbook.Worksheets("2015ww" & I).Range(DW_Pending_cell).Value
    DW_Pending = DW_Pending + 1
Next I
For I = 2 To 12
    ThisWorkbook.Worksheets("Chart").Cells(DW_Approved, 3).Value = ThisWorkbook.Worksheets("2015ww" & I).Range(DW_Approved_cell).Value
    DW_Approved = DW_Approved + 1
Next I
For I = 2 To 12
    ThisWorkbook.Worksheets("Chart").Cells(KSO_DW_dollars, 4).Value = ThisWorkbook.Worksheets("2015ww" & I).Range(DW_KSO_Goal_cell).Value
    KSO_DW_dollars = KSO_DW_dollars + 1
Next I
For I = 2 To 12
    ThisWorkbook.Worksheets("Chart").Cells(KSO_DWPercents, 5).Value = ThisWorkbook.Worksheets("2015ww" & I).Range(DW_Percent_KSO_cell).Value
    KSO_DWPercents = KSO_DWPercents + 1
Next I

ThisWorkbook.Sheets("Chart").Cells(1, 7) = "Work Week"
ThisWorkbook.Sheets("Chart").Cells(1, 8) = "ITF Pending"
ThisWorkbook.Sheets("Chart").Cells(1, 9) = "ITF Approved"
ThisWorkbook.Sheets("Chart").Cells(1, 10) = "KSO [$k]"
ThisWorkbook.Sheets("Chart").Cells(1, 11) = "% to KSO"


For I = 2 To 12
    ThisWorkbook.Worksheets("Chart").Cells(workweek_itf, 7).Value = "ww" & workweek_itf
    workweek_itf = workweek_itf + 1
Next I

For I = 2 To 12
    ThisWorkbook.Worksheets("Chart").Cells(ITF_Pending, 8).Value = ThisWorkbook.Worksheets("2015ww" & I).Range(ITF_Pending_cell).Value
    ITF_Pending = ITF_Pending + 1
Next I
For I = 2 To 12
    ThisWorkbook.Worksheets("Chart").Cells(ITF_Approved, 9).Value = ThisWorkbook.Worksheets("2015ww" & I).Range(ITF_Approved_cell).Value
    ITF_Approved = ITF_Approved + 1
Next I
For I = 2 To 12
    ThisWorkbook.Worksheets("Chart").Cells(KSO_ITF_dollars, 10).Value = ThisWorkbook.Worksheets("2015ww" & I).Range(ITF_KSO_Goal_cell).Value
    KSO_ITF_dollars = KSO_ITF_dollars + 1
Next I
For I = 2 To 12
    ThisWorkbook.Worksheets("Chart").Cells(KSO_ITFPercents, 11).Value = ThisWorkbook.Worksheets("2015ww" & I).Range(ITF_Percent_KSO_cell).Value
    KSO_ITFPercents = KSO_ITFPercents + 1
Next I
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range("Chart!$G$1:$K$12")
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range("Chart!$A$1:$E$12")
 
    
End Sub

[MarvinP]

Hi rbirch,

Without reading all your code, it sounds like you need a Public Global Variable that works until the workbook is closed. See:

http://www.ozgrid.com/VBA/variable-scope-lifetime.htm

http://www.excel-easy.com/vba/exampl...ble-scope.html