Posts by rollis13

Glad we were able to help.

This would be the syntax for cell C2 in a macro:

Range("C2").NumberFormat = "\""dd/mm/yyyy\"""

but it would be quicker if you manually select the entire column and apply \"gg/mm/aaaa\" as Custom Format to the cells.

Apply this Custom Format \"gg/mm/aaaa\" to your column of dates; could it be a solution for you ?

Logit, at least we gave the same answer (me first ) HERE

Sorry, not for me, one thread one question.

So that means that you need a static range for pasting. No need to use variable 'lr2':

Option Explicit
Sub Filter_Copy_Paste()
    Dim lr1    As Long
    'Dim lr2    As Long
    With Sheets("Market Data")
        lr1 = .Cells(Rows.Count, 1).End(xlUp).Row 'last used row in sheet Market Data
        .AutoFilterMode = False
        .Range("A3:Y" & lr1).AutoFilter Field:=3, Criteria1:="ALBANIA" 'filter range on ALBANIA
        'lr2 = Sheets("Template Data").Cells(Rows.Count, 1).End(xlUp).Row + 1 'first unused row in sheet Template Data
        '.Range("E4:Y" & lr1).SpecialCells(xlCellTypeVisible).Copy Sheets("Template Data").Range("A" & lr2) 'Copy/Paste
        .Range("E4:Y" & lr1).SpecialCells(xlCellTypeVisible).Copy Sheets("Template Data").Range("A20") 'Copy/Paste      '<- changed
        .AutoFilterMode = False
    End With
    Application.CutCopyMode = False
End Sub

Thanks for the positive feedback, glad having been of some help.

This could be a macro for you to be pasted in a standard module.

Option Explicit
Sub Filter_Copy_Paste()
    Dim lr1    As Long
    Dim lr2    As Long
    With Sheets("Market Data")
        lr1 = .Cells(Rows.Count, 1).End(xlUp).Row 'last used row in sheet Market Data
        .AutoFilterMode = False
        .Range("A3:Y" & lr1).AutoFilter Field:=3, Criteria1:="ALBANIA" 'filter range on ALBANIA
        lr2 = Sheets("Template Data").Cells(Rows.Count, 1).End(xlUp).Row + 1 'first unused row in sheet Template Data
        .Range("E4:Y" & lr1).SpecialCells(xlCellTypeVisible).Copy Sheets("Template Data").Range("A" & lr2) 'Copy/Paste
        .AutoFilterMode = False
    End With
    Application.CutCopyMode = False
End Sub

As per your example, since your Dim Loc As String is inside the event Sub Worksheet_SelectionChange it will inizialize (to empty) everytime the event triggers. Move the Dim just before the Sub and your variable will retain the last input.

If you put the Call inside the If/Then, and you are clicking outside the range, the Call will never execute; therefore A1 will not change.

Here is where LocVar gets assigned:

If Not Intersect(Target, Range("F9")) Is Nothing Then
    Range("A" & Sheets("VBA Data").Range("O35")).Select '<---- takes user where he needs to be
    LocVar = "AFR"
End If

but when you get to the Second Step, since your macro has skipped First Step because you aren't in cell F9, LocVar will not be assigned, that's why when you are in cells N103-N130 your variable will result empty.

For j = 0 To 27                               '28 day planning horizon
    If Not Intersect(Target, Range("N" & 103 + j)) Is Nothing Then
        Dt_Req = Target.Value
        'Call Loc_Summary(LocVar, Dt_Req)
        MsgBox LocVar & " , " & Dt_Req        '<- - - - - - -now, the box shows ", 02/05/2022" What made LocVar disappear ?? Retriggering the event ?
    End If                                    '  ^-------------------------^
Next

Thanks for the positive feedback, glad having been of some help.

This is the snippet I usually use with VBE:

Option Explicit
Sub CheckOfficeVersion()
    #If Win64 Then
        MsgBox "64-bit Microsoft Office"
    #Else
        MsgBox "32-bit Microsoft Office"
    #End If
End Sub

You could work on something like this:

For Each cell In Sheets("MySheet").Columns(17).Cells
    Select Case cell.Value
        Case "-29.75"
            If cell.Offset(0, -6) = "02-01-2022" Then
                cell.Offset(0, 2).Value = "-8.33"
            ElseIf cell.Offset(0, -6) = "03-01-2022" Then
                cell.Offset(0, 2).Value = "-10.50"
            End If
    End Select
Next

Thanks for the positive feedback, glad having been of some help.

Should be better:

Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, ActiveCell.Column)).Copy

Have a try with my macro to be pasted in the sheet's module (the one with the dropdowns) and change the date format of the time-stamp if necessary:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If (Target.Column = 6 Or Target.Column = 8 Or Target.Column = 10) Then Target.Offset(0, 1) = Format(Now, "mm/dd/yyyy hh:mm:ss")
    Application.EnableEvents = True
End Sub

Maybe:

Range(ActiveCell, Cells(ActiveCell.Row)).Copy

Thanks for the positive feedback, glad having been of some help.

Yes that's right; as you may understand, the data is too long or too wide to fit in one page, you must decide, or reduce the hight of the rows or reduce the width of the columns in your sheet. Then you can go back to:

.FitToPagesWide = 1
.FitToPagesTall = 1

Instead of this:

lr = pasteSheet.Range("D" & Rows.Count).End(xlUp).Row

use this:

lr = pasteSheet.Range("D1:K" & Rows.Count).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row