| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying cells into different rows in different worksheets
Is it possible to insert the inputed values of an userform into the last row
of 3 different worksheets (with unique names) where no data has been filled in yet ? I have already tried the underneath but the macro stops when it has to tackle the next sheet. Is there a way to solve this problem ? Sub cmdok_click() Sheets("uuroverzicht").Cells(4, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For a = 4 To 100 If Sheets("uuroverzicht").Cells(a, 1) = ActiveCell Then Sheets("uuroverzicht").Cells(a, 1).Value = txtSDnr.Text Sheets("uuroverzicht").Cells(a, 2).Value = txtvoornaam.Text Sheets("uuroverzicht").Cells(a, 3).Value = txtachternaam.Text Sheets("uuroverzicht").Cells(a, 4).Value = txtgeboortedatum.Text Sheets("uuroverzicht").Cells(a, 5).Value = txtindienst.Text Sheets("uuroverzicht").Cells(a, 7).Value = txtABM.Text Sheets("uuroverzicht").Cells(a, 8).Value = txtMF.Text Sheets("uuroverzicht").Cells(a, 9).Value = cboafdeling.Text End If Next Sheets("globaal uuroverzicht").Cells(496, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For b = 6 To 1000 If Sheets("globaal uuroverzicht").Cells(b, 1) = ActiveCell Then Sheets("globaal uuroverzicht").Cells(b, 1) = txtSDnr Sheets("globaal uuroverzicht").Cells(b, 2) = txtvoornaam Sheets("globaal uuroverzicht").Cells(b, 3) = txtachternaam Sheets("globaal uuroverzicht").Cells(b, 4) = txtgeboortedatum Sheets("globaal uuroverzicht").Cells(b, 5) = txtindienst Sheets("globaal uuroverzicht").Cells(b, 6) = txtABM Sheets("globaal uuroverzicht").Cells(b, 7) = txtMF Sheets("globaal uuroverzicht").Cells(b, 8) = cboafdeling End If Next End Sub Greetings |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copying cells into different rows in different worksheets
You must select the sheet first.
Sub cmdok_click() Sheets("uuroverzicht").Select Sheets("uuroverzicht").Cells(4, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For a = 4 To 100 If Sheets("uuroverzicht").Cells(a, 1) = ActiveCell Then Sheets("uuroverzicht").Cells(a, 1).Value = txtSDnr.Text Sheets("uuroverzicht").Cells(a, 2).Value = txtvoornaam.Text Sheets("uuroverzicht").Cells(a, 3).Value = txtachternaam.Text Sheets("uuroverzicht").Cells(a, 4).Value = txtgeboortedatum.Text Sheets("uuroverzicht").Cells(a, 5).Value = txtindienst.Text Sheets("uuroverzicht").Cells(a, 7).Value = txtABM.Text Sheets("uuroverzicht").Cells(a, 8).Value = txtMF.Text Sheets("uuroverzicht").Cells(a, 9).Value = cboafdeling.Text End If Next Sheets("globall uurovorizich").Select Sheets("globaal uuroverzicht").Cells(496, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For b = 6 To 1000 If Sheets("globaal uuroverzicht").Cells(b, 1) = ActiveCell Then Sheets("globaal uuroverzicht").Cells(b, 1) = txtSDnr Sheets("globaal uuroverzicht").Cells(b, 2) = txtvoornaam Sheets("globaal uuroverzicht").Cells(b, 3) = txtachternaam Sheets("globaal uuroverzicht").Cells(b, 4) = txtgeboortedatum Sheets("globaal uuroverzicht").Cells(b, 5) = txtindienst Sheets("globaal uuroverzicht").Cells(b, 6) = txtABM Sheets("globaal uuroverzicht").Cells(b, 7) = txtMF Sheets("globaal uuroverzicht").Cells(b, 8) = cboafdeling End If Next End Sub You might need to do some checking before you do your end(xldown). If there is no data below the selected cell, you might end up at the bottom of the sheet. I am not sure if this is a concern or not since I don't know what will be on your sheets. -- Regards, Tom Ogilvy "stephanie" wrote in message ... Is it possible to insert the inputed values of an userform into the last row of 3 different worksheets (with unique names) where no data has been filled in yet ? I have already tried the underneath but the macro stops when it has to tackle the next sheet. Is there a way to solve this problem ? Sub cmdok_click() Sheets("uuroverzicht").Cells(4, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For a = 4 To 100 If Sheets("uuroverzicht").Cells(a, 1) = ActiveCell Then Sheets("uuroverzicht").Cells(a, 1).Value = txtSDnr.Text Sheets("uuroverzicht").Cells(a, 2).Value = txtvoornaam.Text Sheets("uuroverzicht").Cells(a, 3).Value = txtachternaam.Text Sheets("uuroverzicht").Cells(a, 4).Value = txtgeboortedatum.Text Sheets("uuroverzicht").Cells(a, 5).Value = txtindienst.Text Sheets("uuroverzicht").Cells(a, 7).Value = txtABM.Text Sheets("uuroverzicht").Cells(a, 8).Value = txtMF.Text Sheets("uuroverzicht").Cells(a, 9).Value = cboafdeling.Text End If Next Sheets("globaal uuroverzicht").Cells(496, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For b = 6 To 1000 If Sheets("globaal uuroverzicht").Cells(b, 1) = ActiveCell Then Sheets("globaal uuroverzicht").Cells(b, 1) = txtSDnr Sheets("globaal uuroverzicht").Cells(b, 2) = txtvoornaam Sheets("globaal uuroverzicht").Cells(b, 3) = txtachternaam Sheets("globaal uuroverzicht").Cells(b, 4) = txtgeboortedatum Sheets("globaal uuroverzicht").Cells(b, 5) = txtindienst Sheets("globaal uuroverzicht").Cells(b, 6) = txtABM Sheets("globaal uuroverzicht").Cells(b, 7) = txtMF Sheets("globaal uuroverzicht").Cells(b, 8) = cboafdeling End If Next End Sub Greetings |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copying cells into different rows in different worksheets
Thank you very much. Indeed this was the missing link. Thanks also for the
remark but underneath the selected cell are a lot of data listed. "Tom Ogilvy" wrote: You must select the sheet first. Sub cmdok_click() Sheets("uuroverzicht").Select Sheets("uuroverzicht").Cells(4, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For a = 4 To 100 If Sheets("uuroverzicht").Cells(a, 1) = ActiveCell Then Sheets("uuroverzicht").Cells(a, 1).Value = txtSDnr.Text Sheets("uuroverzicht").Cells(a, 2).Value = txtvoornaam.Text Sheets("uuroverzicht").Cells(a, 3).Value = txtachternaam.Text Sheets("uuroverzicht").Cells(a, 4).Value = txtgeboortedatum.Text Sheets("uuroverzicht").Cells(a, 5).Value = txtindienst.Text Sheets("uuroverzicht").Cells(a, 7).Value = txtABM.Text Sheets("uuroverzicht").Cells(a, 8).Value = txtMF.Text Sheets("uuroverzicht").Cells(a, 9).Value = cboafdeling.Text End If Next Sheets("globall uurovorizich").Select Sheets("globaal uuroverzicht").Cells(496, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For b = 6 To 1000 If Sheets("globaal uuroverzicht").Cells(b, 1) = ActiveCell Then Sheets("globaal uuroverzicht").Cells(b, 1) = txtSDnr Sheets("globaal uuroverzicht").Cells(b, 2) = txtvoornaam Sheets("globaal uuroverzicht").Cells(b, 3) = txtachternaam Sheets("globaal uuroverzicht").Cells(b, 4) = txtgeboortedatum Sheets("globaal uuroverzicht").Cells(b, 5) = txtindienst Sheets("globaal uuroverzicht").Cells(b, 6) = txtABM Sheets("globaal uuroverzicht").Cells(b, 7) = txtMF Sheets("globaal uuroverzicht").Cells(b, 8) = cboafdeling End If Next End Sub You might need to do some checking before you do your end(xldown). If there is no data below the selected cell, you might end up at the bottom of the sheet. I am not sure if this is a concern or not since I don't know what will be on your sheets. -- Regards, Tom Ogilvy "stephanie" wrote in message ... Is it possible to insert the inputed values of an userform into the last row of 3 different worksheets (with unique names) where no data has been filled in yet ? I have already tried the underneath but the macro stops when it has to tackle the next sheet. Is there a way to solve this problem ? Sub cmdok_click() Sheets("uuroverzicht").Cells(4, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For a = 4 To 100 If Sheets("uuroverzicht").Cells(a, 1) = ActiveCell Then Sheets("uuroverzicht").Cells(a, 1).Value = txtSDnr.Text Sheets("uuroverzicht").Cells(a, 2).Value = txtvoornaam.Text Sheets("uuroverzicht").Cells(a, 3).Value = txtachternaam.Text Sheets("uuroverzicht").Cells(a, 4).Value = txtgeboortedatum.Text Sheets("uuroverzicht").Cells(a, 5).Value = txtindienst.Text Sheets("uuroverzicht").Cells(a, 7).Value = txtABM.Text Sheets("uuroverzicht").Cells(a, 8).Value = txtMF.Text Sheets("uuroverzicht").Cells(a, 9).Value = cboafdeling.Text End If Next Sheets("globaal uuroverzicht").Cells(496, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For b = 6 To 1000 If Sheets("globaal uuroverzicht").Cells(b, 1) = ActiveCell Then Sheets("globaal uuroverzicht").Cells(b, 1) = txtSDnr Sheets("globaal uuroverzicht").Cells(b, 2) = txtvoornaam Sheets("globaal uuroverzicht").Cells(b, 3) = txtachternaam Sheets("globaal uuroverzicht").Cells(b, 4) = txtgeboortedatum Sheets("globaal uuroverzicht").Cells(b, 5) = txtindienst Sheets("globaal uuroverzicht").Cells(b, 6) = txtABM Sheets("globaal uuroverzicht").Cells(b, 7) = txtMF Sheets("globaal uuroverzicht").Cells(b, 8) = cboafdeling End If Next End Sub Greetings |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro for copying specific rows from various worksheets to summary | Excel Discussion (Misc queries) | |||
| Copying worksheets with hyperlinks to named cells | New Users to Excel | |||
| Copying multiple rows to other worksheets (but amount of rows varies) - How? | Excel Discussion (Misc queries) | |||
| zero value when copying cells between worksheets | Excel Discussion (Misc queries) | |||
| copying cells from other worksheets | Excel Discussion (Misc queries) | |||
Linear Mode
