Range as long macro

[KaaNunL]

Hi Guys,

I have selected DIm Ingrows As Long and set it up as Range ("C2:C" & lngrows & ",D2:D" & lngrows & ",E2:E" & lngrows & ",F2:F" & lngrows & ",G2:G" & lngrows & ",H2:H" & lngrows & ",I2:I" & Ingrows & ",J2:J").Select Selection.Copy

some how it does not work most probably i am selecting something wrong or missed

Do you have any idea how to finish this ?

Sub GetFilterForFails12()
'
' GetFilterForFails Macro
'


'


    Dim CurrentYear As Integer
    Dim Previousweek As Integer
    Dim slaUK As String
    Dim lFails As Long
    Dim Ingrows As Long
    


    CurrentYear = DatePart("yyyy", Date)
    Previousweek = DatePart("ww", Date) - 1
    slaUK = "SLA builder UK.xlsm"


    Sheets("SLA - Order delivery").Select
    Selection.AutoFilter
    ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").Range.AutoFilter Field:=30, _
        Criteria1:=CurrentYear
    ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").Range.AutoFilter Field:=29, _
        Criteria1:=Previousweek
    ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").Range.AutoFilter Field:=28, _
        Criteria1:="Fail"
        
Range("C2:C" & lngrows & ",D2:D" & lngrows & ",E2:E" & lngrows & ",F2:F" & lngrows & ",G2:G" & lngrows & ",H2:H" & lngrows & ",I2:I" & Ingrows & ",J2:J").Select
    Selection.Copy
    Windows("SLA mitigation template v2.xlsx").Activate
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("SLA builder UK.xlsm").Activate


        On Error Resume Next
lFails = ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count
On Error GoTo 0


If lFails = 0 Then MsgBox "Keep Calm and There were no FAILS this week :)"
        
End Sub

Many Thanks

 

[Fluff]

Try

Range("C2:J" & lngrows).Select

 

[KaaNunL]

Try

Range("C2:J" & lngrows).Select

Nope it did not work

 

[JoeMo]

Your code doesn't assign a value to the variable Ingrows so, at the point where you use it, its value is the default value for a long of zero. There is no row 0 so this will cause a run time error there. Is that what you mean by "Nope it did not work"?

 

[KaaNunL]

Your code doesn't assign a value to the variable Ingrows so, at the point where you use it, its value is the default value for a long of zero. There is no row 0 so this will cause a run time error there. Is that what you mean by "Nope it did not work"?

Yes, i am not really good with this yet but thanks for the explain. Basically i do receive en error message after i run" Compile error: Variable not defined "
how can i fix this ?

 

[Fluff]

You have a mix of "Ingrows" and"lngrows" (note the first letter).
Make sure they are all the same and as JonMo pointed out, you haven't given it a value.

 

[KaaNunL]

You have a mix of "Ingrows" and"lngrows" (note the first letter).
Make sure they are all the same and as JonMo pointed out, you haven't given it a value.

Sub Macro1TEST()
'
' Macro1TEST Macro
'


Dim CurrentYear As Integer
Dim Previousweek As Integer
Dim lngrows As Integer
Dim slaUK As String
Dim lFails As Long

CurrentYear = DatePart("yyyy", Date)
Previousweek = DatePart("ww", Date) - 1
slaUK = "SLA builder UK.xlsm"


Sheets("SLA - Order delivery").Select
Selection.AutoFilter
ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").Range.AutoFilter Field:=30, _
Criteria1:=CurrentYear
ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").Range.AutoFilter Field:=29, _
Criteria1:=Previousweek
ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").Range.AutoFilter Field:=28, _
Criteria1:="Fail"

lngrows = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("C3:AL" & lngrows).Copy

Windows("SLA_mitigation_ template_ v2_Blank.xlsx").Activate
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows("SLA builder UK.xlsm").Activate

On Error Resume Next
lFails = ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count
On Error GoTo 0


If lFails = 0 Then MsgBox "Keep Calm and There were no FAILS this week "



End Sub

This worked

i have another question so if i do not open SLA_mitigation_ template_ v2_Blank.xlsx file than it does not find it and it does not work. Is there any way i can set it up without opening the file ?
i have tried Dim MitPath As String and MitPath = "N:\CCHU_IPO\Balázs\Reporting\Customer reports\DeutscheBank\Weekly\SLA\Hardware\SLA_mitigation_ template_ v2_Blank".xlsm" but no luck

Also what i have use as a blank excel sheet i want to save as somewhere else is there any way i can do that ?


Thank you for help

 

[Fluff]

The other workbook needs to be open.
You can either open it manually before the code is run, or you can open it in the code.

 

[KaaNunL]

The other workbook needs to be open.
You can either open it manually before the code is run, or you can open it in the code.

Okay so i have found something called Workbooks.Open

Workbooks.Open "N:\CCHU_IPO\Balázs\Reporting\Customer reports\DeutscheBank\Weekly\SLA\Hardware\SLA_mitigation_ template_ v2_Blank.xlsx"

Workbooks("SLA_mitigation_ template_ v2_Blank.xlsx").Sheets("File to update").Activate
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Workbooks("SLA builder UK.xlsm").Activate

And this is working without opening the workbook

if you can answer my last question i would be thankful (how can i save this blank file like save as using macro ? and idea ?

Thank you

 

[DanteAmor]

Two details The variable is called "ingrows" with vowel "i" and in the copy you are putting "Lngrows" with consonant "L".
Second, nowhere are you putting the value of "Ingrows".

Try this

Sub GetFilterForFails12()
' GetFilterForFails Macro
'
    Dim CurrentYear As Integer
    Dim Previousweek As Integer
    Dim slaUK As String
    Dim lFails As Long
    Dim [COLOR=#0000ff]Ingrows [/COLOR]As Long
    
    CurrentYear = DatePart("yyyy", Date)
    Previousweek = DatePart("ww", Date) - 1
    slaUK = "SLA builder UK.xlsm"


    Sheets("SLA - Order delivery").Select
    Selection.AutoFilter
    
    [COLOR=#0000ff]Ingrows [/COLOR]= Range("C" & Rows.Count).End(xlUp).Row
    
    ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").Range.AutoFilter Field:=30, _
        Criteria1:=CurrentYear
    ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").Range.AutoFilter Field:=29, _
        Criteria1:=Previousweek
    ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").Range.AutoFilter Field:=28, _
        Criteria1:="Fail"


    On Error Resume Next
    lFails = ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count
    On Error GoTo 0
    If lFails = 0 Then
        MsgBox "Keep Calm and There were no FAILS this week :)"
    Else
        Range("C2:L" & [COLOR=#0000ff]Ingrows[/COLOR]).Copy
        Workbooks("SLA mitigation template v2.xlsx").Sheets(1).Range("A3").PasteSpecial xlPasteValues
        MsgBox "Fails copied"
    End If
End Sub