Do I need .SELECT here? Columns not filling as expected.

[logandiana]

Here's a short section of my code.

With comp
    .Range("A1").PasteSpecial xlPasteValues
    .Rows("1:3").Delete
    .Columns("B:B").Delete
    .Columns("A:B").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
LR2 = comp.Cells(Rows.Count, 1).End(xlUp).Row
    .Range("A1:B" & LR2).Copy
End With
With temp
    .Range("J2").PasteSpecial xlPasteValues
LR3 = temp.Cells(Rows.Count, 10).End(xlUp).Row
    .Range("Z2:Z" & LR3).Value = Range("K2:K" & LR3).Value
    .Range("K2:K" & LR3).Value = Range("J2:J" & LR3).Value

Once it pastes into J2 on temp it doesn't seem to do the following lines of code.
The code doesn't stop, it just ignores it.
LR3 does get defined.
If I put select in there, like this

With temp
    .Select
    .Range("J2").PasteSpecial xlPasteValues
LR3 = temp.Cells(Rows.Count, 10).End(xlUp).Row
    .Range("Z2:Z" & LR3).Value = Range("K2:K" & LR3).Value
    .Range("K2:K" & LR3).Value = Range("J2:J" & LR3).Value

it works as it should, but why do I need to do that for it to work.

 

[mole999]

is temp = sheets("temp"), i'm wondering if its a reserved word, because you should be able to

With sheets("temp")
'.Select
.Range("J2").PasteSpecial xlPasteValues
LR3 = sheets("temp").Cells(Rows.Count, 10).End(xlUp).Row
.Range("Z2:Z" & LR3).Value = Range("K2:K" & LR3).Value
.Range("K2:K" & LR3).Value = Range("J2:J" & LR3).Value
end with

 

[logandiana]

is temp = sheets("temp"), i'm wondering if its a reserved word, because you should be able to

With sheets("temp")
'.Select
.Range("J2").PasteSpecial xlPasteValues
LR3 = sheets("temp").Cells(Rows.Count, 10).End(xlUp).Row
.Range("Z2:Z" & LR3).Value = Range("K2:K" & LR3).Value
.Range("K2:K" & LR3).Value = Range("J2:J" & LR3).Value
end with

the sheet name is "Template" and I Set temp = sheets("Template")
let me be clear, that it works with the .Select, just wondering why it doesn't work without it

 

[logandiana]

I just changed the sheet name and Dim to something else and it still doesn't work, so if temp is reserved, then that's not the issue here

 

[Fluff]

How about

With temp
    .Range("J2").PasteSpecial xlPasteValues
LR3 = temp.Cells(Rows.Count, 10).End(xlUp).Row
    .Range("Z2:Z" & LR3).Value = [COLOR=#ff0000].[/COLOR]Range("K2:K" & LR3).Value
    .Range("K2:K" & LR3).Value = [COLOR=#ff0000].[/COLOR]Range("J2:J" & LR3).Value
End With

 

[logandiana]

How about

With temp
    .Range("J2").PasteSpecial xlPasteValues
LR3 = temp.Cells(Rows.Count, 10).End(xlUp).Row
    .Range("Z2:Z" & LR3).Value = [COLOR=#ff0000].[/COLOR]Range("K2:K" & LR3).Value
    .Range("K2:K" & LR3).Value = [COLOR=#ff0000].[/COLOR]Range("J2:J" & LR3).Value
End With

This is the code that I have that is not working. It only works when I put .select somewhere in between

With temp

and

.Range("Z2:Z" & LR3).Value = [COLOR=#ff0000].[/COLOR]Range("K2:K" & LR3).Value

 

[Fluff]

Your code does not have a period in front of the 2 ranges on the right of the = sign.
You had

With temp
    .Range("J2").PasteSpecial xlPasteValues
LR3 = temp.Cells(Rows.Count, 10).End(xlUp).Row
    .Range("Z2:Z" & LR3).Value = Range("K2:K" & LR3).Value
    .Range("K2:K" & LR3).Value = Range("J2:J" & LR3).Value
End With

rather than

With temp
    .Range("J2").PasteSpecial xlPasteValues
LR3 = temp.Cells(Rows.Count, 10).End(xlUp).Row
    .Range("Z2:Z" & LR3).Value = [COLOR=#ff0000].[/COLOR]Range("K2:K" & LR3).Value
    .Range("K2:K" & LR3).Value = [COLOR=#ff0000].[/COLOR]Range("J2:J" & LR3).Value
End With

note the . in red

 

[logandiana]

Your code does not have a period in front of the 2 ranges on the right of the = sign.
You had

With temp
    .Range("J2").PasteSpecial xlPasteValues
LR3 = temp.Cells(Rows.Count, 10).End(xlUp).Row
    .Range("Z2:Z" & LR3).Value = Range("K2:K" & LR3).Value
    .Range("K2:K" & LR3).Value = Range("J2:J" & LR3).Value
End With

rather than

With temp
    .Range("J2").PasteSpecial xlPasteValues
LR3 = temp.Cells(Rows.Count, 10).End(xlUp).Row
    .Range("Z2:Z" & LR3).Value = [COLOR=#ff0000].[/COLOR]Range("K2:K" & LR3).Value
    .Range("K2:K" & LR3).Value = [COLOR=#ff0000].[/COLOR]Range("J2:J" & LR3).Value
End With

note the . in red

Okay! Yes I didn't see that.
I added them and it works as it should, without the select.
Can I ask, what is the rule here? I know I've got this to work before as I had it, although I am not sure it was to a sheet that wasn't the active sheet.
So if I am on the active sheet and my WITH statement is only concerning the non active sheet, then I need the period. ?
Thanks

 

[Fluff]

Anything inside a With statement that starts with a . (period) belongs to the With, so both of the following are the same

Sheets("sheet1").Activate
With Sheets("Sheet2")
   .Range("A1").Value = .Range("B1").Value
End With

Sheets("Sheet2").Range("A1").Value = Sheets("Sheet2").Range("B1").Value

but if you have a range inside a with that doesn't start with a . it's working on the activesheet, so these 2 are the same

Sheets("sheet1").Activate
With Sheets("Sheet2")
   .Range("A1").Value = Range("B1").Value
End With

Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("B1").Value

 

[logandiana]

Anything inside a With statement that starts with a . (period) belongs to the With, so both of the following are the same

Sheets("sheet1").Activate
With Sheets("Sheet2")
   .Range("A1").Value = .Range("B1").Value
End With

Sheets("Sheet2").Range("A1").Value = Sheets("Sheet2").Range("B1").Value

but if you have a range inside a with that doesn't start with a . it's working on the activesheet, so these 2 are the same

Sheets("sheet1").Activate
With Sheets("Sheet2")
   .Range("A1").Value = Range("B1").Value
End With

Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("B1").Value

Perfect explanation thanks! I learn something new every day