Can this be done? 1

[aurbo]

Greetings

I require the following miracle to happen via a button on a worksheet or clicking a specified cell;

Step 1. click on cell or button located in worksheet1 cell F3

Step 2. copy worksheet1 cell f1:f2 to worksheet2 cell a1:a2

Step 3. confirm worksheet1 cell a4 is not empty
if empty, stop and display worksheet2
if not empty
copy worksheet1 cells a4:c4 to worksheet2 cells a4:c4

repeat Step 3 for each row in the A column of worksheet1 until an empty cell is encountered.

 

[rmikesmith]

aurbo,

No miracle necessary. I would use a command button as it will be more obvious than clicking on a cell. Add the command button (from the Control Toolbox), change the caption to your liking, then right-click and select View Code. In the CommandButton1_Click procedure insert the name of the procedure below (or a name of your choosing), which you put into a standard code module. Here is the code:

Sub CopyStuff()
Dim LastUsedRow As Long
Dim i As Long
Dim wks1 As Worksheet
Dim wks2 As Worksheet

  Set wks1 = ThisWorkbook.Worksheets("Sheet1")
  Set wks2 = ThisWorkbook.Worksheets("Sheet2")
  
  wks2.Cells(1, 1).Value = wks1.Cells(1, 6)
  wks2.Cells(2, 1).Value = wks1.Cells(2, 6)
  
  LastUsedRow = wks1.Cells(65536, 1).End(xlUp).Row
  For i = 4 To LastUsedRow
    With wks1
      wks2.Cells(i, 1).Value = .Cells(i, 1).Value
      wks2.Cells(i, 2).Value = .Cells(i, 2).Value
      wks2.Cells(i, 3).Value = .Cells(i, 3).Value
    End With
  Next i
  wks2.Activate

  Set wks1 = Nothing
  Set wks2 = Nothing
End Sub

If you would like to see a demo workbook, post your email address.

Regards,
M. Smith

 

[aurbo]

Thanks M. Smith

Yes I would like to see a demo workbook.

My email is Aurbo@shaw.ca