Hi,
Anyone know much about how to get some command buttons to stick in one place?
I currently have two in a summary sheet. Each button runs a similar code which clears and deletes (by shifting cells upward) at the start of the process, then populates data.
It's rather annoying though that I cant have the buttons stick to one zone, they end up moving left or right depending on which button I press. Each report also changes column sizes, dont know if that's relevant?
Anyhow, thanks for the help! The code is below in case it helps too.
Private Sub CommandButton2_Click()
Dim ws As Worksheet 'Define WS as worksheet
Application.ScreenUpdating = False 'don't update the screen (reduces lag time)
Sheet3.Activate 'select report tab and clear contents (necesairy to allow macro to run properly
Range("A3:AZ100").Select
Selection.Delete Shift:=xlUp
'Enter date of report
Range("A1").Select
ActiveCell.FormulaR1C1 = "Subscriber Report"
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Range("C1").Select
ActiveCell.FormulaR1C1 = "Report as of:"
Range("C1").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = True
Range("D1").Select
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("D1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Enter Column Titles
Range("A3").Select
ActiveCell.FormulaR1C1 = "Region"
Range("B3").Select
ActiveCell.FormulaR1C1 = "Country"
Range("C3").Select
ActiveCell.FormulaR1C1 = "Legal Name"
Range("D3").Select
ActiveCell.FormulaR1C1 = "TV Group"
Range("E3").Select
ActiveCell.FormulaR1C1 = "ACCT#"
Range("F3").Select
ActiveCell.FormulaR1C1 = "Platform"
Range("G3").Select
ActiveCell.FormulaR1C1 = "Deal Type"
Range("H3").Select
ActiveCell.FormulaR1C1 = "Total Operator Subs"
Range("I3").Select
ActiveCell.FormulaR1C1 = "Total BBCWN Subs"
Range("J3").Select
ActiveCell.FormulaR1C1 = "Prev. Month Subs"
Range("K3").Select
ActiveCell.FormulaR1C1 = "Gain/Loss"
Range("L3").Select
ActiveCell.FormulaR1C1 = "Penetration"
Range("M3").Select
ActiveCell.FormulaR1C1 = "Comments"
For Each ws In Worksheets 'for each worksheet (WS), take the specified range and paste special value in the named column
If ws.Name <> "Dashboard" And ws.Name <> "TEMPLATE" And ws.Name <> "Market" Then
'if the named sheet is not Dashboard, Template or Market, then run the if statement to grab data
'Subscriber Report Data
ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0) = "South America" 'Region
ws.Range("C3").Copy 'Country
ActiveSheet.Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ws.Range("C1").Copy 'Legal Name
ActiveSheet.Range("C65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ws.Range("G4").Copy 'TVGroup
ActiveSheet.Range("D65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ws.Range("G1").Copy 'Account Number
ActiveSheet.Range("E65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ws.Range("G7").Copy 'Platform
ActiveSheet.Range("F65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ws.Range("K3").Copy 'DealType
ActiveSheet.Range("G65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ws.Range("I7").Copy 'Total Operator Subs
ActiveSheet.Range("H65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ws.Range("I8").Copy 'Total BBCWN Subs
ActiveSheet.Range("I65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
'Previous Month BBCWN Subs
'???what to put here??? = "=VLOOKUP((INDEX(B11:L500,ROW(INDEX(D$11:D$500,MATCH(9.99999999999999E+307,D$11:D$500)))-10,1)-28),B11:L500,3)"
'ActiveSheet.Range("J65536").End(xlUp).Offset(1, 0).PasteSpecial x1PasteValues
'Simple Formulas
ActiveSheet.Range("K65536").End(xlUp).Offset(1, 0).FormulaR1C1 = "=RC[-1]-RC[-2]" 'Gain/Loss Formula: Subtract current month from previous month value
ActiveSheet.Range("L65536").End(xlUp).Offset(1, 0).FormulaR1C1 = "=RC[-3]/RC[-4]" 'Penetration FORMULA: Divide Total Current CLIENT Subscribers by Total OPERATOR Subscribers
End If 'end affiliate data grab, continue to next sheet (aka next affiliate)
Next ws 'end worksheet (WS) loop
'Format Column sizes
Columns("A:A").ColumnWidth = 14.43 'Region
Columns("E:E").ColumnWidth = 11 'Account Number
Columns("H:H").ColumnWidth = 20.14 'Total Operator Subs
Columns("I:I").ColumnWidth = 20.14 'Total BBCWN Subs
Columns("J:J").ColumnWidth = 19 'Prev. Month
Columns("K:K").ColumnWidth = 13 'Gain/Loss
Columns("L:L").ColumnWidth = 13 'Penetration
Columns("M:M").ColumnWidth = 35 'Comments
Rows("3:100").Select
Selection.AutoFilter
Application.ScreenUpdating = True 'display results
ActiveWindow.ScrollColumn = 1
End Sub
Bookmarks