Posts by L7B

L7B · Jul 5th 2013

Hi

I have searched the forum and tried several of the answers but don't seem to get this to work without making the workbook size enormous.

Current formula works perfectly...except now I have multiples now in the range set as "TYPE" which I now need to sum.
The ranges are in a different sheet.

Formula
=(INDEX(WDB082_DATA,MATCH($A4,DATE,0),MATCH(G$3,TYPE,0)))

Have tried a sumif with all the different types but this over 7000 cells makes the workbook very cumbersome in size.

Any help is much appreciated.

Thanks
Lee

L7B · Oct 19th 2011

Re: BeforeDoubleClick 70 variations

Do I need to close now it is resolved??

L7B · Oct 19th 2011

Re: BeforeDoubleClick 70 variations

Hi

1st didnt know it was not allowed to post on other websites it was more a case of trying to get help. So apologies.

mikerickson Thanks ever so much a few tweaks and is working dream. If I got the beer you would be more than welcome to it.

Code

On Error Resume Next
If Application.Intersect(Target, Range("EmployeeEngagement")) Is Nothing Then
Else
On Error GoTo 0
Show_Employee_Engagement  'This as it brings out a document as a picture
End If

L7B · Oct 18th 2011

Re: BeforeDoubleClick 70 variations

Hi Glenn
Are you able to help me with a solution as I really need to get finisihed for work presentation.

Thanks

L7B · Oct 18th 2011

Help I am pulling whats left of my hair out.

The double click works till it comes across a instruction where there is no double click line/ word relating to the code in the report.

The issue is the 2nd part below.

When I double click on WA Market Share because there is no line for Employee_Turnover on the report it does not skip over and show WA Market Share it shows Employee_Turnover instead.

The macro is made up of 70 variations over 48 reports so I dont want to be messing around with what is applicable for each report when they get broken out from the master.

This is the code that brings in the picture for display. Works perfectly.

Code

'=================
'    EMPLOYEES
'=================
Sub Show_Employee_Engagement()
Application.ScreenUpdating = False
ChDir "N:\corpdata\Admin Gen\Scorecards\Scorecards 2011-12\0. Definitions"
    Workbooks.Open Filename:= _
    "N:\corpdata\Admin Gen\Scorecards\Scorecards 2011-12\0. Definitions\2011-12 Definitions.xlsx"
    Sheets("Employees").Select
    
Application.Goto Reference:="Employee_Engagement"
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Workbooks("2011-12 Definitions.xlsx").Close
    Windows("1 Rob Bransby Group Q1 2011_12.xlsm").Activate
    Range("BP4").Select
    ActiveSheet.Pictures.Paste.Select
    Selection.Name = ("EmployeeEngagement_1")
    Range("CD4").Select
    ActiveWindow.Zoom = 85
    Application.ScreenUpdating = True
  
End Sub
Sub Show_Employee_Performance_Plans()
Application.ScreenUpdating = False
ChDir "N:\corpdata\Admin Gen\Scorecards\Scorecards 2011-12\0. Definitions"
    Workbooks.Open Filename:= _
    "N:\corpdata\Admin Gen\Scorecards\Scorecards 2011-12\0. Definitions\2011-12 Definitions.xlsx"
    Sheets("Employees").Select
    Application.Goto Reference:="Employee_Performance_Plans"
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
     Workbooks("2011-12 Definitions.xlsx").Close
    Windows("1 Rob Bransby Group Q1 2011_12.xlsm").Activate
    Range("BP4").Select
    ActiveSheet.Pictures.Paste.Select
    Selection.Name = ("EmployeePerformancePlans_1")
    Range("CD4").Select
    ActiveWindow.Zoom = 85
    Application.ScreenUpdating = True
End Sub


Sub Show_Performance_Reviews_Completion()
Application.ScreenUpdating = False
ChDir "N:\corpdata\Admin Gen\Scorecards\Scorecards 2011-12\0. Definitions"
    Workbooks.Open Filename:= _
    "N:\corpdata\Admin Gen\Scorecards\Scorecards 2011-12\0. Definitions\2011-12 Definitions.xlsx"
    Sheets("Employees").Select
    Application.Goto Reference:="Performance_Reviews_Completion"
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
     Workbooks("2011-12 Definitions.xlsx").Close
    Windows("1 Rob Bransby Group Q1 2011_12.xlsm").Activate
    Range("BP4").Select
    ActiveSheet.Pictures.Paste.Select
    Selection.Name = ("PerformanceReviewsCompletion_1")
    Range("CD4").Select
    ActiveWindow.Zoom = 85
    Application.ScreenUpdating = True
End Sub
Sub Show_Employee_Turnover()
Application.ScreenUpdating = False
ChDir "N:\corpdata\Admin Gen\Scorecards\Scorecards 2011-12\0. Definitions"
    Workbooks.Open Filename:= _
    "N:\corpdata\Admin Gen\Scorecards\Scorecards 2011-12\0. Definitions\2011-12 Definitions.xlsx"
    Sheets("Employees").Select
    
Application.Goto Reference:="Employee_Turnover"
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
     Workbooks("2011-12 Definitions.xlsx").Close
    Windows("1 Rob Bransby Group Q1 2011_12.xlsm").Activate
    Range("BP4").Select
    ActiveSheet.Pictures.Paste.Select
    Selection.Name = ("EmployeeTurnover_1")
    Range("CD4").Select
    ActiveWindow.Zoom = 85
    Application.ScreenUpdating = True
End Sub
Sub Show_Absenteeism_()
Application.ScreenUpdating = False
ChDir "N:\corpdata\Admin Gen\Scorecards\Scorecards 2011-12\0. Definitions"
    Workbooks.Open Filename:= _
    "N:\corpdata\Admin Gen\Scorecards\Scorecards 2011-12\0. Definitions\2011-12 Definitions.xlsx"
    Sheets("Employees").Select
    Application.Goto Reference:="Abenteeism_"
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
     Workbooks("2011-12 Definitions.xlsx").Close
    Windows("1 Rob Bransby Group Q1 2011_12.xlsm").Activate
    Range("BP4").Select
    ActiveSheet.Pictures.Paste.Select
    Selection.Name = ("Absenteeism_1")
    Range("CD4").Select
    ActiveWindow.Zoom = 85
    Application.ScreenUpdating = True
End Sub


'=================
'    MEMBERS
'=================
Sub Show_WA_Market_Share()
Application.ScreenUpdating = False
ChDir "N:\corpdata\Admin Gen\Scorecards\Scorecards 2011-12\0. Definitions"
    Workbooks.Open Filename:= _
    "N:\corpdata\Admin Gen\Scorecards\Scorecards 2011-12\0. Definitions\2011-12 Definitions.xlsx"
    Sheets("Members").Select
    Application.Goto Reference:="WA_Market_Share"
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    Workbooks("2011-12 Definitions.xlsx").Close
    Windows("1 Rob Bransby Group Q1 2011_12.xlsm").Activate
    Range("BP4").Select
    ActiveSheet.Pictures.Paste.Select
    Selection.Name = ("WAMarketShare_1")
    Range("CD4").Select
    ActiveWindow.Zoom = 85
    Application.ScreenUpdating = True
End Sub

Display More

2nd Part

Code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Create_Shape_CLOSE_KPI_DEFINITION
'=================
'    EMPLOYEES
'=================
On Error Resume Next
If Range("EmployeeEngagement") Is Nothing Then Exit Sub
If Not Intersect(Target, Range("EmployeeEngagement")) Is Nothing Then
On Error GoTo 0
    Show_Employee_Engagement
   
On Error Resume Next
ElseIf Range("EmployeePerformancePlans") Is Nothing Then Exit Sub
ElseIf Not Intersect(Target, Range("EmployeePerformancePlans")) Is Nothing Then
On Error GoTo 0
   Show_Employee_Performance_Plans
 
On Error Resume Next
ElseIf Range("PerformanceReviewsCompletion") Is Nothing Then Exit Sub
ElseIf Not Intersect(Target, Range("PerformanceReviewsCompletion")) Is Nothing Then
On Error GoTo 0
    Show_Performance_Reviews_Completion
   
'On Error Resume Next
'ElseIf Range("EmployeeTurnover") Is Nothing Then
'ElseIf Not Intersect(Target, Range("EmployeeTurnover")) Is Nothing Then
'On Error GoTo 0
   'Show_Employee_Turnover
  
'On Error Resume Next
'ElseIf Range("Absenteeism_") Is Nothing Then
'ElseIf Not Intersect(Target, Range("Absenteeism_")) Is Nothing Then
'On Error GoTo 0
   'Show_Absenteeism_
On Error Resume Next
ElseIf Range("WAMarketShare") Is Nothing Then Exit Sub
ElseIf Not Intersect(Target, Range("WAMarketShare")) Is Nothing Then
On Error GoTo 0
    Show_WA_Market_Share

Display More

L7B · Oct 4th 2011

Re: Parse Report - iLoop does not filter report correctly

Hi

I have been doing some more work on my code and now have it filtering close.
It picks up the loop and the "aaaa". How can I get it to pick up all the different a's in criteria2?

Thanks Lee

I am slowly progressing in teach yourself VBA

Code

Option Explicit
Sub ParseReport()
'Dim Itm As Long, vCol As Long, m As Long
Dim ws As Worksheet, newName As String, SvPath As String
Dim Sourcewb As Workbook, Destwb As Workbook, MyArr As Variant
Dim FileExtStr As String, FileFormatNum As Long
Dim iLoop As Integer, SCH As Variant


'Set Sourcewb = ActiveWorkbook
Set ws = Sheets("Master")  'Sheet with data in it
SvPath = "C:\Documents and Settings\l7b\My Documents\Lee Trial Reports\KPI Test\"       'output directory


Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
  
  
'Filters report by scorecard holder
SCH = Array("Finance", "Capital Risk", "GIP", "Enabling", "GIP Team", "ICT", "IT", "Financial Services", "GIP", _
    "Legal", "IBM", "Communications Team", "Benefits Management", "Viability", "Health Partnerships", "Member Relations", "BSD", _
    "Projects & Systems", "Distribution", "Corporate health", "Contact Centre", "Branch Network", "Member Support", _
    "HG", "GMF health", "People & Performance", "Human Resources", "Group Loss Prevention & Awareness", "Group", "HBF Group", "Program Office")
'How many in SCH to loop through
    For iLoop = 1 To 31
    Set Sourcewb = ActiveWorkbook
        
        Call UnHide
        Call UnGroup
        Call UnFilter
        'Call UnMerge
        
        ws.Range("$B$8:$BM$572").AutoFilter Field:=3, Criteria1:=SCH(iLoop), Operator:=xlOr, Criteria2:=Array("a" _
        , "aa", "aaa", "aaaa"), Operator:=xlFilterValues
                   
        'Copies to new Workbook
        Range("$B$1:$BM$600").Select
        Selection.Copy
        Workbooks.Add
        ActiveSheet.Paste
        
        Set Destwb = ActiveWorkbook
    
        MyArr = Range("L1").Value
                 
        Call SetMyArr
        Call Formatting
        Call Quarters
        Call Merge
               
    'With Destwb
        Call SaveNewWorkbook
    'End With
    
        
    Next iLoop
     
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

Display More

L7B · Oct 3rd 2011

Re: Parse Report - iLoop does not filter report correctly

Hi

That makes it worse it only picks up the "aaaa".
Anyone else have some answers that will assist.

Thanks

L7B · Sep 30th 2011

Hi

This macro works with the exception of the part "Criteria1:=Array(iLoop " which should filter with one of the numbers between 1 - 45 and all the "a", "aa", "aaa", "aaaa". It only filters by the "a", "aa", "aaa", "aaaa".

Please if someone can assist as I need to get this report finished.

Thanks

Code

Option Explicit
Sub ParseReport()
Dim Itm As Long, vCol As Long, m As Long
Dim ws As Worksheet, newName As String, SvPath As String
Dim Sourcewb As Workbook, Destwb As Workbook, MyArr As Variant
Dim FileExtStr As String, FileFormatNum As Long
Dim iLoop As Integer
Set Sourcewb = ActiveWorkbook
Set ws = Sheets("Master")  'Sheet with data in it
SvPath = "C:\Documents and Settings\l7b\My Documents\Lee Trial Reports\KPI Test\"       'output directory
MyArr = Range("L1").Value
Application.ScreenUpdating = False
Application.EnableEvents = False
   
'Filters report by scorecard holder


For iLoop = 1 To 45
   
    ws.Range("$B$8:$BM$572").AutoFilter Field:=1, Criteria1:=Array(iLoop _
       , "a", "aa", "aaa", "aaaa"), Operator:=xlFilterValues
                
           
        'Copies to new Workbook
        Range("A1:QZ598").Select
        Selection.Copy
        Workbooks.Add
        ActiveSheet.Paste
        
          
    Set Destwb = ActiveWorkbook
    
    With Destwb
       'Save the new workbook and close it
        FileExtStr = ".xlsm"
        FileFormatNum = 52
        newName = Range("L1").Value
        ActiveSheet.Name = MyArr
        Range("G1").Select
        
        Destwb.SaveAs SvPath & MyArr & FileExtStr, FileFormat:=FileFormatNum
              
        'Formats sheet
        ActiveWindow.DisplayGridlines = False
        Rows("1:7").RowHeight = 18
        Rows("8:8").RowHeight = 51
        Rows("9:520").RowHeight = 18
        Rows("9:520").WrapText = False
        Cells.EntireColumn.AutoFit
        Range("I:I,M:M").ColumnWidth = 2
        Range("AG6:AM6,AO6:AU6,AW6:BC6,BE6:BK6,AG7:AM7,AO7:AU7,AW7:BC7,BE7:BK7").Merge
        ActiveWindow.Zoom = 70
        Range("A:F").Group
        Range("P:AF").Group
        ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
    
    
    
    Destwb.Save
    Destwb.Close
        
        
        
    End With
   Next iLoop
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Display More

L7B · Sep 13th 2011

Hi

I have been working on this forever and still am having issues so I am really hoping someone can help.
Thanks
Lee

Code

Sub ParseReport()
'JB
Dim my_array() As String
Dim Itm As Long, vCol As Long, m As Long
Dim ws As Worksheet, newName As String, SvPath As String
Dim Sourcewb As Workbook, Destwb As Workbook, MyArr As Variant
Dim FileExtStr As String, FileFormatNum As Long
Dim iLoop As Integer
Set Sourcewb = ActiveWorkbook
Set ws = Sheets("Master")  'Sheet with data in it
SvPath = "C:\Documents and Settings\l7b\My Documents\Lee Trial Reports\KPI Test\"       'output directory
MyArr = Range("L1").Value
Application.ScreenUpdating = False
Application.EnableEvents = False
ReDim my_array(35)
    
my_array(0) = "Enabling"
my_array(1) = "IT"
my_array(2) = "ICT"
my_array(3) = "Program Office"
my_array(4) = "BSD"
my_array(5) = "Finance"
my_array(6) = "Finance"
my_array(7) = "Capital Risk"
my_array(8) = "GIP"
my_array(9) = "Information Centre"
my_array(10) = "Planning Centre"
my_array(11) = "Financial Services"
my_array(12) = "Legal"
my_array(13) = "IBM"
my_array(14) = "Communications"
my_array(15) = "Product Development"
my_array(16) = "Public Relations"
my_array(17) = "Marketing"
my_array(18) = "Market Research"
my_array(19) = "Community health"
my_array(20) = "Benefits Management"
my_array(21) = "Viability"
my_array(22) = "health Partnerships"
my_array(23) = "Member Relations"
my_array(24) = "Projects & Systems"
my_array(25) = "Distribution"
my_array(26) = "Corporate health"
my_array(27) = "Contact Centre"
my_array(28) = "Branch Network"
my_array(29) = "Member Support"
my_array(30) = "HG"
my_array(31) = "GMF health"
my_array(32) = "People Services"
my_array(33) = "Human Resources"
my_array(34) = "Learning & Development"
my_array(35) = "HBF Group"
   
'Filters report by scorecard holder


For iLoop = 1 To 35
   
    ws.Range("$A$8:$BM$409").AutoFilter Field:=3, Criteria1:=my_array(iLoop _
       , "a", "aa", "aaa", "aaaa"), Operator:=xlFilterValues
        
        
       
        'Copies to new Workbook
        ws.Range("A1:BM520").Copy
        Workbooks.Add
        ActiveSheet.Paste
    
        Set Destwb = ActiveWorkbook
    
    With Destwb
       
    
        'Formats sheet
        ActiveWindow.DisplayGridlines = False
        Rows("1:7").RowHeight = 18
        Rows("8:8").RowHeight = 51
        Rows("9:520").RowHeight = 18
        Rows("9:520").WrapText = False
        Cells.EntireColumn.AutoFit
        Range("I:I,M:M").ColumnWidth = 2
        Range("AG6:AM6,AO6:AU6,AW6:BC6,BE6:BK6,AG7:AM7,AO7:AU7,AW7:BC7,BE7:BK7").Merge
        ActiveWindow.Zoom = 70
        Range("A:F").Group
        Range("P:AF").Group
        ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
    
    newName = Range("L1").Value
    ActiveSheet.Name = MyArr
    Range("G1").Select
    
    'Save the new workbook and close it
        FileExtStr = ".xlsm"
        FileFormatNum = 52
        
        Destwb.SaveAs SvPath & MyArr & FileExtStr, FileFormat:=FileFormatNum
        Destwb.Close
        
        
    End With
   Next iLoop
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Display More

L7B · May 17th 2011

Re: LOOKUP with MATCH INDEX summing named range with multiple metrics

Hi Craig

I have managed to upload report for you

Thanks
Lee

L7B · May 17th 2011

Re: LOOKUP with MATCH INDEX summing named range with multiple metrics

Hi Sorry even zipped the file is large 1.08gb. I could email.

L7B · May 16th 2011

Hi

I need some help with bringing in data from cells which may be text ( Yes, No, High, Low, Medium)or numbers & Percentage.

Row 2 is a heading row with upto 48 different headings
Column C has metrics which can repeated be in a cell multiple time.

Columns D:AZ has the data I need to bring into a primary Matrix. The primary matrix is formated to the value of cells it is bringing in.

Is there someone who can assist me.

Thanks
Lee

L7B · Mar 30th 2011

Re: VBA Listbox hide column error

Hi

As soon as it gets to the 1st ElseIf ListBox2 and everyone after it.

Thanks

L7B · Mar 30th 2011

Hi

Can someone please assist with this report which I need by tomorrow. It is running from a Userform listbox1 transfer to listbox2. Button OK hides columns as per Listbox2 selction. I keep getting error 1004. Thanks heaps in advance

Code

Private Sub Hide_Criteria_month()
'OTHER M:P
'CUR = Q:AH
'JUL = AK:AP
'AUG = AQ:AV
'SEP = AW:BB
'OCT = BC:BH
'NOV = BI:BN
'DEC = BO:BT
'JAN = BU:BZ
'FEB = CA:CF
'MAR = CG:CL
'APR = CM:CR
'MAY = CS:CX
'JUN = CY:DD
'FULL = DE:DJ
'YTD = DK:DP
Application.ScreenUpdating = False
Application.Calculation = False
           
       
If ListBox2.Value = "Current Period" Then
Worksheets("P&L Period").Columns("M:AH").EntireColumn.Hidden = False
Worksheets("P&L Period").Columns("AK:DP").EntireColumn.Hidden = True


ElseIf ListBox2.Value = "July" Then
Worksheets("P&L Period").Columns("M:P", "AK:AP").EntireColumn.Hidden = False
Worksheets("P&L Period").Columns("Q:AH", "AQ:DD").EntireColumn.Hidden = True


ElseIf ListBox2.Value = "August" Then
Worksheets("P&L Period").Columns("M:P", "AQ:AV").EntireColumn.Hidden = False
Worksheets("P&L Period").Columns("Q:AP", "AW:DD").EntireColumn.Hidden = True


ElseIf ListBox2.Value = "September" Then
Worksheets("P&L Period").Columns("M:P", "AW:BB").EntireColumn.Hidden = False
Worksheets("P&L Period").Columns("Q:AV", "BC:DD").EntireColumn.Hidden = True


ElseIf ListBox2.Value = "October" Then
Worksheets("P&L Period").Columns("M:P", "BC:BH").EntireColumn.Hidden = False
Worksheets("P&L Period").Columns("Q:BB", "BI:DD").EntireColumn.Hidden = True


ElseIf ListBox2.Value = "November" Then
Worksheets("P&L Period").Columns("M:P", "BI:BN").EntireColumn.Hidden = False
Worksheets("P&L Period").Columns("Q:BH", "BO:DD").EntireColumn.Hidden = True


ElseIf ListBox2.Value = "December" Then
Worksheets("P&L Period").Columns("M:P", "BO:BT").EntireColumn.Hidden = False
Worksheets("P&L Period").Columns("Q:BN", "BU:DD").EntireColumn.Hidden = True


ElseIf ListBox2.Value = "January" Then
Worksheets("P&L Period").Columns("M:P", "BU:BZ").EntireColumn.Hidden = False
Worksheets("P&L Period").Columns("Q:BT", "CA:DD").EntireColumn.Hidden = True


ElseIf ListBox2.Value = "February" Then
Worksheets("P&L Period").Columns("M:P", "CA:CF").EntireColumn.Hidden = False
Worksheets("P&L Period").Columns("Q:BZ", "CG:DD").EntireColumn.Hidden = True


ElseIf ListBox2.Value = "March" Then
Worksheets("P&L Period").Columns("M:P", "CG:CL").EntireColumn.Hidden = False
Worksheets("P&L Period").Columns("Q:CF", "CM:DD").EntireColumn.Hidden = True


ElseIf ListBox2.Value = "April" Then
Worksheets("P&L Period").Columns("M:P", "CM:CR").EntireColumn.Hidden = False
Worksheets("P&L Period").Columns("Q:CL", "CS:DD").EntireColumn.Hidden = True


ElseIf ListBox2.Value = "May" Then
Worksheets("P&L Period").Columns("M:P", "CS:CX").EntireColumn.Hidden = False
Worksheets("P&L Period").Columns("Q:CR", "CY:DD").EntireColumn.Hidden = True


ElseIf ListBox2.Value = "June" Then
Worksheets("P&L Period").Columns("M:P", "CY:DD").EntireColumn.Hidden = False
Worksheets("P&L Period").Columns("Q:CX").EntireColumn.Hidden = True


End If
Application.ScreenUpdating = True
Application.Calculation = True
End Sub

Display More

L7B · Dec 24th 2010

Re: Macro Lookup text in column then copy paste down till text appears again

Sorry can't attach files from here. Column A is blank, Column C is the word Location, Column D is the field to be copied to Colum A. Location is not at set intervals.

LOCATIONA01503BBE10219833P2757510310646370290B DR A O KAARD1122010291.2BENEFITBBE18502507P840456006846370288B DR RAMESH RAJAGOPALAN11220101462.8BENEFIT

L7B · Dec 24th 2010

Hi

I have this where it finds the text "LOCATION" in column C then copies the number in Column D to column A.

What I need it to do is copy down till the text "LOCATION appears again in Column C. Then a continous Loop until the end of page.

Code

Sub Copy_Location()
Dim Firstrow As Long
Dim lastRow As Long
 
Firstrow = 1
lastRow = Cells(Rows.Count, "C").End(xlUp).Row
ActiveSheet.Range("C:C").Select
 
If ActiveCell.Value = "LOCATION" Then
ActiveCell.Offset(0, 1).Copy
ActiveCell.Offset(0, -2).Select
ActiveSheet.Paste
End If
End Sub

Display More

Thanks

L7B · Dec 7th 2010

Re: Copy Formula Row into Blank cells

Hi

Thanks for this. A little bit of playing around and is now working perfectly.

Lee

L7B · Nov 18th 2010

Re: Copy Formula Row into Blank cells

Hi

Can't use filters as boss want the rows hidden. Yes up to row 33 contains the data that is used for the data validation lists and is hidden.

Sorry can't up load as work block this.

Thanks
Lee

L7B · Nov 17th 2010

Hi

I have data in columns H:T. This is manually entered and in non consistant in text values.

I am trying to get the macro to find the last blank cell and copy the formula contained in A33:G33

and paste to blank cells in columns A:G where H contains data.

Code

Sub Copy_Formula_Down()
    FinalRow = Worksheets("Complaint Summary List").Range("H65536").End(xlUp).Row
     ' Find the last row of data
     ' Loop through each row
    For x = 2 To FinalRow
         ' Decide if to copy based on column H
        ThisValue = Range("H" & x).Value
         ' could be any value
        If ThisValue = "yes" Then
            Worksheets("Complaint Summary List").Range("A33:G33").Copy
            NextRow = Worksheets("Complaint Summary List").Range("A65536").End(xlUp).Row + 1
            Worksheets("Complaint Summary List").Range("A" & NextRow).PasteSpecial = xlPasteFormulas
        End If
    Next x
End Sub

Display More

Thanks
Lee

L7B · Nov 9th 2010

Re: Macro paste special - Compile Error

Hi

No there is no error. I have copied and paste columns on there own and no problems. All the other sheets work fine.

Thanks
Lee

Posts by L7B

Index/Match/Sumif

BeforeDoubleClick 70 variations

BeforeDoubleClick 70 variations

BeforeDoubleClick 70 variations

BeforeDoubleClick 70 variations

Parse Report - iLoop does not filter report correctly

Parse Report - iLoop does not filter report correctly

Parse Report - iLoop does not filter report correctly

Auto filter and looping code to break out 35 sheets

LOOKUP with MATCH INDEX summing named range with multiple metrics

LOOKUP with MATCH INDEX summing named range with multiple metrics

LOOKUP with MATCH INDEX summing named range with multiple metrics

VBA Listbox hide column error

VBA Listbox hide column error

Macro Lookup text in column then copy paste down till text appears again

Macro Lookup text in column then copy paste down till text appears again

Copy Formula Row into Blank cells

Copy Formula Row into Blank cells

Copy Formula Row into Blank cells

Macro paste special - Compile Error