Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need HELP!
I've a excel spreadsheet which is used as a form to fill out informations. I
need the data from that form to be copied in another worksheet in a simple format with no spaces and no blank rows and columns as a normal list format. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need HELP!
Hi Liz,
Here is some code I'm using on another project. It may get you going. Change the input cells in the code to match your input cells on the sheet and give it a run. Change AAA & BBB to your sheet names. It takes those cell values in sheet AAA and puts them in sheet BBB starting in column A and running over to column J. If you want the data in sheet BBB to be vertical, the code will need to be modified. I tried to do that but since I did not write the code but understand most of it, I was unable to do that. Any MVP or real expert can make that change. Sub UpdateLogWorksheet() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myCopy As String Dim myCell As Range 'cells to copy from Input sheet myCopy = "B1,C2,D3,E4,F5,G6,H7,I8,J9,K10" Set inputWks = Worksheets("AAA") Set historyWks = Worksheets("BBB") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks With .Cells(nextRow, "A") oCol = 1 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With End With 'clear input cells that contain constants With inputWks On Error Resume Next With .Range(myCopy).Cells.SpecialCells(xlCellTypeConsta nts) .ClearContents Application.GoTo .Cells(1) End With On Error GoTo 0 End With End Sub HTH Regards, Howard "Liz" wrote in message ... I've a excel spreadsheet which is used as a form to fill out informations. I need the data from that form to be copied in another worksheet in a simple format with no spaces and no blank rows and columns as a normal list format. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|