From one sheet to another...

[MontySharpei]

Hello,

can anyone help me ?

I have two sheets in my workbook. sheet 1 and sheet 2. I have a row in sheet 1 (row12). Is there a code I can use that when a button is pressed in sheet 2 this row is copied across into sheet 2 (row12). Then once the button is pressed once again (row 12 sheet 1) is copied across into row 13 sheet 2 and if pressed again row 12 sheet 1 copies across to sheet 2 row 14. and so on etc... etc....

[Simon Lloyd]

This should do what you need!

Sub Copytosheet2()
Dim i As Long
i = 0
Sheets("Sheet1").Rows(12).Copy
Do
If Sheets("Sheet2").Range("A" & 12 + i).Value <> "" Then
i = i + 1
End If
Loop Until Sheets("Sheet2").Range("A" & 12 + i).Value = ""
Sheets("Sheet1").Rows(12).Copy Destination:=Sheets("Sheet2").Range("A" & 12 + i)
End Sub

[Bob Phillips]

How abou this to dispense with the loop Simon



Sub Copytosheet2()
Dim i As Long
With Sheets("Sheet1")
.Rows(12).Copy
i = .Evaluate("MAX(MAX(IF(Sheet2!A1:A1000<>"""",ROW(Sheet2!A1:A1000)))+1,12)")
.Rows(12).Copy Destination:=Sheets("Sheet2").Range("A" & i)
End With
End Sub

[Simon Lloyd]

You truly are the formula king!!, i don't expect the Op will entirely understand the formula against being able to follow a loop but of course yours will be faster over many rows. Am i right in my assumption that MAX doesnt work over an entire column like ("A:A")?, i think i have come across it before where you have to give row numbers for your range.

[Bob Phillips]

It is not the MAX that is the problem Simon, it is the IF. IF is a single cell test, so if you want to test an array, you need an array formula, hence not whole columns.

[MontySharpei]

Thankyou !!!

These work a treat and I am forever in your debt....