Copy selected range to another worksheet, stop, and go to the next record to select (VBA)

[feni1388]

Hello....

I want to copy selected range to another worksheet, stop (as the user need to check the result), and the user will go the source sheet again to select another record.

I found this code in the internet and piece it together to achieve what I wanted to create and it works well.
The only problem is that before pasting it to another worksheet, I need to clear the cells as the range that I copy can be long or short.
I inserted the first two lines of the code to clear the previous record. But became error when I run it. I think it's because of the selection in the other sheet to copy it?
Any idea on how to do this?
I also attached the source sheet (where I want to copy it from).

Thank you in advance for the help.

Private Sub CommandButton1_Click()
 
   Sheets("Search").Range("D6:G31").Select
   Selection.Clear
 
     
    Dim srcRange As Range
    Dim destSheet As Worksheet
    Dim destRange As Range
   
   
       
    ' Assuming the highlighted range is currently selected
    Set srcRange = Selection
   
    ' Set the destination worksheet and range
    Set destSheet = Worksheets("Search")
    Set destRange = destSheet.Range("D6").Resize(srcRange.Rows.Count, srcRange.Columns.Count)
   
    ' Copy the selected range to the destination
    srcRange.Copy destRange
End Sub



'activex commandbutton will always be at top right
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
        On Error GoTo 0
        With Cells(Windows(1).ScrollRow, Windows(1).ScrollColumn)
            CommandButton1.Top = .Top + 100
            CommandButton1.Left = .Left + 300 - CommandButton1.Width + 1000
        End With

 
End Sub

先日付確認用.xlsm
	A	B	C	D
1
2	AES01032	1	GL72	1
3		2	GL71	2
4		3	BM43	3
5		4	GM73	5
6		5	GH73	10
7		6	BM70	10
8	AES01032	1	GL72	1
9		2	GL71	2
10		3	BM43	3
11		4	GM73	5
12		5	GH73	10
13		6	BM70	10
14		7	BM71	5
15	ASD01061	1	BM43	10
16	ASD01061	1	BM43	10
17	ASS01041	1	BM43	1
18		2	BM73	1
19		3	GL41	1
20		4	GL42	1
21		5	GL43	1
22		6	GL44	1
23		7	GL45	1
24		8	GH73	1
25		9	GH83	1
先日付受注

Cell Formulas
Range	Formula
B2,B8,B15:B17	B2	=IFERROR(IF(C2<>"",1,""),"")
B3:B7,B9:B14,B18:B25	B3	=IF(C3="","",IF(AND(C3<>"",B2=""),1,B2+1))

 

[Alex Blakenburg]

Just replace your first 2 lines with this one:

   Worksheets("Search").Range("D6:G31").Clear

To use paste you only need to identify the top left corner of the paste area (so you don't need the resize)

    Set destRange = destSheet.Range("D6")

If later you decide to copy values only using an assignment (ie destRange.Value = srcRange.Value) then your current code resizing the destination would come in very handy.

 

[feni1388]

Just replace your first 2 lines with this one:

   Worksheets("Search").Range("D6:G31").Clear

To use paste you only need to identify the top left corner of the paste area (so you don't need the resize)

    Set destRange = destSheet.Range("D6")

If later you decide to copy values only using an assignment (ie destRange.Value = srcRange.Value) then your current code resizing the destination would come in very handy.

Thank you..... it works perfectly.
and thank you for the suggestion for resizing the destination. I changed that too.

 

[Alex Blakenburg]

You're welcome. Glad I could help.