Posts by takhan1

Quote from Keebellah

Hi, I just got the message today, will see what I can do for you.
Cheers,

Thank you Keebellah, looking forward to see it.

Regards,

Quote from ashu1990

you have to give the file a path hence its saving it in a unknown location, below code will save the file where you have saved your macro file.

Code

ActiveWorkbook.SaveAs thisworkbook.path & "\" & Replace(Range("L6").Value & "-" & Range("N2").Value & "-" & Range("A6").Value, ".", "") & ".xlsm", 52

Thank you Ashu and again very sorry for late reply as I had not tried it until today.

It worked great, did some further tinkering to customise it.

Thank you for your help!

Regards,

Thanks Jonathan,

Never though of this.

Regards,

Quote from JonathanVH

I would turn the range into a table and then show the users how to use the dropdown filter on the column header.

Hi Jonathan,

The data is in table format, it is an action list where I want to hide the actions which have been marked as closed. I can upload a blank version if it helps?

Any help please?

Regards,

Quote from ashu1990

this will do the trick removed . and replaced it with space as it hampers saving excel file format

Code

ActiveWorkbook.SaveAs Replace(Range("L6").Value & "-" & Range("N2").Value & "-" & Range("A6").Value, ".", "") & ".xlsm", 52

Hi Ashu,

Thank you for feedback, I haven't had the chance until now to try this. Unfortunately following error is coming up,

Run-Time Error - 1004. I have uploaded the screenshot as attachment.

Many Thanks for your help.

Regards,

Quote from Keebellah

If you would take the time to attach the file (remove sensitive data) it will make is easier to work with the data instead of filling in something ourselfves

Hi,

As requested, Please see attached file.

Any help would be appreciated.

Regards,

Hi All,

Need your help in achieving following please.

Save As file in same folder using value from L6 (Date), N2 (Revision No.) and A6 (Name of Section).
Format will be 21.08.2018 (L6) – v2.0(N2) – ABC 546(A6).xlsm

Apologies if it has been requested before but I could not find any specific reference. Any help in above will be appreciated.

Hi All,

Need your help in achieving following please.

I am trying to work out is to hide the rows based on their value in Column M – Values are Completed – Late and Completed -On Time.
Unhide these rows on demand – will add a button on sheet.

Apologies if it has been requested before but I could not find any specific reference. Any help in above will be appreciated.

Hi All,

I am trying to work out VBA code to do the following,

• Select Sheet “ABL”
• Row 10 has headings (column A to N)
• Sort data by Alphabet in Column H – values Critical, High, Medium and Low – Filters are on.
• Select all rows with Critical and High value in Column H.
• Copy this data.
• Select sheet “DBL” – Row 46 has headings and formats, same as of sheet ABL
• Clear all the rows from 47 onwards in sheet DBL – Row 1 – 46 has some graphs.
• Paste the data into DBL from row 47 onwards which was copied from sheet ABL with High and Critical Values (column H). Format must be same.
• Convert DBL into pdf – only rows with data.
• Email this pdf using tbl_Main_Email_List as To: and tbl_CC_Email_list as CC:.

I have worked out a code to do above but main issues are,

• Data will vary from week to week i.e. one week it may only have 5 rows with High and Critical and next week it may have 10, week after maybe 2.
• When the data is pasted into sheet DBL, it does not copy the borders.

Code is pasted below, it does not contain data sort, copy and paste information. It does contain some MsgBox Input which I would like to retain.

Sub Email()
    Application.ScreenUpdating = False
    Sheets("DBL").Select
    Dim AlertInput As String
 
    AlertInput = InputBox("What is the Subject of Email?", "ABC - Project Dashboard Summary")
    If AlertInput = vbNullString Then
        MsgBox ("This Email request has been cancelled...")
    Else
    Dim v As Variant
        v = Environ("Temp") & "\DBL_Summary.pdf"
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=v
       
    Dim toRange As Range
    Dim ccRange As Range
    Dim cl As Range
    Dim toList As String
    Dim ccList As String
    Dim toListCnt As Long
    Dim ccListCnt As Long
           
   
    With Worksheets("Email.List")
    Set toRange = .Range("A3", .Range("A3").End(xlDown))
    toListCnt = WorksheetFunction.CountA(toRange)
   
    Set ccRange = .Range("B3", .Range("B3").End(xlDown))
    ccListCnt = WorksheetFunction.CountA(ccRange)
   
    'MsgBox toListCnt
    End With
    'add main recipients to toList string
    For Each cl In toRange
        If toListCnt = 1 Then
            toList = toList & cl.Value
            Exit For
        End If
        toList = toList & "; " & cl.Value
    Next cl
   
    'add cc recipients to ccList string
    For Each cl In ccRange
        If ccListCnt = 1 Then
            ccList = ccList & cl.Value
            Exit For
        End If   
        ccList = ccList & "; " & cl.Value
    Next cl
    'MsgBox toList
    On Error Resume Next
    With CreateObject("Outlook.Application").CreateItem(0)
        .To = toList
        .Cc = ccList
        .Subject = "Weekly DBL Summary - " & AlertInput
        .Body = ""
        .Attachments.Add v
        .display
    End With
    On Error GoTo 0
    Kill v
    End If
Application.ScreenUpdating = True
End Sub

Apologies if it has been requested before but I could not find any specific reference. Any help in above will be appreciated.

Thank you Alan, it worked as intended.

Great Help.

sorry for not adding the code tags earlier.

Thank You Alan, will follow the rules.

Hi,
I need some help please.
I have a macro recorded in a worksheet. It performs following actions,

• Unprotect Worksheet.
• Insert a row (Row No. 11)
• Unhide row 8 & 9.
• Copy row 8.
• Paste row 8 in No. 11 (incl. conditional formatting, data lists, etc)
• Add a serial no. to a11 by adding 1 in a12.
• Hides row 8& 9.
• Protect sheet.

On item 6, I would like VBA to find highest no. in column A (A11 onwards) and then add a serial no. onto it.

Code is pasted below,

Sub Insert_New_Line_Item()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Rows("11:11").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("6:10").Select
Range("A10").Activate
Selection.EntireRow.Hidden = False
Rows("8:8").Select
Selection.Copy
Rows("11:11").Select
ActiveSheet.Paste
x = Cells(12, 1).Value + 1
Cells(11, 1) = x
Range("I11").Select
ActiveCell.FormulaR1C1 = Date
Rows("8:9").Select
Range("A9").Activate
Selection.EntireRow.Hidden = True
Range("B11").Select
ActiveSheet.Protect
Application.ScreenUpdating = True


End Sub

Apologies if it is a duplicate request.
Any help in above will be appreciated.

Many Thanks,

Thank you Dave,

It worked like a charm:)

Hi,

I am trying to work out a code in a worksheet to change letter "Y" into "1" and letter "N" into"0" when typed in any cell on the sheet excluding column A & B and Rows 1 - 5.

Any help would be appreciated.

Apologies if this is duplicate request but couldn't find any similar post.