Form Summarys

  • Thread starter Thread starter cassy01
  • Start date Start date
C

cassy01

I want to know if i can write up my form on excel and then on anothe
sheet it summarizes it so i have a log of all the forms i send out bu
each time i create a new form can it create a new summary underneat
the old one ??

Many Thanks:D
Ben
 
Does this mean that you have certain cells that you want to track?

If yes, then you could run a macro when you're done with the form:

Option Explicit
Option Base 0
Sub testme01()

Dim myCellAddresses As Variant
Dim FormWks As Worksheet
Dim LogWks As Worksheet
Dim nextRow As Long
Dim iCtr As Long

Set FormWks = Worksheets("formsheet")
Set LogWks = Worksheets("logsheet")

myCellAddresses = Array("a1", "b9", "c22", "e14")

With LogWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
For iCtr = LBound(myCellAddresses) To UBound(myCellAddresses)
.Cells(nextRow, iCtr + 1).Value _
= FormWks.Range(myCellAddresses(iCtr))
Next iCtr
End With

End Sub

I used two worksheets (fromSheet and LogSheet). I wanted the values in
a1,b9,c22,e14 to be kept in the log sheet in the next row (columns A:D).

(adjust names and addresses to match your form.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
This Doesnt Make Sence Im affraid, so if you want i can send it directly
to you ? just send an email to (e-mail address removed)

But if you cant can you try and explain it to me more simply. Thanks

My sheet1 is called Form
sheet2 is called Summary

i want cells : C11, B9, F9, B17, F17, C19, F13 in that order on sheet 1
to be put on sheet2 one cell in each column A,B,C,D,E,F,G

Many Thanks:D
Benn
 
Is it the macro itself that makes no sense or the stuff that the macro does?

You can try this version. Notice the changes in the worksheet name and the
addresses that you want copied. (That's all I did.) (And I just changed the
name of the macro to something more meaningful.)

Option Explicit
Option Base 0
Sub SaveMyValues()

Dim myCellAddresses As Variant
Dim FormWks As Worksheet
Dim LogWks As Worksheet
Dim nextRow As Long
Dim iCtr As Long

Set FormWks = Worksheets("form")
Set LogWks = Worksheets("Summary")

myCellAddresses = Array("C11", "B9", "F9", "B17", "F17", "C19", "F13")

With LogWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
For iCtr = LBound(myCellAddresses) To UBound(myCellAddresses)
.Cells(nextRow, iCtr + 1).Value _
= FormWks.Range(myCellAddresses(iCtr))
Next iCtr
End With

End Sub

Again, you may want to read David McRitchie's notes if macros are new to you.


Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
click on Tools|macro|macros...
click on the macro name (savemyvalues)
and then click run.

Don't forget to save the workbook--so you don't have to do the setup again!
 
thanks the second bit worked better for me the one where you just paste
it but i have one problem:

when i click on run macro it replaces the summary page so like when
click on run macro it places all my form into the other page in th
column then i save it, then i write another form then click run macr
and it replaces it !!! i dont want it to be replaced i wan the nex
form summary to go underneath the old one so i keep a summary or th
forms i create.

Many Thanks:D
Ben
 
Actually, it uses column A to get the nextrow with this line:

nextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

That +1 drops it down one row. But if C11 is sometimes blank (and it's mapped
to column A), it could overwrite existing data.

Is there any column that always has data in it--the user can't leave that cell
blank?

If yes, then change that "A" to that column in the formula above.

If no, then maybe this'll work. It uses the same technique to find the last row
as hitting ctrl-End does if you did it manually:

Option Explicit
Option Base 0
Sub SaveMyValues()

Dim myCellAddresses As Variant
Dim FormWks As Worksheet
Dim LogWks As Worksheet
Dim nextRow As Long
Dim iCtr As Long
Dim dummyRng As Range

Set FormWks = Worksheets("form")
Set LogWks = Worksheets("Summary")

myCellAddresses = Array("C11", "B9", "F9", "B17", "F17", "C19", "F13")

With LogWks
Set dummyRng = .UsedRange 'try to reset lastcell
nextRow = .Cells.SpecialCells(xlCellTypeLastCell).Row + 1
For iCtr = LBound(myCellAddresses) To UBound(myCellAddresses)
.Cells(nextRow, iCtr + 1).Value _
= FormWks.Range(myCellAddresses(iCtr))
Next iCtr
End With

End Sub
 
ok right, all the cells are always filled in so i will leave it so that
it can go in Column A but i tried to copy the above formula but it
wouldn't let me ?? do i have to change anything ??

Many Thanks
Benn
 
ignore my last post i have fixed the problem, but, i have one othe
little problem i am going to insert some new formulas in columns G:
so therefore the formulas above wont work properly so, i would like :

C11 of the "form" sheet to go in column A in the "summary" sheet.
B9 into column B
F9 into column C
B17 into column D
F17 into column E
C19 into column G
F13 into column O

if that is possible ??

Many Thanks :D
Ben
 
I'm not sure which way you determined the last row. I modified the "ctrl-End"
version.


Option Explicit
Option Base 0
Sub SaveMyValues()

Dim myCellAddresses As Variant
Dim myColumnLetters As Variant

Dim FormWks As Worksheet
Dim LogWks As Worksheet
Dim nextRow As Long
Dim iCtr As Long
Dim dummyRng As Range

Set FormWks = Worksheets("form")
Set LogWks = Worksheets("Summary")

myCellAddresses = Array("C11", "B9", "F9", "B17", "F17", "C19", "F13")
myColumnLetters = Array("a", "b", "C", "d", "e", "G", "o")

If UBound(myCellAddresses) <> UBound(myColumnLetters) Then
MsgBox "design error! & vblf _
"Make the number of cells match the number of colums!"
Exit Sub
End If

With LogWks
Set dummyRng = .UsedRange 'try to reset lastcell
nextRow = .Cells.SpecialCells(xlCellTypeLastCell).Row + 1
For iCtr = LBound(myCellAddresses) To UBound(myCellAddresses)
.Cells(nextRow, myColumnLetters(iCtr)).Value _
= FormWks.Range(myCellAddresses(iCtr))
Next iCtr
End With

End Sub
 
i have altered the cells so that they are all together but everytime i
click Run Macro it goes underneath all my formulas but i want it to go
along side the other formulas is this possible ??

Many Thanks
Benn
 
no it works but now i have entered another formula :

=IF($G4="C1",1,IF($G4="C2",2,IF($G4="C3",3,IF($G4="C4",4,IF($G4="C5",5,IF($G4="C6",6,IF($G4="C7",7,0)))))))

But when i click run macro it goes to the next empty row but i want i
to go on the same row as the above formula

Many Thanks
Ben
 
Where is the formula?

And you always want it to go on the same row as the formula? Won't that mean
that you only get to keep track of one set of values?
 
Back
Top