Why not have both sheets in one workbook?
Is it one line per form and where do you want the data copied to, an example of where the data goes would help.
Posts by royUK
-
-
Quote
Still not what I need
Try explaining more fully what you want.
-
SH - do not report your own posts, next time I will simply delete them.I strongly suggest you read the Forum Rules befor posting again.
-
I would convert the sheets to pdf before emailing. It shouldn't be to difficult if the data is laid out correctly
-
-
No VBA will do this successfully in my experience. I use Able2Extract or Wondershare PDFelement
-
Welcome to the Forum Whooops. In future please attach your examples to the Forum so they are permanent
-
Pleased to help. Check out my Databaseform. It's free from the link in my signature
-
Then my suggestion above will work.
-
To help solve your problem we need clear examples. Post a small Excel workbook, not an image, showing a realistic sample of your data with confidential information removed.
-
Which version of Excel are you using? The best way would be to convert the source data to a proper Table and then use a PivotTable
-
Why use VBA? The CountIf function is all you need.
-
I can't see where you give ws a value, nor lRow. Try
Display MoreCode- Private Sub btnsubmit_Click()
- 'Copy input values to sheet.
- Dim lRow As Long
- ''/// you do not need to activate the sheet.
- 'Make Sheet1 Active
- ' Sheet1.Activate
- 'Determine empty row
- ' emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
- With Sheet1
- lRow -.Cells(.Rows.Count, 1).End(xlUp).Row + 1
- .Cells(lRow, 1).Value = Me.empid.Value
- .Cells(lRow, 2).Value = Me.disreview.Value
- .Cells(lRow, 3).Value = Me.daterec.Value
- .Cells(lRow, 4).Value = Me.batch.Value
- .Cells(IRow, 5).Value = Me.cmbdate.Value & "/" & Me.cmbmonth.Value & "/" & Me.cmbyear.Value
- End With
- 'Clear input controls.
- Me.empid.Value = ""
- Me.disreview.Value = ""
- Me.daterec.Value = ""
- Me.batch.Value = ""
- Me.dateac.Value = ""
- End Sub
-
Sorry, I wasn't available but I would have suggested Transpose. Well done.
-
Are you copying sheets with red Tabs? If so then try this
Display MoreCode- Option Explicit
- Public Sub CopyREDTABS2()
- Dim WBSource As Workbook
- Dim sht As Worksheet
- Set WBSource = ActiveWorkbook
- For Each sht In WBSource.Sheets
- ' Debug.Print Sheet.Name
- If sht.Tab.Color = vbRed Then sht.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
- Call Copypastevalues
- Next Sheet
- End Sub
-
You have set WBSource and WBDestination as the ActiveWorkBook.
-
-
If it does what you want then let me know and I'll help you set it up.
-
Try this, I think it does what you are aiming for. I've also tidied up the rest of the code.
-
For a start you declare a variable ws but don't give it a value. It should be
Note all variables should be declared at the top of the code.
I think this is what you are trying to do, but the code will need expanding to cover each set of controls
Display MoreCode- Private Sub CommandButton3_Click()
- Dim ws As Worksheet
- If Me.ComboBox1.ListIndex < 0 Then
- MsgBox "you must select a name", vbCritical, "Input required"
- Me.ComboBox1.SetFocus
- Exit Sub
- End If
- Set ws = Worksheets(Me.ComboBox1.Value)
- ws.Range("D16").CurrentRegion.Cells(Me.ComboBox2.ListIndex + 2, Me.ComboBox3.ListIndex + 2) = Me.TextBox1.Value
- End Sub
- Try it out, it only works with the first set of comboboxes and textbox. Let me know if this on the right track