I'm trying to copy the row in a table when a cell in a specified column has data inserted then paste this row into another sheet.
当指定列中的单元格插入数据并将这一行粘贴到另一个表中时，我尝试复制表中的行。
The table starts at cell A3 being the first header to the table and it is 9 columns long, there will be an endless amount of rows.
表格从单元格A3开始，它是第一个表格的头，它有9列长，有无数行。
The column to monitor for change is column 8, named "Date Complete". The information entered should always be a date, format "dd mmm".
监视更改的列是第8列，名为“Date Complete”。输入的信息应该始终是一个日期，格式“dd mmm”。
The row needs to be copied onto a sheet with the same name as the date entered into column 8 which may not exist before the date is entered.
需要将行复制到与第8列中输入的日期同名的表上，在输入日期之前，该日期可能不存在。
Also before the copying is done I would like a text box to enter notes into the corresponding cell in column 9, named "Notes".
在复制完成之前，我还希望有一个文本框将notes输入到列9中相应的单元格中，名为“notes”。
Private Sub Worksheet_change(ByVal Target As Range)
Const lngdatecomplete As Long = 8
Dim wks As Worksheet
Dim lngNextAvailableRow As Long
If Target.Areas.Count = 1 And Target.Cells.Count = 1 Then
If Not Intersect(Target, Columns(lngdatecomplete)) Is Nothing Then
On Error Resume Next
Set wks = ThisWorkbook.Worksheets(Target.Value)
On Error GoTo 0
If wks Is Nothing Then
lngNextAvailableRow = wks.Range("a1").CurrentRegion.Rows.Count + 1
ActiveSheet.Range(Cells(Target.Row, 2), Cells(Target.Row, 8)).copy _
wks.Range("A" & lngNextAvailableRow).PasteSpecial
ElseIf Not wks Is Nothing Then
Dim ShtName$
Sheets.Add after:=Sheets(Sheets.Count)
ShtName = Format(Date, "dd mmm")
Sheets(Sheets.Count).Name = ShtName
Sheets(ShtName).Visible = True
lngNextAvailableRow = wks.Range("a1").CurrentRegion.Rows.Count + 1
ActiveSheet.Range(Cells(Target.Row, 2), Cells(Target.Row, 8)).copy _
wks.Range("A" & lngNextAvailableRow).PasteSpecial
End If
End If
End If
End Sub
1 个解决方案
#1
1
The following seems pretty robust and will accept multiple values pasted into column H. I would advise setting a breakpoint on the Application.EnableEvents = False code line and typing a date into column H. Once you arrive at the breakpoint, you can step through each line with the F8 key.
下面看起来相当健壮，并将接受粘贴到列h中的多个值，我建议在应用程序上设置断点。EnableEvents = False代码行，并将日期输入到列h中，一旦到达断点，就可以使用F8键遍历每一行。
Private Sub Worksheet_change(ByVal Target As Range)
Const lDATECMPLT As Long = 8
If Not Intersect(Target, Columns(lDATECMPLT)) Is Nothing Then
On Error GoTo bm_Safe_Exit
'Application.ScreenUpdating = False
Application.EnableEvents = False
Dim trgt As Range
For Each trgt In Intersect(Target, Columns(lDATECMPLT))
If trgt.Row > 3 And IsDate(trgt) Then
trgt.NumberFormat = "dd mmm"
On Error GoTo bm_Need_WS
With Worksheets(trgt.Text)
On Error GoTo bm_Safe_Exit
trgt.Resize(1, 7).Offset(0, -6).Copy _
Destination:=.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
'optional mark the row copied
'With trgt.Resize(1, 7).Offset(0, -6).Font
' .Strikethrough = True
' .Color = RGB(120, 120, 120)
'End With
End With
End If
Next trgt
End If
GoTo bm_Safe_Exit
bm_Need_WS:
On Error GoTo 0
With Worksheets.Add(after:=Sheets(Sheets.Count))
.Name = trgt.Text
.Visible = True
.Cells(1, 1).Resize(1, 7) = Me.Cells(3, 2).Resize(1, 7).Value2
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
.FreezePanes = True
.Zoom = 80
End With
End With
Resume
bm_Safe_Exit:
Application.EnableEvents = True
Me.Activate
Application.ScreenUpdating = True
End Sub
I left some extras like copying the headers from the original worksheet into the new worksheet, freezing row 1 on the new worksheet, zooming the new worksheet, etc. Delete or adjust these these if you do not find them helpful.
我留下了一些额外的功能，比如将原始工作表中的头文件复制到新的工作表中，在新工作表上冻结第一行，放大新的工作表等等。如果你觉得它们没有帮助，删除或调整这些文件。
When you have made all adjustments to the code, uncomment the 'Application.ScreenUpdating = False code line to avoid screen flashes.
当您对代码做了所有的调整后，取消对“应用程序”的注释。屏幕更新=错误代码行以避免屏幕闪烁。
#1
1
The following seems pretty robust and will accept multiple values pasted into column H. I would advise setting a breakpoint on the Application.EnableEvents = False code line and typing a date into column H. Once you arrive at the breakpoint, you can step through each line with the F8 key.
下面看起来相当健壮，并将接受粘贴到列h中的多个值，我建议在应用程序上设置断点。EnableEvents = False代码行，并将日期输入到列h中，一旦到达断点，就可以使用F8键遍历每一行。
Private Sub Worksheet_change(ByVal Target As Range)
Const lDATECMPLT As Long = 8
If Not Intersect(Target, Columns(lDATECMPLT)) Is Nothing Then
On Error GoTo bm_Safe_Exit
'Application.ScreenUpdating = False
Application.EnableEvents = False
Dim trgt As Range
For Each trgt In Intersect(Target, Columns(lDATECMPLT))
If trgt.Row > 3 And IsDate(trgt) Then
trgt.NumberFormat = "dd mmm"
On Error GoTo bm_Need_WS
With Worksheets(trgt.Text)
On Error GoTo bm_Safe_Exit
trgt.Resize(1, 7).Offset(0, -6).Copy _
Destination:=.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
'optional mark the row copied
'With trgt.Resize(1, 7).Offset(0, -6).Font
' .Strikethrough = True
' .Color = RGB(120, 120, 120)
'End With
End With
End If
Next trgt
End If
GoTo bm_Safe_Exit
bm_Need_WS:
On Error GoTo 0
With Worksheets.Add(after:=Sheets(Sheets.Count))
.Name = trgt.Text
.Visible = True
.Cells(1, 1).Resize(1, 7) = Me.Cells(3, 2).Resize(1, 7).Value2
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
.FreezePanes = True
.Zoom = 80
End With
End With
Resume
bm_Safe_Exit:
Application.EnableEvents = True
Me.Activate
Application.ScreenUpdating = True
End Sub
I left some extras like copying the headers from the original worksheet into the new worksheet, freezing row 1 on the new worksheet, zooming the new worksheet, etc. Delete or adjust these these if you do not find them helpful.
我留下了一些额外的功能，比如将原始工作表中的头文件复制到新的工作表中，在新工作表上冻结第一行，放大新的工作表等等。如果你觉得它们没有帮助，删除或调整这些文件。
When you have made all adjustments to the code, uncomment the 'Application.ScreenUpdating = False code line to avoid screen flashes.
当您对代码做了所有的调整后，取消对“应用程序”的注释。屏幕更新=错误代码行以避免屏幕闪烁。