Copy pasting & loop

[Paul Norris]

Hi all

hope someone can help.
I have a sheet which updates using a dropdown for it criteria, what I need to do is copy that data from sheet1 to sheet2, straight forward on that, however when I next change my criteria I need to copy & paste to sheet 2 below the original data by 2 rows.

The data is A4:V19 on sheet1.
On sheet1 their is no data in cells A5:C19 the data thens needs to be copied to Sheet2
Sheet 2 the data needs to be pasted to A1:V16,

I then choose another criteria on sheet1 this then needs to be pasted under A16 by 2 rows.

Any assistance appreciated.
Thanks in advance
Paul

 

[kevin lazell]

hi paul
the following is untested but should do what you want

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
x = Sheets("sheet2").Range("a" & Rows.Count).End(xlUp).Row
If x > 1 Then x = x + 2
Range("a4:v19").Copy
Sheets("sheet2").Range("a" & x & ":v" & x).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

cheers

kevin

 

[Paul Norris]

hi paul
the following is untested but should do what you want

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
x = Sheets("sheet2").Range("a" & Rows.Count).End(xlUp).Row
If x > 1 Then x = x + 2
Range("a4:v19").Copy
Sheets("sheet2").Range("a" & x & ":v" & x).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

cheers

kevin

Thanks Kevin I will give it a try,

 

[kevin lazell]

hi paul
just realised i made a mistake the following should work


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
x = Sheets("sheet2").Range("a" & Rows.Count).End(xlUp).Row
y = Sheets("sheet2").Range("v" & Rows.Count).End(xlUp).Row
If x = 1 Then y = 16

If x > 1 Then x = x + 3: y = y + 18
Range("a4:v19").Copy
Sheets("sheet2").Range("a" & x & ":v" & y).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

cheers

kevin

 

[Paul Norris]

hi paul
just realised i made a mistake the following should work


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
x = Sheets("sheet2").Range("a" & Rows.Count).End(xlUp).Row
y = Sheets("sheet2").Range("v" & Rows.Count).End(xlUp).Row
If x = 1 Then y = 16

If x > 1 Then x = x + 3: y = y + 18
Range("a4:v19").Copy
Sheets("sheet2").Range("a" & x & ":v" & y).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub





kevin

Thanks will let you know if it works which i believe it will.

Paul.