PDA

View Full Version : Solved: Excel in-formula macro help!



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!!

nst1107
01-21-2009, 09:21 AM
You can use the variables in your formulas, just as you did in Workbooks.Open
Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile Exhibits\Outputs\" & year & month & day & " Sales - M.xls") Just make sure to use ampersands and double quotes appropriately.

As far as upgrading your coding? Yes, there is a lot that could be done to improve what you've got there. For one, I think you meant to dim year, month, and day as strings, like count, not as variants. Change this:
Dim year, month, day, count As String
to this:Dim year As String, month As String, day As String, count As StringFor another, the macro recorder is great for showing you how to code various actions, but once you have recorded your macro, try to eliminate all the .Select and .Selection. For instance, you can change this:
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
to this:Columns.AutoFit
Sheets.Add after:=Sheets(1)
Sheets(2).Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Columns("E:).Copy
Sheets(1).Range("A1").Paste
By not using .Select, .ActiveCell, etc., you not only speed up execution, but also reduce screen flicker and file size.

Groves22
01-21-2009, 10:27 AM
Hey Nate...
Thanks for the tips! I actually found another way around the formula problem, but now I am experiencing an error with the code you supplied to make it more efficient




Columns.AutoFit
Sheets.Add after:=Sheets(1)
Sheets(2).Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Columns("E:E").Copy
Sheets(1).Range("A1").Paste


When the code gets to the Sheets(1).Range("A1").Paste, I get the following error: Object doesn't support this property or method.

I also tried replacing .Range("A1").Paste with .Columns("A:A").Paste, to no avail.

Bob Phillips
01-21-2009, 11:34 AM
Columns.AutoFit
Sheets.Add after:=Sheets(1)
Sheets(2).Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Columns("E:E").Copy Sheets(1).Range("A1")

Groves22
01-21-2009, 12:25 PM
I don't quite understand this -


Columns.AutoFit
Sheets.Add after:=Sheets(1)
Sheets(2).Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Columns("E:E").Copy Sheets(1).Range("A1")


Here is what I have right now for that part. I am trying to make this more efficient.

Workbooks.Open ("P:\STATE REVIEWS\OH\OH\New Product Set Up\Property\Profile Exhibits\Outputs\" & year & month & day & " Sales - M.xls")
Columns.AutoFit
Sheets(1).Name = "Sheet2"
Sheets.Add before:=Sheets(1)
Sheets(2).Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Columns("E:E").Copy
Sheets(1).Range("A1").Select
Sheets(1).Paste
Sheets(2).Columns("AJ:AJ").Copy
Sheets(1).Range("B1").Select
Sheets(1).Paste
Columns("A:B").AutoFit
Sheets(2).ShowAllData

Thanks!

nst1107
01-21-2009, 01:39 PM
Bob's code is correct. The Copy method copies a range to a range. Sheets(1).Range("A1") is the destination.

Groves22
01-22-2009, 07:05 AM
Bob's code is correct. The Copy method copies a range to a range. Sheets(1).Range("A1") is the destination.

Hey Nate... I am not arguing the Bob was incorrect. I just didn't understand the code, and when I tried it, I got the same error. Could some please apply Bob's logic the code below so I can have a better understanding?

Thanks!

Columns.AutoFit
Sheets(1).Name = "Sheet2"
Sheets.Add before:=Sheets(1)
Sheets(2).Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Columns("E:E").Copy
Sheets(1).Range("A1").Select
Sheets(1).Paste
Sheets(2).Columns("AJ:AJ").Copy
Sheets(1).Range("B1").Select
Sheets(1).Paste
Columns("A:B").AutoFit
Sheets(2).ShowAllData

nst1107
01-22-2009, 08:17 AM
Columns.AutoFit
Sheets(1).Name = "Sheet2"
Sheets.Add before:=Sheets(1)
Sheets(2).Range("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Sheets(2).Columns("E").Copy Sheets(1).Range("A1")
Sheets(2).Columns("AJ:AJ").Copy Sheets(1).Range("B1")
Columns("A:B").AutoFit
Sheets(2).ShowAllData

Groves22
01-22-2009, 08:57 AM
That worked!! Thanks!

I may post more as I go.

Thanks again for all the help!