Solved: Copy/Paste rows on two sheets simultaneously..

[sunilmulay]

Hi There

I hope someone can help me with this. I'm new to VBA and have put together the following code with assistance from others that inserts specific rows at the Active Cell depending on a condition.

What I want to do is make the macro perform exactly the same function on a second sheet.

For example, if a user currently clicks on Cell D22 in sheet A and runs the macro, rows 22:29 of Sheet A will be inserted above it. I want the marco to also insert rows 22:29 from Sheet B above Cell D22 in Sheet B.

how can I achieve this?

BTW, the history behind this is that I ran out of columns on Sheet A, so I created a Sheet B. Trouble is that I'm giving the user the option to insert/Delete rows via a Button, and that is why the macro needs to carry out the command on both sheets.

Hope someone can help!! Thanks
Sunil

Sub InsertTask01()
'
'
Dim SelectedRow As Long

SelectedRow = ActiveCell.row

If Cells(SelectedRow, 2).Value = 1 Then
ActiveSheet.Rows("22:29").Copy
Range("A" & SelectedRow).Insert Shift:=xlDown
Else
ActiveSheet.Rows("24:29").Copy
Range("A" & SelectedRow).Insert Shift:=xlDown
Application.CutCopyMode = False
End If

End Sub

[Demosthine]

Hi there.

To copy it to the second worksheet, run the .Insert command on "Sheet2".


ActiveSheet.Rows("22:29").Copy
Range("A" & SelectedRow).Insert Shift:=xlDown
Worksheet("Sheet B").Range("A" & SelectedRow).Insert Shift:=xlDown


Scott

[mikerickson]

You could pass the sheets (both ActiveSheet and the other one) as as arguments
Sub Main()
Call modifySheet(ActiveSheet)
Call modifySheet(ThisWorkbook.Sheets("otherSheet"))
End Sub

Sub modifySheet(thisSheet as Worksheet)
With thisSheet
Rem do stuff
End With
End Sub

[rbrhodes]

Hi sunil,

Is this correct?


Sub InsertTask01dr()
'
'
Dim SelectedRow As Long
Dim SheetB As Worksheet

Set SheetB = Sheets("SheetB") ' <---rename to suit
SelectedRow = ActiveCell.Row

If Cells(SelectedRow, 2).Value = 1 Then
ActiveSheet.Rows("22:29").Copy
Range("A" & SelectedRow).Insert Shift:=xlDown
With SheetB
.Rows("22:29").Copy
.Range("A" & SelectedRow).Insert Shift:=xlDown
End With
Else
ActiveSheet.Rows("24:29").Copy
Range("A" & SelectedRow).Insert Shift:=xlDown
With SheetB
.Rows("24:29").Copy
.Range("A" & SelectedRow).Insert Shift:=xlDown
End With
End If
Application.CutCopyMode = False

Set SheetB = Nothing
End Sub

[sunilmulay]

Hi Guys
thanks so much for your responses.....I tried the last one from rbrhodes, and it worked a treat!!
S