Posts by mathew31

Re: using macro to code a pie chart

any help is appreciated....

Re: using the open file dialog box to avoid hard coding the file names..........

hello all,

could somebody take a look at this,http://www.ozgrid.com/forum/showthread.php?t=180687 its going to get buried.

Hello all i have this weird problem i cant wrap my head around.

i know making a pie chart is easy, but the workbook I am using has certain unique challenges.
in the attached file on the dashboard i need to create a pie chart in percentage of how many employees have assignment and how many do not have assignment as shown in the calender.....
the data changes so it should be done in a macro....

RMG Dashboard.xlsm

Re: using the open file dialog box to avoid hard coding the file names..........

that works
thank you jindon,pike,cytop and roy......

Re: using the open file dialog box to avoid hard coding the file names..........

ty jindon, but why is it pasting from the first cell and not the 4th?
in this process it replaces the headers with data...
it is the same code that worked before

Re: using the open file dialog box to avoid hard coding the file names..........

sorry guys i am confused now
for reference RMG Dashboard.xlsm my main file
my input file Global Headcount.xlsx

Re: using the open file dialog box to avoid hard coding the file names..........

update: object variable or with block variable not set

b = mastRng = .Range(.Cells(1, 1), .Cells(1, Mastcol)).Value

Re: using the open file dialog box to avoid hard coding the file names..........

i am replacing this line

[COLOR=blue]With[/COLOR][COLOR=#333333] workbooks.open(fn).Worksheets("GLOBAL_HEADCOUNT")[/COLOR]

with this

[COLOR=blue]With[/COLOR][COLOR=#333333] workbooks.open(fn).sheets(1)[/COLOR]

as per your previous post
cool?

update invalid use of property error

Re: using the open file dialog box to avoid hard coding the file names..........

yes and these lines as well

Windows("Dashboard_for_Roshan.xlsm").Activate thisworkbook.Worksheets("Dashboard").Cells(i + 1, k)

Re: using the open file dialog box to avoid hard coding the file names..........

hello jindon,
i want to avoid using the sheet names

Re: using the open file dialog box to avoid hard coding the file names..........

Good Afternoon Roy,,,
i get a method or data member not found error on lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
i guess i deleted soemthing by mistake..... and i cant figure it out...

here is my entire code... what obvious thing am i missing

Sub Global_Headcount()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   Application.Calculation = xlCalculationManual
    Worksheets("Dashboard").Select '<-------------name of the sheet in dashboard>












' Clear Emp data
    Range("A4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("A4").Select
     
     Dim k As Long, i As Long, j As Long, t As Long
    Dim y As Long, a As Variant, b As Variant, cmpRng As Range, r As Range, lastrow1 As Long
    Dim Mastcol As Long, mastRng As Range, n As Long
    Dim Wbk As Workbook
    Dim rng As Range
    Dim lastrow As Long, lastCol As Long
    Dim sngStartTime As Single
    Dim sngTotalTime As Single
    sngStartTime = Timer








  lastCol = 15
 
With ThisWorkbook
    lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
    Set cmpRng = .Range(.Cells(1, 1), .Cells(3, lastCol))
    a = cmpRng
    i = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
 
fn = Application.GetOpenFilename("Excel,*.xls*")
If fn = "False" Then Exit Sub
 
Set Wbk = Workbooks.Open(fn)
 
With Wbk
    Mastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    j = .Cells(.Rows.Count, 1).End(xlUp).Row
    Set mastRng = .Range(.Cells(1, 1), .Cells(1, Mastcol))
    b = mastRng
     
    For k = 1 To lastCol
        For n = 1 To Mastcol
            If UCase(a(3, k)) = UCase(b(1, n)) Then
                .Range(.Cells(2, n), .Cells(j, n)).Copy
                 '            Windows("Dashboard_for_Roshan.xlsm").Activate
                ThisWorkbook.Cells(i + 1, k).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                Exit For
            End If
        Next
    Next
End With
Wbk.Close False








 
 'To delete entire row if criteria is not met
                        Range("A4").Select
                        lastrow = Cells(Rows.Count, 1).End(xlUp).Row
                        For x = lastrow To 4 Step -1
                        If Cells(x, 15).Value <> "Active" Or (Cells(x, 10).Value <> "E&D" And Cells(x, 10).Value <> "ESG" _
                        And Cells(x, 10).Value <> "PLM SER" And Cells(x, 10).Value <> "VPD" And Cells(x, 10).Value <> "PLM PROD") Then
                        Rows(x).Delete
                        End If
                        Next x




   




        sngTotalTime = Timer - sngStartTime
        MsgBox "Employee Records Generated In:  " & (sngTotalTime \ 60) & " minutes, " & (sngTotalTime Mod 60) & " seconds"
 
 
 MsgBox "Set the dates for calendar generation "
 
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
        Application.Calculation = xlCalculationAutomatic
    Exit Sub




End Sub

Re: using a dialog box to select the file for consolidation

guys please have a look before this gets buried http://www.ozgrid.com/forum/sh…80663&p=674275#post674275

Re: using a dialog box to select the file for consolidation

actually if you post #1 you will see i have mentioned the problem completely.... but i now realise the title has been poorly framed as jindon has said....
so ill post another question

hello all the above code matches header between two sheets and if they match copies the data......
now the problem with this is that i have to hard code the file names always, which may cause someone to change the data in my code later so i want to avoid this approach and use the file open dialog box.
now the lone for using a file open box has been added but as you can see the file names are still hardcoded.... now what do i replace them with?

in the above example dashoard is my main workbook and the sheet name...
global headcount is then name of the file and sheet which i want to take the data from.

'lastCol = Worksheets("Dashboard").Cells(3, Columns.Count).End(xlToLeft).Column
lastCol = 15 
lastrow = Worksheets("Dashboard").Cells(Rows.Count, 1).End(xlUp).Row 
Set cmpRng = Range(Cells(1, 1), Cells(3, lastCol)) 
a = cmpRng 
i = Cells(Rows.Count, 1).End(xlUp).Row 
 
fn = Application.GetOpenFilename("Excel,*.xls*") 
If fn = "False" Then exit Sub 
 
Set Wbk = Workbooks.Open(fn) 
 
Worksheets("GLOBAL_HEADCOUNT").Select 
Mastcol = Cells(1, Columns.Count).End(xlToLeft).Column 
j = Cells(Rows.Count, 1).End(xlUp).Row 
Set mastRng = Range(Cells(1, 1), Cells(1, Mastcol)) 
b = mastRng 
 
For k = 1 To lastCol 
    For n = 1 To Mastcol 
        If UCase(a(3, k)) = UCase(b(1, n)) Then 
            Windows("Global_HEADCOUNT").Activate 
            Worksheets("GLOBAL_HEADCOUNT").Range(Cells(2, n), Cells(j, n)).Copy 
            Windows("Dashboard_for_Roshan.xlsm").Activate 
            Worksheets("Dashboard").Select 
            Cells(i + 1, k).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 
            Exit For 
        End If 
    Next 
Next 
Call Wbk.Close(False)

Re: using a dialog box to select the file for consolidation

ill make it short and brief....

the code that i have matches headers between two workbooks and copy pastes the data.....
now this works but the problem is the user will have to go inside the code and give the name of the workbooks and the sheets and i want to avoid it using a file open dialog box....
in my code global headcount is the name of my file and headsheet from where i want to copy the data, and dashboard is the name of the workbook and sheet which i wan to paste them tooo.

hope that helps jindon..... this is the only problem i am facing....
please advise

Re: using a dialog box to select the file for consolidation

apologies for the mistake......but i have mentioned why i wanted to do this in the question.
no matter.... how do i change it.

Re: using a dialog box to select the file for consolidation

mate i was looking to avoid giving the reference using an open file dialog box so nobody has to step in the code and do it everytime.......

Re: using a dialog box to select the file for consolidation

i hear you jindon.....
but i have no way of anticipating the kind of formatting they use, if you could take a look at my code post #24
cause it seems to work with any formtaing and that is something that would help me....

Re: using a dialog box to select the file for consolidation

fn = "'" & Left$(fn, InStrRev(fn, Application.PathSeparator)) & _ "[" & Mid$(fn, InStrRev(fn, Application.PathSeparator) + 1) & "]sheet1'!"
x = ExecuteExcel4Macro(fn & "r1c1") x = ExecuteExcel4Macro("match(""" & .Cells(1, i).Value & _ """," & fn & "r1:r1,0)") If IsNumeric(x) Then y = ExecuteExcel4Macro("counta(" & fn & "c" & x & ":c" & x & ")") For ii = 2 To y .Cells(ii, i).V

Re: using a dialog box to select the file for consolidation

i thank you for the code jindon, but i do not understand it, could you guide me ?