Posts by audimtm

Hi,

I have two issues that i am trying to resolve. I have a code that basically formats and combine multiple selected excel files. Problem 1. the macro doesn't always work (maybe i need to restart comp or restart excel? refresh issue?) 2. I need help with naming the worksheet based on a cell value/text.

currently i have a code to pull/extract numbers within parenthasis. however sometimes the sheet may not have numbers. so i need it to say if there's (12345) name sheet 12345, if there's no (12345 or similar), name sheet (basketball) baskeball. the cell range will always start with account:

ChDrive "G"
    ChDir "G:\OPERATNS\a"
    Z = Application.GetOpenFilename(Title:="Select a file or files", _
    MultiSelect:=True)
    If TypeName(Z) = "Boolean" Then Exit Sub
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next
    For x = 1 To UBound(Z)
    Set wb = Workbooks.Open(Z(x))
    
    For Each ws In ActiveWorkbook.Worksheets
    'For Each ws In wb.Worksheets
    ws.Select
       
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    Range("G:M,O:Q,S:T,V:AV").EntireColumn.Hidden = True
    
    Cells.Find(What:="Identifier", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
            
    ActiveCell.Offset(4, -1).Select
    ActiveWindow.FreezePanes = True
    
    Cells.Find(What:="Equivalents", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
            
    Selection.EntireRow.ShowDetail = False
    
    
    txt = Range("a5").Value  
    With CreateObject("VBScript.RegExp")
        .Pattern = "\(([^\)-]+)[^\)]*\)$"
        If .test(txt) Then ws.Name = .Execute(txt)(0).submatches(0)
    End With
    
    wb.Sheets.Copy Before:=ThisWorkbook.Sheets(1)
    wb.Close False
    Next ws
    
    For Each ws In ActiveWorkbook.Worksheets
    ws.Select
        If Application.CountA(ws.Cells) = 0 Then ws.Delete
    Next ws
    
    Next x
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub

Re: index match or another function

thanks for the quick replay i really appreciate it

Re: index match or another function

i want be able to pull specific data by matching in this case, code+date. ie if date&code matches the data set, give me the value.

Hi,

can someone look at my excel sheet and tell me whats wrong with my formula? thanks

Hi,

i'm having some trouble coming up with a clean way to pull data from another workbook(s). For example, wb 1 contains 3 set of data; column a = alphabets a through z, column b contains month end dates and column c contains a value.

How do i in wb 2 (new worksheet) only pull in data for letters, a, t, s, b and e based on a date i specify?

right now i'm using a very messy "sumproduct formula." for example, i'm using sumproduct((daterange=wb1daterange)*(letter=wb1letter)*(wb1.value)

my end result would be to pullin the value from wb 1 using two or three critierias in wb 2.

Re: Better Way to Write This Code

thanks, i'll try this.

I"m currently trying to perform a vooklookup/index match/sumproduct (either one) based on the input date the user chooses. Currently i have the code written as (see below). is there a better way to say, based on user input date, match date + unique code and bring the return overs (paste value)

Sub Pull_MTD_In()
Dim start_date As Integer
Dim UserEntry As String
Dim Msg As String
Dim TheDate As String
Dim LR As Long
Dim Wb As Workbook
Set Wb = ActiveWorkbook
 
Application.ScreenUpdating = False
Application.EnableEvents = False
Msg = "Enter A Date as mm/dd/yyyy"
    
    Do
        UserEntry = InputBox(Msg)
        If UserEntry = "" Then Exit Sub
        Sheets("Bent").Range("E1").Value = UserEntry
        If IsDate(UserEntry) Then
      
    Workbooks.Open FileName:= _
        "G:\Desktop\Daily In.xlsm"
    
    Wb.Activate
    Sheets("Benchmark").Select
    Range("C2").Select
   ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT((R1C5='[Daily In.xlsm]MTD Rets'!R2C3:R1048576C3)*(RC[-2]='[Daily In.xlsm]MTD Rets'!R2C1:R1048576C1)*('[Daily In.xlsm]MTD Rets'!R2C2:R1048576C2))"
    ActiveCell.Copy
  
    
    LR = Range("C" & Rows.Count).End(xlUp).Row
    Range("C3:C" & LR).Select
    
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    
   Range("C2:C" & LR).Select
   Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
  Workbooks("Daily In.xlsm").Close savechanges:=False
            
  Exit Sub
        Else
            Msg = "Invalid Date. Please enter date as mm/dd/yyyy"
        End If
    Loop
    
    
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Re: Left / Right / Mid

hi jindon, thanks for the quick reply. i don't need it as a msgbox but as a value so i can rename the sheet with that value. thanks!!!!

Re: Left / Right / Mid

hi, what if i had another example where range("a1")= 11111 (abd) (abcdef-cad) and i only wanted to extract abcdef. what would the vb code look like? thank sin advance

Hi,

The below code allows me to combine multiple sheets from different workbooks into one. However the macro is limited to 10 workbooks, i.e. if i select more than 10 workbooks, the code stops copying/moving sheets over and will stop at 10. Would you guys happen to know why? i need the code to copy as many workbooks i have opened.

Sub CombineSheets()
Dim WS As Worksheet
Dim x As Integer
    Z = Application.GetOpenFilename(Title:="Select a file or files", _
    MultiSelect:=True)
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next
    For x = 1 To UBound(Z)
    Set WB = Workbooks.Open(Z(x))
    
    For Each WS In ActiveWorkbook.Worksheets
    WS.Select
        If Application.CountA(WS.Cells) = 0 Then WS.Delete
    Next WS
    
    WB.Sheets.Copy Before:=ThisWorkbook.Sheets(1)
    WB.Close False
    Next x
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub

Re: Left / Right / Mid

how would this translate into VBA code? thanks in advance.

Hi,

Trying to rename each worksheet to a cell content. However within the cell, there are paranthesis that i need to get rid of. For example: range("a1")= 11111 (abcdef):

i need a code or excel function to only extract abcdef. From the example, i can use right(a1,7) which will give me abcdef), how would i get rid of the last )?

thanks in advance

Re: Groups and Sub Groups

thanks, really appreciate your help. i ended using activecell.offset to address the one cell.

Hi,

Was wondering if there's a way to ungroup 1 of 2 sub groups? For example the below code ungroups all data if there are multiple subgroups with a group. Is there a code to only ungroup 1 or the first subgroup only?

What i need:

Group 1:

Sub Group A (i only need to show Sub Group A)
Sub Group B

ActiveSheet.Outline.ShowLevels RowLevels:=1

Re: Confused on Countif in VBA - Share insight and some Help?

thanks for the reply. i just need something simplier. my code below sort of does the job. however, i need only to apply the countif formula if column E cell value = "T", if it does not equal "T," then skip or end sub.

Sub Total()
Dim lastRow As Long


    lastRow = Range("B" & ROWS.Count).End(xlUp).ROW
    
    
   
    If Range("E1").Value <> "T" Then
    Exit Sub
    'Range("F1:F100").FormulaR1C1 = "=COUNTIF(R4C2:R" & lastRow & "C2,RC2)"
    Else
    If Range("E1").Value = "T" Then
    Range("F1:F100").FormulaR1C1 = "=COUNTIF(R4C2:R" & lastRow & "C2,RC2)"
    End If
    Exit Sub
   
    
   
End If
End Sub

Re: Confused on Countif in VBA - Share insight and some Help?

Hi,

if you look at my file, i have worksheets before and after. in the after worksheet, i need help with summing up the individual transactions lines. if you look at the before worksheet, i have 4 total rows/lines relating to "O123", 3 for "O456" and 4 for 'O789." in the after worksheet, i need column F re-updated with the correct number of transactions.

i am using this code to sum up duplicates, but i cannot add up the aggregate.

[CODE]
[Sub SUM_DUP_ROWS()
    arow = 2
    Do While Cells(arow, 1).Text <> ""
        If Cells(arow, 1).Text = Cells(arow - 1, 1).Text And Cells(arow, 2).Text = Cells(arow - 1, 2).Text And Cells(arow, 3).Text = Cells(arow - 1, 3).Text And Cells(arow, 4).Text = Cells(arow - 1, 4).Text And Cells(arow, 5).Text = Cells(arow - 1, 5).Text Then
            Cells(arow - 1, 6) = Cells(arow - 1, 6) + Cells(arow, 6)
            Cells(arow - 1, 7) = Cells(arow - 1, 7) + Cells(arow, 7)
            Cells(arow - 1, 8) = Cells(arow - 1, 8) + Cells(arow, 8)
            Cells(arow - 1, 9) = Cells(arow - 1, 9) + Cells(arow, 9)
            Cells(arow - 1, 10) = Cells(arow - 1, 10) + Cells(arow, 10)
            Cells(arow - 1, 11) = Cells(arow - 1, 11) + Cells(arow, 11)
            Cells(arow - 1, 12) = Cells(arow - 1, 12) + Cells(arow, 12)
            ROWS(arow).Delete
        Else
            arow = arow + 1
        End If
    Loop
End Sub
/CODE]

Re: Confused on Countif in VBA - Share insight and some Help?

in terms of a way of determining the boundaries, can you provide an example of how to find the first instance of you? Or would it be better to use column M with cell value "F?"

Re: Confused on Countif in VBA - Share insight and some Help?

let me try these options and will go from here. thanks guys!

Re: Confused on Countif in VBA - Share insight and some Help?

Hi Luke,

thank you for the start. its getting me to think a bit clearer. but range F can by dynamic, it can more than 3 rows, say 50-1000 for the countif part and over 5000 rows for the sumif part.

Hi,

I am trying to write a macro that automatically counts the number or rows based on a value/critieria. I labeled the excel sheets before and after. Basically i need count/sum up the number of rows within the dynamic range (in this case the range starts from (B4:B13). In excel, i can use countif($b$4:$b1$3,b1) to return the updated number of rows. But how do i update the first 3 cells in column f, g, h, I, j, k and L?

just fyi,

in column A, ABC = the total count/sum of the rows and values
in column A, YOU = the individual count/sum (which totals up to ABC)