Posts by sktneer

You should try it like this...

Range(strt_date_cell).Resize(, NoDays).DataSeries Rowcol:=xlRows, Type:=xlChronological, Date:= _
xlDay, Step:=1, Stop:=EndDate, Trend:=False

Note how the Resize arguments are set.

You're welcome Ape!

You're welcome! Glad it worked as desired.

Deleted

While placing the formula through VBA, you need to double the double quotes if used in the formula. So the line which inserts the formula in the cell should be like this...

Range("J14").Formula = "=IFERROR(VLOOKUP(J13,Sheet2!A2:C202,3,FALSE),[COLOR=#FF0000][B]""""[/B][/COLOR])"

You should declare the Public Boolean variable DisableX on a Standard Module.
And where do you change the value of DisableX? Is it ever set to be True? Otherwise it will remain False and you cannot close the workbook.

You're welcome!

Please find the attached with two codes on Report Sheet Module.

1) The Sheet Activate Code will insert a drop down list based on values in column F on Dump Sheet i.e. unique location codes.
2) The Change Event Code will populate the relevant data once you select a location code in E2

So do you want to select the location code in E2 on Report tab and macro to show the relevant data under TPI Summay?

Please upload a small sample workbook with a layout which is similar to the layout of your original file and mock up the desired output on another sheet to visualize the end output you are trying to achieve.

You're welcome Michael!

Or you may try something like this...

=IF(LEFT(A2,1)="A","UA","UE")&TEXT(LEFT(RIGHT(A2,LEN(A2) - MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")) +1),2),"00")&TEXT(B2,"mmddyy")

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Formula.jpg","data-attachmentid":1213916}[/ATTACH]

You're welcome!

ws2.Range("A" & Rows.Count).End(3)(2) is equivalent to ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).

You may try something like this...

Set rng1 = ws1.Range(ws1.Cells(2, 4), ws1.Cells(lr1, 4))
Set rng2 = ws1.Range(ws1.Cells(2, 5), ws1.Cells(lr1, 5))


rng1.Copy ws2.Range("A2")
rng2.Copy ws2.Range("A" & Rows.Count).End(3)(2)


With ws2
    .Range("A1").Value = "Lista med Domare"
    .Range("A1").Font.Bold = True
    .Range("A1").CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
    .Columns(1).AutoFit
End With

That didn't work because the times in column D are times as string. Change the formula to the following one and it will work.

=AND(D3<>"",D3*1>TIMEVALUE("00:10:00"))

You can also make a new rule for conditional formatting using the formula given below...

=AND(D3<>"",D3>TIMEVALUE("00:10:00"))

Please give this a try...

Sub CopyToArchive()
Dim sws As Worksheet, dws As Worksheet
Dim tbl As ListObject


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set sws = Sheets("DRS REGISTER")
Set tbl = sws.ListObjects(1)
Set dws = Sheets("ARCHIVE")
sws.AutoFilterMode = False


With tbl.Range
    .AutoFilter field:=11, Criteria1:="YES"
    If tbl.Range.Columns(11).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
        tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
        dws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
        tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    End If
    tbl.Range.AutoFilter field:=11
End With


Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Click the button called "Copy To Archive " on DRS Register Sheet to run the code.

[ATTACH]n1207666[/ATTACH]

phill6815

You will need a change event code to achieve that. Please open a new question with your requirement.

Upload your file here to see why it is not working.

You may try something like this...

Sub SelectSheet()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Range("A1").Value = WorksheetFunction.WeekNum(Date, vbMonday) Then
        ws.Activate
        Exit For
    End If
Next ws
End Sub