Tfr data from one sheet to another

[Mikeice]

Hi There

I am haveing trouble moving data from one sheet called "Survey" to
worksheet called Summary.

I wanted the "Survey" sheet to be filled out and at the click of a
button trnasfer the data from Survey worksheet to summary and blank
"Survey.
Next time the information is entered I want the data posted to the next
available column in the summary sheet.

This is what I have so far but it is not working.

PLease help.


Private Sub CommandButton1_Click()

Dim Survey As Worksheet
Dim Summary As Worksheet


Set Survey = Worksheets("Survey")
Set Summary = Worksheets("Summary")

Survey.Range("C3").Value = Summary.Range("B1").Value
Survey.Range("C4").Value = Summary.Range("B3").Value
Survey.Range("C5").Value = Summary.Range("B4").Value
Survey.Range("C6").Value = Summary.Range("B5").Value
Survey.Range("G3").Value = Summary.Range("B2").Value

Survey.Range("D14").Value = Summary.Range("B7").Value
Survey.Range("D15").Value = Summary.Range("B8").Value
Survey.Range("D16").Value = Summary.Range("B9").Value
Survey.Range("D17").Value = Summary.Range("B10").Value
Survey.Range("D18").Value = Summary.Range("B11").Value

Survey.Range("D21").Value = Summary.Range("B13").Value
Survey.Range("D22").Value = Summary.Range("B14").Value
Survey.Range("D23").Value = Summary.Range("B15").Value

Survey.Range("D26").Value = Summary.Range("B17").Value
Survey.Range("D27").Value = Summary.Range("B18").Value
Survey.Range("D28").Value = Summary.Range("B19").Value

Survey.Range("D31").Value = Summary.Range("B21").Value
Survey.Range("D32").Value = Summary.Range("B22").Value

Survey.Range("D35").Value = Summary.Range("B24").Value

Survey.Range("c39").Value = Summary.Range("B26").Value



End Sub

thx in advance you guys are great!

 

[Dave Peterson]

Your basic code looks backwards. Didn't you want to populate the Summary sheet
with the values from the Survey sheet?

And is this commandbutton placed on the Survey sheet?

And can we use one row to determine the next available column (I used the first
row (1).)

If yes to all three...

Option Explicit
Private Sub CommandButton1_Click()

Dim Summary As Worksheet
Dim myFromAddr As Variant
Dim myToRow As Variant
Dim iCtr As Long
Dim LastCol As Range
Dim NextColNum As Long

myToRow = Array(1, 3, 4, 5, 2, _
7, 8, 9, 10, 11, _
13, 14, 15, 17, 18, _
19, 21, 22, 24, 26)

myFromAddr = Array("C3", "C4", "C5", "C6", "G3", _
"D14", "D15", "D16", "D17", "D18", _
"D21", "D22", "D23", "D26", "D27", _
"D28", "D31", "D32", "D35", "c39")

If UBound(myToRow) <> UBound(myFromAddr) Then
MsgBox "Design error--not same number of cells!"
Exit Sub
End If

If IsEmpty(Me.Range(myFromAddr(LBound(myFromAddr)))) Then
MsgBox "Please fill in cell: " & myFromAddr(LBound(myFromAddr))
Exit Sub
End If

Set Summary = Worksheets("Summary")

With Summary
Set LastCol _
= .Cells(myToRow(LBound(myToRow)), .Columns.Count).End(xlToLeft)
If IsEmpty(LastCol) Then
NextColNum = LastCol.Column
Else
NextColNum = LastCol.Column + 1
End If

For iCtr = LBound(myToRow) To UBound(myToRow)
.Cells(myToRow(iCtr), NextColNum).Value _
= Me.Range(myFromAddr(iCtr)).Value
Me.Range(myFromAddr(iCtr)).ClearContents
Next iCtr
End With
End Sub

 

[anilsolipuram]

Try this macro

Sub CommandButton1_Click()

Dim Survey As Worksheet
Dim Summary As Worksheet

Dim r As Range
Set Survey = Worksheets("Survey")
Set Summary = Worksheets("Summary")
Set r = Survey.UsedRange
t = Split(r.Address, ":")
Dim col As Integer
If UBound(t) > 0 Then
col = Range(t(1)).Offset(0, 1).Column

Survey.Cells(3, col).Value = Summary.Range("B1").Value
Survey.Cells(4, col).Value = Summary.Range("B3").Value
Survey.Cells(5, col).Value = Summary.Range("B4").Value
Survey.Cells(6, col).Value = Summary.Range("B5").Value
Survey.Cells(7, col).Value = Summary.Range("B2").Value
col = col + 1
Survey.Cells(14, col).Value = Summary.Range("B7").Value
Survey.Cells(15, col).Value = Summary.Range("B8").Value
Survey.Cells(16, col).Value = Summary.Range("B9").Value
Survey.Cells(17, col).Value = Summary.Range("B10").Value
Survey.Cells(18, col).Value = Summary.Range("B11").Value

Survey.Cells(21, col).Value = Summary.Range("B13").Value
Survey.Cells(22, col).Value = Summary.Range("B14").Value
Survey.Cells(23, col).Value = Summary.Range("B15").Value

Survey.Cells(26, col).Value = Summary.Range("B17").Value
Survey.Cells(27, col).Value = Summary.Range("B18").Value
Survey.Cells(28, col).Value = Summary.Range("B19").Value

Survey.Cells(31, col).Value = Summary.Range("B21").Value
Survey.Cells(32, col).Value = Summary.Range("B22").Value

Survey.Cells(35, col).Value = Summary.Range("B24").Value

Survey.Cells(39, col - 1).Value = Summary.Range("B26").Value

End If

End Sub

 

[Mikeice]

Hi There Dave

I tried your code and have two little probs.

1. It only copies 4 cells to the summary sheet.

2. Getting debug error at - = Me.Range(myFromAddr(iCtr)).Value


thanks for your help so far but really need your help.

I will send spreadsheet if that would be easier.

I am still learning excel and do struggle at the VBA level.

th

 

[Dave Peterson]

It worked ok for me when I tested it.

Did you change anything?

That line "Me.Range(myFromAddr(iCtr)).Value" is really part of a larger line.

.Cells(myToRow(iCtr), NextColNum).Value _
= Me.Range(myFromAddr(iCtr)).Value

If you changed the code, you may want to post back with your current code--not
the workbook, just the code.

 

[Mikeice]

Thx Dave all fine
I had inadvertently overlooked a . period.
thank you so much for all your hel