Button Command - Copy and Paste {Excel}

[cristi der]

i have a tabel in sheet1 - columns a,b,c,d and rows 2 to 100 with different data.
i want to put in sheet2 a command button to copy row from sheet1( a2,b2,d2) in sheet2 in the cells ( e1,e2,f3). when i press the button command again copy next row from sheet1(a3,b3,d3) in same cells from sheet2, and so long when i press the command button.
p.s-> help and tanks!

Sub ok_click()
Dim a As Range, b As Range, c As Range
Set a = Sheets("sheet2").Range("e1")
Set b = Sheets("sheet2").Range("e2")
Set c = Sheets("sheet2").Range("f3")
For j = 0 To 1
j = j + 1
    Sheets("sheet1").Cells(j, 5).Copy Destination:=a
    Sheets("sheet1").Cells(j, 2).Copy Destination:=b
    Sheets("sheet1").Cells(j, 3).Copy Destination:=c
 
Next
 
  End Sub
 

[ADezii]

This should point you in the right direction:

Dim rng1 As Excel.Range
Dim rng2 As Excel.Range
 
Set rng1 = Worksheets("Sheet1").Range("A2:D100")
 
For Each rng2 In rng1
  Worksheets("Sheet2").Cells(rng2.Row, rng2.Column + 4) = rng2.Value
Next

[cristi der]

hello, ADezii! i thank you for your response ;), but i have a problem! when i push the command button I would like to copies one row from sheet1(the example: row 2) in sheet2(for example in row 2). when i push the command button again, i want to copies next row from sheet1(the example: row 3) in same place in sheet2 ( row 2 )! i press command button again, i want to copies next row from sheet1( the example: row 4) in same place in sheet2(row 2).
Sorry I don't know so good english!

[ADezii]

My Post was to only point you in the right direction. What you need to do is to:

1. Create a Global or Static Variable to keep tract of the Current Row Number.
2. In the Click() Event of the Command Button on Sheet 2, increment the Value of the Variable representing the Row Number, then copying the data (1 Row at a time) over from Sheet1 keeping the Rows the same but offsetting the Column by 4.
3. Provide a mechanism to RESET the Variable.
4. If you are still having problems, let me know, and I'll post something a little more definitive.

[Mihail]

Hello !
From your name I think you are a Romanian guy, aren't you ?
Of course that no matter. So:

Do you really wish to COPY (values and formats) or you wish only to transfer values ?

One more question:
Why you use a FOR - NEXT cycle and, IN this cycle you increase the value for j ?

Your code:

For j = 0 to 1
     j=j+1
     ......
Next j

This cycle will be executed only one time and the value for j will be, every time, 1.

[cristi der]

Hi!
1)Yes, i'm Romanian guy :D.
2)I wish to transfer values(text)!
3)I used the statement because I thought it goes without writing too many lines of program. it's too very difficult :(

[cristi der]

Thanks for the advice. but I have one question - what I do, can be done or not ?

[Mihail]

Yes. It can be done.

Two more questions:
1) You have a form which contain a button for COPY-PASTE or your button is in a sheet? If it is in a sheet, in which one ?
2) What version of Excel you use?

I understand why you use FOR-NEXT cycle. But I explain you that the statement "j=j+1" INSIDE the cycle is wrong:
When the cycle start, j will be initialized: j=0. Immediately, the statement "j=j+1" asign to j values 1 (j=0+1 => j=1). So, in the next lines j will be 1. When NEXT statement is executed, the cycle variable (j, in your case) will be increased by 1. So j will be... 2 (1+1) grater than the value "1" in "FOR j = 0 to 1". In this case Visual Basic exit from the cycle and execute the statement after NEXT. Hope you understand.

As soon you answer to my questions I'll send you the program to do your job.

Of course, I can explain better in Romanian language but the forum rules... :)

Good luck !

[cristi der]

1) the command button is in a sheet. in sheet2.( i don't use form)
2)excel 2003 - sp2


I understand, but the code does not work if you change the value for example: j = 0 to 5 Step 1.
Thanks a lot!

[ADezii]

I do have a viable solution which I will Post later on. Actually, I'll give you what I have now as an Attachment, and should you have any questions, simply ask.

[Mihail]

Note that I rename the sheets. Excel think now that the sheets are named Sh1 and Sh2. On the other way, you'll see that the sheets are named Foaie1 and Foaie2. There are two different things: The internal names of sheets and what you see as sheets names.
I can't test the code in 2003 version. I save for you in that version. Anyway now you have the idea.
Good luck !

[NeoPa]

To copy the values of the cells from Sheet1.A2,B2,D2 (Notice we are ignoring column C here) across to Sheet2.E1,E2,F3 (Notice these are three separate cells, but a completely different shape from the From cells) and changing this each time it's used to progress from A2,B2,D2 in Sheet1 up to A100,B100,D100 in the same sheet, before wrapping round again to row 2, you can use code similar to :

Public Sub OK_Click()
  Static lngRow As Long
  Dim shtTo As Worksheet
 
  lngRow = IIf((lngRow Mod 100) = 0, 2, lngRow + 1)
  Set shtTo = Worksheets("Sheet2")
  With Worksheets("Sheet1")
    shtTo.Range("E1:E2") = .Range(Replace("A%R:B%R", "%R", lngRow)).Value
    shtTo.Range("F3") = .Range("D" & lngRow).Value
  End With
End Sub