Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Writing an Excel 2010 macro with an IF statement to run other macros

Status
Not open for further replies.
DJWheezyWeez

DJWheezyWeez

Technical User
Jun 5, 2008
265
US
The basic idea is this... I have a blank spreadsheet with a macro that pulls variance information from other spreadsheets, copies it to the blank spreadsheet all on their own tab, makes pivot tables, etc etc. I have 4 tabs, one for the current variance as of the time I run the report to find it, one which holds the previous month's variance, a calculation tab to compare the two, and a final version tab that sorts out the differences into a more readable format.

What I'm trying to do now is write something that says if the month on the previous month tab is one less than the current month, then do nothing, else copy/paste information from a new spreadsheet to the previous month tab.

For example, let's say the previous month tab information is for February. When I run the macro, I want it to look at that month and see that February is 1 less then March (current month) and do nothing. Now let's say it's April and I run the macro. I want it to see that February is now 2 less than April and then open a new spreadsheet which contains end of March variances, copy and paste all that information into the previous month tab of the main spreadsheet overwriting the old information.

If this makes sense to anyone and it's not just rambling, does anyone have any suggestions? The way I'm thinking of this is something like if cell x-1 is equal to cell y then run macro a else run macro b.

Any help is greatly appreciated and thank you in advance :)

-DJWW
 
Sort by date Sort by votes


hi,

So what code do you have so far that you have a problem with? Please post your code, results, expectations, error messages etc. In other words, more specific illustrative information, please.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Here is my entire code. I made a note inside of it:
Code: 
Sub Create_Variance()


    Sheets("Variance").Select
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)"
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = "=YEAR(RC[-6])"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[6], ""mmmm"")"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=MONTH(RC[5])"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "OK"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("E2:Q2").Select
    Selection.Copy
    Range("E2:Q500").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
    Sheets("Var").Select
    Range("A1").Select


    Workbooks.Open Filename:="X:\SalesJournal\Variance.xls"
    Workbooks.Open Filename:="X:\SalesJournal\Variance-1.xls"
'    Here is where I'd like an if statement comparing two months and copy/pasting the information from Variance-1.xls to the Var-1 tab if they aren't 1 apart. Or everything below this could be a new macro and I could create another macro almost identical but it would NOT copy/paste the Variance-1 information. I would just need to write an if statement to run one of the two macros based on the comparison.
    Range("A1:Q5000").Select
    Selection.Copy
    Windows("Variance_Setup.xlsm").Activate
    Sheets("Var-1").Select
    Range("A1").Select
    ActiveSheet.Paste
    Windows("Variance.xls").Activate
    Range("A1:Q5000").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Variance_Setup.xlsm").Activate
    Sheets("Var").Select
    Range("A1").Select
    ActiveSheet.Paste
    Windows("Variance.xls").Activate
    Application.CutCopyMode = False
    ActiveWindow.Close
    Windows("Variance-1.xls").Activate
    ActiveWindow.Close
    Range("A1").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Var!R1C1:R1048576C17", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTableVar", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Var PT"
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTableVar")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
    With ActiveSheet.PivotTables("PivotTableVar").PivotFields("CustomerCode")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTableVar").PivotFields("ProdGroup")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTableVar").AddDataField ActiveSheet.PivotTables( _
        "PivotTableVar").PivotFields("Variance"), "Count of Variance", xlCount
    With ActiveSheet.PivotTables("PivotTableVar").PivotFields("Count of Variance")
        .Caption = "Sum of Variance"
        .Function = xlSum
    End With
    Range("A7").Select
    ActiveSheet.PivotTables("PivotTableVar").PivotFields("CustomerCode").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    Range("B7").Select
    ActiveSheet.PivotTables("PivotTableVar").PivotFields("ProdGroup").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    Sheets("Var-1").Select
    Range("A1").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Var-1!R1C1:R1048576C17", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet2!R3C1", TableName:="PivotTableVar-1", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Var-1 PT"
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTableVar-1")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
    With ActiveSheet.PivotTables("PivotTableVar-1").PivotFields("CustomerCode")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTableVar-1").PivotFields("ProdGroup")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTableVar-1").AddDataField ActiveSheet.PivotTables( _
        "PivotTableVar-1").PivotFields("Variance"), "Count of Variance", xlCount
    With ActiveSheet.PivotTables("PivotTableVar-1").PivotFields("Count of Variance")
        .Caption = "Sum of Variance"
        .Function = xlSum
    End With

    Range("A7").Select
    ActiveSheet.PivotTables("PivotTableVar-1").PivotFields("CustomerCode").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    Range("B7").Select
    ActiveSheet.PivotTables("PivotTableVar-1").PivotFields("ProdGroup").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)


    Sheets("Var PT").Select
    Columns("A:C").Select
    Selection.Copy
    Sheets("Calc").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Var-1 PT").Select
    Columns("A:C").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Calc").Select
    Range("E1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    Range("B:B,F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("B5").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]"
    Range("B5").Select
    Selection.Copy
    Range("G5").Select
    ActiveSheet.Paste

    Range("B6").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""", R[-1]C, RC[-1])"
    Range("B6").Select
    Selection.Copy


    Range("B6:B500").Select
    ActiveSheet.Paste
    Range("G6:G500").Select
    ActiveSheet.Paste

    Application.CutCopyMode = False
    Cells.Select
    Selection.Copy

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A4").Select
    Selection.Copy
    Range("B4").Select
    ActiveSheet.Paste
    Range("F4").Select
    Selection.Copy
    Range("G4").Select
    ActiveSheet.Paste
    Range("A:A,F:F").Select
    
    Selection.Delete Shift:=xlToLeft
    Range("A:A,E:E").Select
    Range("E1").Activate
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1], RC[2])"
    Range("A5").Select
    Selection.Copy
    Range("F5").Select
    ActiveSheet.Paste
    Range("A5").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("A5:A500"), Type:=xlFillDefault
    Range("A5:A500").Select
    Range("F5").Select
    Selection.AutoFill Destination:=Range("F5:F500"), Type:=xlFillDefault
    Range("F5:F500").Select

    Range("G4:I4").Select
    Selection.Copy
    Range("K4").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("M5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-12],C[-7]:C[-4], 4, FALSE)), RC[-9], IF(RC[-9]=VLOOKUP(RC[-12],C[-7]:C[-4], 4, FALSE), """", RC[-9]-VLOOKUP(RC[-12],C[-7]:C[-4], 4, FALSE)))"
    Range("L5").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[1]="""", """", RC[-9])"
    Range("K5").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[2]="""", """", RC[-9])"
    Range("K5:M5").Select
    Selection.AutoFill Destination:=Range("K5:M500"), Type:=xlFillDefault
    Range("K5:M500").Select

    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("K5:M500").Select
    Selection.Copy
    Sheets("Variance").Select
    Range("A2").Select
    ActiveSheet.Paste
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[1]="""", x, IF(RC[1]=""Grand Total"", x, 0))"
    Range("A1").Select
    Selection.AutoFill Destination:=Range("A1:A500"), Type:=xlFillDefault
    Range("A1:A500").Select
    Range("A1").Select
    Selection.SpecialCells(xlCellTypeFormulas, 16).Select
    Selection.EntireRow.Delete
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft

    Range("A2:A500").Select
    Selection.Cut
    Range("H2").Select
    ActiveSheet.Paste
    Range("C2:C500").Select
    Selection.Cut
    Range("P2").Select
    ActiveSheet.Paste
    Range("B2:B500").Select
    Selection.Cut
    Range("C2").Select
    ActiveSheet.Paste

    Range("D2").Select
    Workbooks.Open Filename:="X:\SalesJournal\CustomerList.xls"
    Workbooks.Open Filename:="X:\SalesJournal\CustomerSalespersonList.xls"
    Workbooks.Open Filename:="X:\SalesJournal\SalespersonList.xls"
    
    Windows("Variance_Setup.xlsm").Activate
    
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[4], [CustomerList.xls]Sheet1!C1:C2, 2, FALSE)"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D500"), Type:=xlFillDefault
    Range("D2:D500").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[7], [CustomerSalespersonList.xls]Sheet1!C1:C2, 2, FALSE)"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[6], [CustomerSalespersonList.xls]Sheet1!C1:C3, 3, FALSE)"
    Range("A2:B2").Select
    Selection.AutoFill Destination:=Range("A2:B500"), Type:=xlFillDefault
    Range("A2:B500").Select
    Range("A:A").Select
    Selection.SpecialCells(xlCellTypeFormulas, 16).Select
    Selection.EntireRow.Delete
    Range("A1").Select

    Cells.Select
    Selection.Copy
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A4").Select
    Application.CutCopyMode = False
    Range("A1").Select

    Windows("CustomerList.xls").Activate
    ActiveWindow.Close
    Windows("CustomerSalespersonList.xls").Activate
    ActiveWindow.Close
    Windows("SalespersonList.xls").Activate
    ActiveWindow.Close

End Sub
 
Upvote 0 Downvote


You really did not answer ALL the questions I posted.

You apparently have a sheet with tabs representing each month.

I do not particularly recommend chopping data up into arbitrary chunks, as it makes analyzing and reporting the data much more difficult. I would keep ALL data in one sheet, and then REPORT on one sheet, any summary requested using a PivotTable or MS Query or aggregations formulas.

That being said, "February" is not one less than "March". In fact "2" is not one less than "3", but it is true that 2 is one less than 3. But what happens when you get to December and January? It seems that you are not REALLY referring to February. Rather February of a specific year. So it might be a better tab naming strategy to use a yyyy-mm name structure, like "2012-02" and "2012-03" as tab names for your sheets and then the comparison is to find a sheet name like this.
Code: 
function NewSheet() as boolean
   dim ws as worksheet, bFound as boolean
 
   bfound = true

   for each ws in thisworkbook.worksheets
      if ws.name = format(date, "yyyy-mm") then
         bfound = false
         exit for
      end if
   next
end function
So NewSheet returns FALSE when a sheet exists for the current month. You would use it like this...
Code: 
  if NewSheet() then
     'here's where you call whatever procedures to add a sheet etc.

  end if


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Skip, I guess you missed one line:
next
[!]NewSheet = bfound[/!]
end function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote


So it might be better to code...
Code: 
function NewSheet() as boolean
   dim ws as worksheet
 
   NewSheet = true

   for each ws in thisworkbook.worksheets
      if ws.name = format(date, "yyyy-mm") then
         NewSheet = false
         exit for
      end if
   next
end function

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
If you would prefer to not change your tab naming scheme as others have suggested, the following macro uses the tab index number and cell range to determine the month on the relevant sheets and then runs the macro anytime the difference between the two months is not less than 2. This code relies upon your latest tab being the one the one furthest left, and the next previous one being one tab to the right of that. If your sheets are not in those fixed position but are always a given numbers to the right of the first sheet, change sheets(1) and sheets(2) to the correct index number for your two latest sheets. If your most recent sheet is all the way to the right of your tabs and the previous month sheet is one tab to the left, replace "Sheets(1)" with "Sheets(Worksheets.count)" and Sheets(2) with "Sheets(worksheets.count -1)"

Here is the code:

Sub CheckMonth()
Dim MyMonth As Boolean
Dim Month1 As Integer
Dim Month2 As Integer
'edit the range references below to refer to the cell on your report that contains the date of the report.
Month1 = Month(Sheets(1).Range("$A$1").Value)
Month2 = Month(Sheets(2).Range("$A$1").Value)
If Month2 > Month1 Then Month2 = Month2 - 12
MyMonth = Month1 - Month2 < 2
If MyMonth = True Then
'do nothing, or other option
Else
Create_Variance
End If
End Sub
 
Upvote 0 Downvote

so using just MONTH, no big deal!
Code: 
function NewSheet() as boolean
   dim ws as worksheet
 
   NewSheet = true

   for each ws in thisworkbook.worksheets
      if ws.name = format(date, "[b][highlight]mmmm[/highlight][/b]") then
         NewSheet = false
         exit for
      end if
   next
end function

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Combine two working macros into one for MS Word 1
Replies
7
Views
856
strongm
strongm
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
1K
remeng
remeng
remeng
  • Locked
  • Question
Excel VBA to Pass Var between file macros after var changes
Replies
13
Views
555
mikrom
mikrom
remeng
  • Locked
  • Question
If obsolete - deactivate all but one macro
Replies
6
Views
369
mintjulep
M
remeng
  • Locked
  • Question
Run PowerShell Commands from Excel VBA
Replies
14
Views
3K
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top