Results 1 to 4 of 4

Seeking Guidance/ Help with my Macros/VBA Code (File name changes with what date it is)

Threaded View

  1. #1
    Registered User
    Join Date
    07-17-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    7

    Seeking Guidance/ Help with my Macros/VBA Code (File name changes with what date it is)

    Hello All, BIG time newbie here to the macros/VBA world so bear with my lack of sufficient knowledge. I have to do an inventory report everyday for work that is constantly changing. Multiple problems:
    1) I do not know how to make an array stop at the last row (there will never be the same amount of rows on any given day). Example:
    Selection.AutoFill Destination:=Range("C2:C304") ---> this needs to be Range("C2:Last row") and i do not know what that specific code is. There are multiple instances in the code where I need to change it to be the last row and not a specific cell.
    2)I will like to delete just the last four rows of the BB0716 file sheet 4 (the pivot table) in E281:F284 . Again, this will not always be its place holder as it could be higher or lower in the excel sheet but ALWAYS the last 4
    3) I will always have an excel window open with the two excel workbooks open of RBCxxxx and BBxxxx that will look like the base models attached. My problem is how do i make 1 macro to reference a file that changes names constantly? For instance tomorrow will be rbc0717 next day rbc 0718 etc. The file is always saved as the previous WORKING (M-F) day's date.
    4) At the end of this code i am not done with my process, i just don't have a clue what to do next. This is what i need to do:

    Sub positions()
    '
    ' positions Macro
    '
    ' Keyboard Shortcut: Ctrl+p
    '
        Range("A1").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Sheets.Add
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "FTR23_Daily_Inventory_Report!R1C1:R304C2", Version:=xlPivotTableVersion10). _
            CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable6" _
            , DefaultVersion:=xlPivotTableVersion10
        Sheets("Sheet1").Select
        Cells(3, 1).Select
        With ActiveSheet.PivotTables("PivotTable6").PivotFields("CUSIP")
            .Orientation = xlRowField
            .Position = 1
        End With
        ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
            "PivotTable6").PivotFields("Net Position"), "Sum of Net Position", xlSum
        Range("A5").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Selection.End(xlUp).Select
        Range("E5").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("E5").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range("E5").Select
        Selection.End(xlDown).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Application.CutCopyMode = False
        Selection.ClearContents
        Selection.End(xlUp).Select
        Selection.End(xlUp).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets("Sheet2").Select
        Sheets("Sheet2").Name = "RBCvBB"
        Range("B4").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("B3").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "CUSIP"
        Range("C3").Select
        ActiveCell.FormulaR1C1 = "RBC"
        Range("D3").Select
        ActiveCell.FormulaR1C1 = "BB"
        Range("E3").Select
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        With Selection
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        ActiveCell.FormulaR1C1 = "VAR"
        Range("D4").Select
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets("Sheet3").Select
        Sheets("Sheet3").Name = "BBvRBC"
        Range("B4").Select
        ActiveWindow.WindowState = xlMinimized
        Windows("bb0716.xls").Activate
        ActiveWindow.WindowState = xlMaximized
        Columns("A:B").Select
        Selection.SpecialCells(xlCellTypeBlanks).Select
        Selection.EntireRow.Delete
        Range("A227").Select
        Selection.End(xlUp).Select
        Range("A1").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Sheets.Add
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Sheet1!R1C1:R289C2", Version:=xlPivotTableVersion10).CreatePivotTable _
            TableDestination:="Sheet4!R3C1", TableName:="PivotTable7", DefaultVersion _
            :=xlPivotTableVersion10
        Sheets("Sheet4").Select
        Cells(3, 1).Select
        With ActiveSheet.PivotTables("PivotTable7").PivotFields("CusipNumber")
            .Orientation = xlRowField
            .Position = 1
        End With
        ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _
            "PivotTable7").PivotFields("CurrentNetPosition"), "Sum of CurrentNetPosition", _
            xlSum
        Range("A5").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Selection.End(xlUp).Select
        Range("E5").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("E5").Select
        Selection.End(xlDown).Select
        Range("H282").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "delete last four fows"
        Range("E284").Select
        Selection.End(xlUp).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        ActiveWindow.WindowState = xlMinimized
        Windows("rbc0716.xls").Activate
        ActiveWindow.WindowState = xlMaximized
        Range("B4").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("B3").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "CUSIP"
        Range("C3").Select
        ActiveCell.FormulaR1C1 = "BB"
        Range("D3").Select
        ActiveCell.FormulaR1C1 = "RBC"
        Range("E3").Select
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        With Selection
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        ActiveCell.FormulaR1C1 = "VAR"
        Range("D4").Select
        Sheets("RBCvBB").Select
        ActiveCell.FormulaR1C1 = _
            "=VLOOKUP(RC[-2],[bb0716.xls]Sheet4!R5C5:R284C6,2,FALSE)"
        Range("D4").Select
        Selection.AutoFill Destination:=Range("D4:D280")
        Range("D4:D280").Select
        Range("E4").Select
        ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
        Range("E4").Select
        Selection.AutoFill Destination:=Range("E4:E280")
        Range("E4:E280").Select
        Range("B3").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        ActiveWorkbook.Worksheets("RBCvBB").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("RBCvBB").Sort.SortFields.Add Key:=Range("E4:E280") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("RBCvBB").Sort
            .SetRange Range("B3:E280")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("F10").Select
        Sheets("BBvRBC").Select
        Range("D4").Select
        ActiveWindow.WindowState = xlMinimized
        Windows("bb0716.xls").Activate
        ActiveWindow.WindowState = xlMaximized
        Range("E284").Select
        Selection.End(xlUp).Select
        Range("E5").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        ActiveWindow.WindowState = xlMinimized
        Windows("rbc0716.xls").Activate
        ActiveWindow.WindowState = xlMaximized
        Range("D4").Select
        Range("D4").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet1!R5C5:R281C6,2,FALSE)"
        Range("D4").Select
        Selection.AutoFill Destination:=Range("D4:D283")
        Range("D4:D283").Select
        Range("E4").Select
        ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
        Range("E4").Select
        Selection.AutoFill Destination:=Range("E4:E283")
        Range("E4:E283").Select
        Range("B3").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        ActiveWorkbook.Worksheets("BBvRBC").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("BBvRBC").Sort.SortFields.Add Key:=Range("E4:E283") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("BBvRBC").Sort
            .SetRange Range("B3:E283")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("F14").Select
    End Sub
    Attached Files Attached Files
    Last edited by andrewg10; 07-18-2013 at 07:53 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Issue with macros and password protection
    By Chiccada in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-17-2013, 10:26 AM
  2. VBA Macros Array List Issue
    By hecgroups in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-15-2013, 01:48 PM
  3. Macros causing a Read Only Issue
    By michellecairns in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-09-2011, 07:45 AM
  4. Issue with Vlookup using Macros
    By kapil in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-13-2010, 06:01 AM
  5. Personal Macro issue - 2 x macros
    By angel56 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-22-2007, 04:49 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1