VBA - No Select or Activate

[The_Rock]

Hi Folks
I'm trying to delve deeper into VBA and am trying to run the following macro without resorting to 'Select' or 'Activate'.

What I am trying is to get the macro to loop thru two different worksheets, enter a formula, then copy & paste it down to the length of the range.
What happens is that it enters the formula in the correct sheet, but when it has to copy and paste, it does so on the current page!
Appreciate your wisdom on this.

Sub Concat()

''Fill Concats in following sheets
    
    Dim ws As Worksheet, rng As Range
    Dim cpw As Worksheet
    
    Set ws = Sheets("Missing Details - MPR")
    Set cpw = Sheets("CHIP Pull (Website)")
    
  'Missing Details - MPR
    Set rng = ws.Range("A3")
    Range("A3") = "=RC[1]&RC[2]&RC[3]"
    rng.Copy Destination:=Range("A3:A" & Range("C" & Rows.Count).End(xlUp).Row)
    Set rng = ws.Range("A4:A" & Range("C" & Rows.Count).End(xlUp).Row)
    rng.Copy
    rng.PasteSpecial (xlPasteValues)
  'CHIP Pull (Website)

    Set rng = cpw.Range("A2")
    Range("A2") = "=RC[2]&RC[3]"
    rng.Copy Destination:=Range("A2:A" & Range("C" & Rows.Count).End(xlUp).Row)
    Set rng = cpw.Range("A3:A" & Range("C" & Rows.Count).End(xlUp).Row)
    rng.Copy
    rng.PasteSpecial (xlPasteValues)
End Sub

 

[JLGWhiz]

"rng.Copy Destination:=Range("A2:A" & Range("C" & Rows.Count).End(xlUp).Row)"

In your Destination statement you are not qualifying the Range. By "Qualifying", you need to tell VBA which worksheet your destination range is located in. Since you are copying from ws I would assuem that the destination worksheet would be cpw so:

Code:
rng.Copy Destination:=cpw.Range("A2:A" & Range("C" & Rows.Count).End(xlUp).Row)
Code:

 

[Michael M]

Try....untested !!

Sub Concat()

''Fill Concats in following sheets
    
    Dim ws As Worksheet, rng As Range
    Dim cpw As Worksheet, lr As Long
    
    Set ws = Sheets("Missing Details - MPR")
    Set cpw = Sheets("CHIP Pull (Website)")
  'Missing Details - MPR
    lr = ws.Cells(Rows.Count, "C").End(xlUp).Row
    With ws
    .Range("A3:A" & lr).Formula = "=A3&B3&C3"
    .Range("A4:A" & lr).Value = .Range("A4:A" & lr).Value
    End With
  'CHIP Pull (Website)
    lr = cpw.Cells(Rows.Count, "C").End(xlUp).Row
    With cpw
    .Range("A2:A" & lr).Formula = "=B2&C2"
    .Range("A3:A" & lr).Value = .Range("A3:A" & lr).Value
    End With
End Sub

 

[The_Rock]

JLGWhiz - unfortunately that did not work, as it still pasted to the current sheets.

Michael M - worked like a charm and less coding needed to. I've learnt something new

Many thanks to both of you!!