Activate?

[bgkgmg]

I would like to copy cells (after filling in) A4, B5, C6 and B3 into cells
F1, F2, F3 and F4 at the same time using a command button named Submit.
After copied I want original cells A4,B5,C6,B3 empty again. Please help.
Thanks

 

[Susan]

this works but is VERY clumsy....... there's got to be a better way,
probably using an array, but i don't know how to do it.
=======================
Option Explicit

Sub move()

Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim r4 As Range

Set r1 = ActiveSheet.Range("a4")
Set r2 = ActiveSheet.Range("b5")
Set r3 = ActiveSheet.Range("c6")
Set r4 = ActiveSheet.Range("b3")

ActiveSheet.Range("f1").Value = r1.Value
ActiveSheet.Range("f2").Value = r2.Value
ActiveSheet.Range("f3").Value = r3.Value
ActiveSheet.Range("f4").Value = r4.Value

r1.ClearContents
r2.ClearContents
r3.ClearContents
r4.ClearContents

End Sub
==================
hope somebody else posts with something better!

susan

 

[Kevin B]

You could use this sub

Sub PickAndDrop()

Dim ws As Worksheet
Dim strSource(3) As String
Dim strTarget(3) As String
Dim i As Integer

Set ws = ThisWorkbook.ActiveSheet

strSource(0) = "A4"
strSource(1) = "B5"
strSource(2) = "C6"
strSource(3) = "B3"

strTarget(0) = "F1"
strTarget(1) = "F2"
strTarget(2) = "F3"
strTarget(3) = "F4"

For i = 0 To 3
ws.Range(strTarget(i)).Value = ws.Range(strSource(i)).Value
ws.Range(strSource(i)).Clear
Next i

Set ws = Nothing

End Sub

 

[Alan]

All you really need is this (if the Submit button is located on the same
sheet):

Private Sub Submit_Click()
Range("F1").Value = Range("A4").Value
Range("F2").Value = Range("B5").Value
Range("F3").Value = Range("C6").Value
Range("F4").Value = Range("B3").Value
Range("A4").Value = ""
Range("B5").Value = ""
Range("C6").Value = ""
Range("B3").Value = ""
End Sub


Alan

 

[Rick Rothstein \(MVP - VB\)]

Try this...

Dim X As Long
With Worksheets("Sheet3")
For X = 0 To 3
.Range(Array("F1", "F2", "F3", "F4")(X)).Value = _
.Range(Array("A4", "B5", "C6", "B3")(X)).Value
Next
.Range("A4,B5,C6,B3").Clear
End With

Rick

 

[bgkgmg]

In the 3 posts that worked they all were erased when I clicked the Submit
button again. I want the info to remain in cells F1-F4
Thanks

 

[HammerJoe]

Try this:

Private Sub Submit_Click()
If Not IsEmpty(ThisWorkbook.Sheets(Range("A4").Value) then
Range("F1").Value = Range("A4").Value
If Not IsEmpty(ThisWorkbook.Sheets(Range("B5").Value) then
Range("F2").Value = Range("B5").Value
If Not IsEmpty(ThisWorkbook.Sheets(Range("C6").Value) then
Range("F3").Value = Range("C6").Value
If Not IsEmpty(ThisWorkbook.Sheets(Range("B3").Value) then
Range("F4").Value = Range("B3").Value
Range("A4").Value = ""
Range("B5").Value = ""
Range("C6").Value = ""
Range("B3").Value = ""
End Sub