Excel Macro For copy paste

[nh3]

I want to make a macro excel,where i can copy set of dynamic 7 rows & paste in different sheet .For example i copy A1 to A7 rows & paste .Again after click,copy A2 to A8 rows & paste.Again after click,copy A3 to A9 rows & paste.So on.Please help.My range selection should be dynamic.My paste location is fixed,means my new paste value would overwrite my previous value.

 

[MARK858]

I don't see the reason for multiple clicking put the code below in the worksheet module of the sheet being copied from (right click the sheet tab, click view code and paste in the window that appears).
Then double click the first cell in the range to be copied

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Target.Resize(7).Copy Sheets("Sheet2").Range("A7") ' Rename Sheet2 to destination sheets name
End Sub

 

[nh3]

I want to change D1 & E1 value by each click.Because i have lot of data,I want to analysis with 7 set of data.here is my code.
Sub CopyRows()
Dim sourceSheet As Worksheet
Dim destSheet As Worksheet
Dim lastRow As Long
Dim startCell As Range
Dim endCell As Range
Dim destCell As Range

Set sourceSheet = ThisWorkbook.Sheets("Sheet1")
Set destSheet = ThisWorkbook.Sheets("Sheet2")

D1 = 0
E1 = 7

' Find the last row in the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row

' Set the range to copy
Set startCell = sourceSheet.Range("A1").Offset(D1, 0)
Set endCell = sourceSheet.Range("A1").Offset(E1, 0)

' Set the destination cell
Set destCell = destSheet.Range("A1")

' Copy the range and paste it to the destination cell
sourceSheet.Range(startCell, endCell).Copy
destCell.Offset(0, 0).PasteSpecial xlPasteValues
End Sub

 

[MARK858]

You're code is doing the same as the code that posted with the exceptions that you are using a last cell bit of code rather than a fixed cell (as you stated) and you are pasting as values (that you haven't stated) bar...

The code that I posted is triggered by you double clicking in the first cell in the range that you need copying whereas in your code they are hard coded.

My question is what do you want to do differently to either code?

If you just want to look at the results between each cell being copied loop through the cells and put a message box at the end of each loop, which will pause the macro

 

[Skyybot]

Try this.

Sub CopyRows()
Dim sourceSheet As Worksheet
Dim destSheet As Worksheet
Dim lastRow As Long
Dim startCell As Range
Dim endCell As Range
Dim destCell As Range
Static n As Double, m As Double
Set sourceSheet = ThisWorkbook.Sheets("Sheet1")
Set destSheet = ThisWorkbook.Sheets("Sheet2")
If m < 7 Then m = 7



' Find the last row in the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row

' Set the range to copy
Set startCell = sourceSheet.Range("A1").Offset(n, 0)
Set endCell = sourceSheet.Range("A1").Offset(m, 0)

' Set the destination cell
Set destCell = destSheet.Range("A1")

' Copy the range and paste it to the destination cell
sourceSheet.Range(startCell, endCell).Copy destCell
Application.CutCopyMode = False
n = n + 1
m = m + 1
End Sub

 

[Skyybot]

Sorry, Post #5 only incremented by 1. This should work.

Sub CopyRows()
Dim sourceSheet As Worksheet
Dim destSheet As Worksheet
Dim lastRow As Long
Dim startCell As Range
Dim endCell As Range
Dim destCell As Range
Static n As Double, m As Double
Set sourceSheet = ThisWorkbook.Sheets("Sheet1")
Set destSheet = ThisWorkbook.Sheets("Sheet2")
If n = 0 And m = 0 Then
    n = 1
    m = 7
End If

' Find the last row in the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row

' Set the range to copy
Set startCell = sourceSheet.Range("A1").Offset(n, 0)
Set endCell = sourceSheet.Range("A1").Offset(m, 0)

' Set the destination cell
Set destCell = destSheet.Range("A1")

' Copy the range and paste it to the destination cell
sourceSheet.Range(startCell, endCell).Copy destCell
Application.CutCopyMode = False
n = n + 7: m = m + 7
End Sub

 

[nh3]

Thanks.

How to Rotate a Spin Button Control horizontally??Its in vertical mode.​