automation?

[straberiwine]

I have a workbook with several sheets related to customer transactions. On one sheet (Customer) is a table that has just the customer code and name, then on another sheet is a list of goals. For each customer there is a column for Goal, Actual and Difference. Then there are 4 difference goals, listed in a row, for each month. So there is a group of four rows for each month and 3 columns for each customer. The 'Actual' column has data pulled from a pivot table using GetPivotData that specifies the month and the customer in the formula.


Right now, whenever a new customer is added, i have to manually create the three columns for the customer, then copy the formulas and replace the customer name in the GetPivotData formula.


I'd like to make this an automatic process, either driven by a new customer being added to the Customer sheet or as a macro assigned to a button.


Any ideas?

 

[Luke M]

I'd start by recording a macro of you setting up the sheet for someone. That will give you the basic framework. From there, you can work on making things more dynamic by using variables. Chandoo has written a few articles on macros that might be worth reading:

http://chandoo.org/wp/2011/08/29/introduction-to-vba-macros/

 

[straberiwine]

So, I've built the macro and it works like I want. However, instead of selecting the Columns("O:O"), I need it to paste in the active cell, or preferably in the next free column. How would I use the activecell object in this case? I'm sorry - my VBA is very rusty.


Sub AddGoal()

'

' AddGoal Macro


'


Dim OldCarrier As String

Dim CarrierName As String


ActiveSheet.Unprotect

Range("C2").Select

Columns("C:E").Select

Range("C2").Activate

OldCarrier = ActiveCell.Value

Selection.Copy

Columns("O:O").Select

Range("O2").Activate

ActiveSheet.Paste

CarrierName = InputBox("Name of New Carrier", "New Carrier Add")

Application.CutCopyMode = False

Selection.Replace What:=OldCarrier, Replacement:=CarrierName, LookAt:=xlPart, _

SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

 

[patsfan]

Although I am not much of a VBA programmer, here are a few examples of where I use activecell.offset


ActiveCell.Offset(1, 2).Select 'this code will select the cell ONE down and TWO right of the activecell

ActiveCell.Offset(0, 5).EntireColumn.Select ' this will select the entire column FIVE cells to the right of the activecell

Range(ActiveCell.Offset(0, 5), ActiveCell.Offset(0, 7)).EntireColumn.Select ' this will select the entire columns FIVE right to SEVEN right of the activecell


'If you are looking to select the NEXT empty column on the sheet.

Range("IV1").End(xlToLeft).Offset(0,1).EntireColumn.Select ' this will select the next column AFTER the last cell in row 1


'You can copy these codes into your VBA editor and step through them to see how they work

'Is this what you are asking?

 

[mercatog]

Hi, here your code without any select or Activate (they make wasting time and code stability)

[pre]

Sub AddGoal()
Dim OldCarrier As String, CarrierName As String
Dim NewCol As Integer
Dim LastLig As Long

Application.ScreenUpdating = False
CarrierName = InputBox("Name of New Carrier", "New Carrier Add")
If CarrierName <> "" Then
'Adapte to CodeName of your Sheet in place of Sheet1 or With Worksheets("NameOfYourSheet")
With Sheet1
LastLig = .Cells(.Rows.Count, "C").End(xlUp).Row
NewCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
If NewCol < .Columns.Count - 4 Then
NewCol = Application.Max(NewCol + 1, 15)    'here Begin the copy from the column O and the following
.Unprotect
.Range("C2:E" & LastLig).Copy .Cells(2, NewCol)
OldCarrier = .Range("C2").Value
.Range(.Cells(2, NewCol), .Cells(LastLig, NewCol + 2)).Replace OldCarrier, CarrierName, xlPart
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Else
MsgBox "Deficient number of columns for a new copy"    'Don't laugh about my English :)
End If
End With
End If
End Sub

[/pre]

 

[SirJB7]

@mercatog

Hi!

Missing an "Application.ScreenUpdating = True" before "End Sub".

Regards!

 

[mercatog]

Hi SirJB7

Right! even if no effect is observed if we do not return to true in this case.


By default, it is set to True when the next code is called

 

[shrivallabha]

That's true about application.screenupdating only. But it doesn't extend to other items such as application.enablevents.


So it will not harm to write just a line more IMHO.