Groves22
01-21-2009, 06:14 AM
Hello...
I am working between 2 workbooks. I run a profile report monthly, and I want to make it as automated as possible. One section, I want to have the formula's updated with the name of the file. I'll explain a bit more after I post the VBA
Sub Month_sales()
Dim year, month, day, count As String
year = InputBox("Please enter year:")
month = InputBox("Please enter month (##):")
day = InputBox("Please enter day (##):")
'Open and set up data monthly sheet
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile Exhibits\Outputs\" & year & month & day & " Sales - M.xls")
Cells.Select
Columns.AutoFit
Sheets(1).Select
Sheets.Add
Sheets(2).Select
Columns("D:D").Select
Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Columns("E:E").Select
Selection.Copy
Sheets(1).Select
Range("A1").Select
ActiveSheet.Paste
Sheets(2).Select
Columns("AJ:AJ").Select
Selection.Copy
Sheets(1).Select
Range("B1").Select
ActiveSheet.Paste
Range("A1").Select
Columns("A:B").Select
Columns.AutoFit
Sheets(2).Select
ActiveSheet.ShowAllData
'Open and set up monthly sale Profile tab
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile Exhibits\Ohio Property Profile v2 " & month & day & year & ".xls")
Sheets(5).Select
Range("A4:GL65500").Select
Selection.ClearContents
'Filter and paste policy data from data sheet to profile tab
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).Select
Columns("E:E").Select
Range("E:E").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Range("F2:H2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("B3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).Select
Range("I2:M2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("F3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).Select
Range("S2:X2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("N3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).Select
Range("AA2:AG2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("Z3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).Select
Range("AH2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AH3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).Select
Range("AI2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AY3").Select
ActiveSheet.Paste
'Copy down formulas in profile tab
Range("B1").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[3]C:R[65535]C)"
count = Range("B1").Value
Range("A3").Select
Selection.AutoFill Destination:=Range(Cells(3, 1), Cells(count + 3, 1))
Range("E3").Select
Selection.AutoFill Destination:=Range(Cells(3, 5), Cells(count + 3, 5))
'Show all data from data tab
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).Select
ActiveSheet.ShowAllData
'Paste Formula's into profile data sheet
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("K3").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC5=""H3"",VLOOKUP(RC1&""-""&""CVA"",'[20081231 Sales - M.xls]20081231 Sales - M'!R2C4:R65536C36,12,FALSE),VLOOKUP(RC1&""-""&""CVC"",'[20081231 Sales - M.xls]20081231 Sales - M'!R2C4:R65536C36,12,FALSE))"
End Sub
The very end, where commeneted, is where I am having an issue. I want where it says: "=IF(RC5=""H3"",VLOOKUP(RC1&""-""&""CVA"",'[20081231 Sales - M.xls]20081231 Sales - M'!R2C4:R65536C36,12,FALSE),VLOOKUP(RC1&""-""&""CVC"",'[20081231 Sales - M.xls]20081231 Sales - M'!R2C4:R65536C36,12,FALSE))", to change with the names of year, month, and day from above.
If you need more info, I will present it. Also, if you see a way to upgrade my VBA, by all means let me know. I am still rather new to this world of code!
Thanks!!
I am working between 2 workbooks. I run a profile report monthly, and I want to make it as automated as possible. One section, I want to have the formula's updated with the name of the file. I'll explain a bit more after I post the VBA
Sub Month_sales()
Dim year, month, day, count As String
year = InputBox("Please enter year:")
month = InputBox("Please enter month (##):")
day = InputBox("Please enter day (##):")
'Open and set up data monthly sheet
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile Exhibits\Outputs\" & year & month & day & " Sales - M.xls")
Cells.Select
Columns.AutoFit
Sheets(1).Select
Sheets.Add
Sheets(2).Select
Columns("D:D").Select
Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Columns("E:E").Select
Selection.Copy
Sheets(1).Select
Range("A1").Select
ActiveSheet.Paste
Sheets(2).Select
Columns("AJ:AJ").Select
Selection.Copy
Sheets(1).Select
Range("B1").Select
ActiveSheet.Paste
Range("A1").Select
Columns("A:B").Select
Columns.AutoFit
Sheets(2).Select
ActiveSheet.ShowAllData
'Open and set up monthly sale Profile tab
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile Exhibits\Ohio Property Profile v2 " & month & day & year & ".xls")
Sheets(5).Select
Range("A4:GL65500").Select
Selection.ClearContents
'Filter and paste policy data from data sheet to profile tab
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).Select
Columns("E:E").Select
Range("E:E").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Range("F2:H2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("B3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).Select
Range("I2:M2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("F3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).Select
Range("S2:X2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("N3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).Select
Range("AA2:AG2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("Z3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).Select
Range("AH2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AH3").Select
ActiveSheet.Paste
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).Select
Range("AI2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("AY3").Select
ActiveSheet.Paste
'Copy down formulas in profile tab
Range("B1").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[3]C:R[65535]C)"
count = Range("B1").Value
Range("A3").Select
Selection.AutoFill Destination:=Range(Cells(3, 1), Cells(count + 3, 1))
Range("E3").Select
Selection.AutoFill Destination:=Range(Cells(3, 5), Cells(count + 3, 5))
'Show all data from data tab
Workbooks(year & month & day & " Sales - M.xls").Activate
Sheets(2).Select
ActiveSheet.ShowAllData
'Paste Formula's into profile data sheet
Workbooks("Ohio Property Profile v2 " & month & day & year & ".xls").Activate
Range("K3").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC5=""H3"",VLOOKUP(RC1&""-""&""CVA"",'[20081231 Sales - M.xls]20081231 Sales - M'!R2C4:R65536C36,12,FALSE),VLOOKUP(RC1&""-""&""CVC"",'[20081231 Sales - M.xls]20081231 Sales - M'!R2C4:R65536C36,12,FALSE))"
End Sub
The very end, where commeneted, is where I am having an issue. I want where it says: "=IF(RC5=""H3"",VLOOKUP(RC1&""-""&""CVA"",'[20081231 Sales - M.xls]20081231 Sales - M'!R2C4:R65536C36,12,FALSE),VLOOKUP(RC1&""-""&""CVC"",'[20081231 Sales - M.xls]20081231 Sales - M'!R2C4:R65536C36,12,FALSE))", to change with the names of year, month, and day from above.
If you need more info, I will present it. Also, if you see a way to upgrade my VBA, by all means let me know. I am still rather new to this world of code!
Thanks!!