Selecting and copying data

[SlinkyWheels]

Hi

Is it possible to copy a row of data from "Sheet 1" and paste it into "Sheet 2" without actually selecting "Sheet 1"

ie, I am trying to write some code that will work whilst in "Sheet 2" but never actually goes into "Sheet 1"

Does this make any sense?

 

[njimack]

Sheets("Sheet1").Range("A1:A100").Copy Destination:=Sheets("Sheet2").Range("A1")

 

[ravishankar]

Hi
try

Worksheets("sheet1").Rows(51).Copy
Rows(12).PasteSpecial

pastes row 51 of sheet 1 to 12th row of sheet2 (active sheet)
Ravi

 

[SlinkyWheels]

Thanks both, really good.

This now leads me to another problem.

My code is needed on a "Worksheet_activate" macro.

The trouble is the first part requires me do a loop through "Sheet 1", which I can do but, I cant get the loop to start in cell "B12" without selecting "Sheet 1" first. If I do this, I would need to select "Sheet 2" at the end of the macro and this would initiate the macro all over again.

So........basically......all I am trying to do is set "B12" as the start of my loop without actually going into "Sheet 1" do do it.

Can anyone help?

 

[njimack]

Depends what you're trying to do, but I'd have thought the following would work...

With Sheets("Sheet1").Range("B1")
'your code goes here, (eg .Copy)
End With

 

[SlinkyWheels]

Hi

That is the code I tried but it is not starting from "B12".

It is looping from wherever the active cell is on "Sheet 1".

Very confusing.

 

[SlinkyWheels]

Tell a lie!!!

It is looping on "Sheet 2" not "Sheet 1"

Really confused now!!

 

[njimack]

If you need help rewriting your code, post the offending portion and explain what you're trying to do.

 

[SlinkyWheels]

Suddenly realised I had not posted my code. I need more coffee!!

Ok, here is the code.

Private Sub Worksheet_Activate()

Dim Proj As Variant

n = ActiveSheet.Name

Set Proj = Range("B11")
Proj.Select
ActiveCell.Offset(0, 29).Select
Sheets("POST APPROVAL").Unprotect "Password"

With Sheets("POST APPROVAL").Range("B12")
Do Until ActiveCell = Blank

If ActiveCell.Value = Proj Then
If ActiveCell.Offset(0, -1).Value = "Post Approval" Then
GoTo Dunc
Exit Sub
End If
End If
ActiveCell.Offset(1, 0).Select
Loop
End With
Dunc:
ActiveCell.Offset(0, 29).Activate
Sheets("POST APPROVAL").Range("AE12").Resize(1, 121).Copy
Sheets(n).Range(Proj).Offset(0, 29).PasteSpecial Paste:=xlValues

End Sub

This basically runs when the sheet (Sheet 2) is selected.
Then unprotects the "Post Approval" sheet, finds "Proj" on this sheet, then offsets, then resizes, copies and finally pastes back onto Sheet 2

 

[njimack]

Without seeing the layout of your file, I'm struggling to understand your code (I think the resize statement would be redundant if you used a dynamic range), but this should be more efficient

Private Sub Worksheet_Activate()
Dim Proj As Variant
Dim n As String
Dim i As Long, End_Row As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

n = ActiveSheet.Name

Set Proj = Range("B11")

With Sheets("POST APPROVAL")
.Unprotect "Password"

End_Row = .Range("B12").End(xlDown).Row

For i = 12 To End_Row

If .Cells(i, 2).Value = Proj And Cells(i, 1).Value = "Post Approval" Then
Sheets("POST APPROVAL").Range("AE12").Resize(1, 121).Copy
Sheets(n).Range(Proj).Offset(0, 29).PasteSpecial xlValues

Exit Sub
End If

Next i
End With

End Sub