复制并将单元格向下移动。

huangapple go评论70阅读模式
英文:

Pasting while shifting cells down

问题

以下是代码的翻译部分:

Sub filter_copy_paste()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim whatToFind As String
Dim foundTwo As Range
Dim newSelectionRange As Range
Dim rowSelectionRange As Range
Dim Found_Row As Long
Dim num As Integer

Sheets("Sheet1").Select

whatToFind = "Mean"
    
Set foundTwo = Cells.Find(What:=whatToFind, After:=ActiveCell, LookIn:=xlFormulas2, _
  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  MatchCase:=False, SearchFormat:=False)
'
Found_Row = foundTwo.Row

With Sheets("Main").Range("A12:S12").CurrentRegion
    .AutoFilter Field:=19, Criteria1:="Yes"
    .SpecialCells(xlCellTypeVisible).EntireRow.Copy
        
    '
    ' I added the following line to insert selection and shift down in Cells above mean
    '
    Set rowSelectionRange = Rows(Found_Row - 1).Resize(1)
    rowSelectionRange.Select
    Selection.Insert Shift:=xlDown
End With
    
'
'Following is added to clean up my previous worksheet
'
Sheets("Main").Select
If ActiveSheet.FilterMode = True Then
    ActiveSheet.ShowAllData
End If
        
Sheets("Main").Select
Rows("3:11").Select
Range("A11").Activate
Selection.EntireRow.Hidden = True
Application.CutCopyMode = False
    
Sheets("Sheet1").Select
    
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

希望这对您有所帮助。如果您有任何其他问题,请随时提出。

英文:

I am trying to copy info from a workbook into another workbook and paste while shifting cells down.
The code does everything it is supposed to except for pasting the rows: it inserts a new blank row instead of inserting the copied rows.

Sub filter_copy_paste()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim whatToFind As String
Dim foundTwo As Range
Dim newSelectionRange As Range
Dim rowSelectionRange As Range
Dim Found_Row As Long
Dim num As Integer

Sheets("Sheet1").Select

whatToFind = "Mean"
    
Set foundTwo = Cells.Find(What:=whatToFind, After:=ActiveCell, LookIn:=xlFormulas2, _
  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  MatchCase:=False, SearchFormat:=False)
'
Found_Row = foundTwo.row

With Sheets("Main").Range("A12:S12").CurrentRegion
    .AutoFilter Field:=19, Criteria1:="Yes"
    .SpecialCells(xlCellTypeVisible).EntireRow.Copy
        
    '_
    ' Destination:=Sheets("Sheet1").Range("A1")
    '
    ' I added the following line to insert selection and shift down in Cells above mean
    '
    Set rowSelectionRange = Rows(Found_Row - 1).Resize(1)
    rowSelectionRange.Select
    Selection.Insert Shift:=xlDown
End With
    
'
'Following is added to clean up my previous worksheet
'
Sheets("Main").Select
If ActiveSheet.FilterMode = True Then
    ActiveSheet.ShowAllData
End If
        
Sheets("Main").Select
Rows("3:11").Select
Range("A11").Activate
Selection.EntireRow.Hidden = True
Application.CutCopyMode = False
    
Sheets("Sheet1").Select
    
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

I expect the copied rows to be inserted in the range above Mean.

答案1

得分: 3

以下是代码的中文翻译部分:

这应该满足您的需求:
Sub filter_copy_paste()

    Const FIND_THIS As String = "mean" '用于固定值的常量
    
    Dim f As Range, numRows As Long, wsSrc As Worksheet, wsDest As Worksheet
   
    Set wsSrc = ThisWorkbook.Worksheets("Main")    '源表格
    Set wsDest = ThisWorkbook.Worksheets("Sheet2") '复制到这里
    
    Set f = wsDest.Cells.Find(What:=FIND_THIS, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
    If f Is Nothing Then
        MsgBox "'" & FIND_THIS & "' 未在 " & wsDest.Name & " 上找到", vbExclamation
        Exit Sub
    End If

    With wsSrc.Range("A12:S12").CurrentRegion
        Debug.Print "数据", .Address()
        .AutoFilter Field:=19, Criteria1:="Yes"
        '将要复制多少行?
        numRows = .Columns(1).SpecialCells(xlCellTypeVisible).Count
        f.Resize(numRows).EntireRow.Insert shift:=xlDown '插入行
        '复制可见行
        .SpecialCells(xlCellTypeVisible).Copy wsDest.Cells(f.Row - numRows, "A")
    End With
    
    wsSrc.ShowAllData

End Sub
英文:

This should do what you need:

Sub filter_copy_paste()
    
    Const FIND_THIS As String = "mean" 'use const for fixed values
    
    Dim f As Range, numRows As Long, wsSrc As Worksheet, wsDest As Worksheet
   
    Set wsSrc = ThisWorkbook.Worksheets("Main")    'source table
    Set wsDest = ThisWorkbook.Worksheets("Sheet2") 'copy to here
    
    Set f = wsDest.Cells.Find(What:=FIND_THIS, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
    If f Is Nothing Then
        MsgBox "'" & FIND_THIS & "' not found on " & wsDest.Name, vbExclamation
        Exit Sub
    End If

    With wsSrc.Range("A12:S12").CurrentRegion
        Debug.Print "Data", .Address()
        .AutoFilter Field:=19, Criteria1:="Yes"
        'how many rows will be copied?
        numRows = .Columns(1).SpecialCells(xlCellTypeVisible).Count
        f.Resize(numRows).EntireRow.Insert shift:=xlDown 'insert the rows
        'copy visible rows
        .SpecialCells(xlCellTypeVisible).Copy wsDest.Cells(f.Row - numRows, "A")
    End With
    
    wsSrc.ShowAllData

End Sub

答案2

得分: 1

插入筛选行

<!-- 语言: lang-vb -->

Sub InsertFilteredRows()

    Application.ScreenUpdating = False
    
    Dim wb As Workbook: Set wb = ThisWorkbook &#39; 包含此代码的工作簿
    
    &#39; 源
    
    Dim sws As Worksheet: Set sws = wb.Sheets(&quot;Main&quot;)
    If sws.FilterMode Then sws.ShowAllData
    If sws.AutoFilterMode Then sws.AutoFilterMode = False
    
    Dim srg As Range: Set srg = sws.Range(&quot;A12&quot;).CurrentRegion
    srg.AutoFilter Field:=19, Criteria1:=&quot;Yes&quot;
        Dim svrg As Range: Set svrg = srg.SpecialCells(xlCellTypeVisible)
    sws.AutoFilterMode = False
    
    Dim sarg As Range, srCount As Long
    For Each sarg In svrg.Areas: srCount = srCount + sarg.Rows.Count: Next sarg
    
    &#39;Debug.Print srg.Address, svrg.Address, srCount
    
    &#39; 目标
    
    Dim dws As Worksheet: Set dws = wb.Sheets(&quot;Sheet1&quot;)
    If dws.FilterMode Then dws.ShowAllData
    
    Dim durg As Range: Set durg = dws.UsedRange
    Dim dlCell As Range: Set dlCell = durg.Cells(durg.Cells.CountLarge)
    
    &#39; 从用过的范围的第一个单元格开始按行搜索,
    &#39; 尝试找到包含搜索字符串的第一个单元格。
    &#39; 搜索默认情况下是不区分大小写的('A=a')。
    Dim dfCell As Range: Set dfCell = dws.Cells.Find( _
        What:=&quot;Mean&quot;, After:=dlCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows) &#39; 其余是默认参数
    If dfCell Is Nothing Then Exit Sub &#39; 未找到字符串
    Dim dirg As Range: Set dirg = Intersect(durg, dfCell.EntireRow) _
        .Resize(srCount) &#39; 您的代码另外建议 &#39;.Offset(-1)&#39; !?
    
    &#39;Debug.Print svrg.Address, dfCell.Address, dirg.Address
    
    &#39; 插入并复制。
    
    dirg.Insert Shift:=xlShiftDown
    &#39; 无法确定不看到数据的情况下 &#39;CopyOrigin&#39; 参数。
    
    &#39; 复制。
    svrg.Copy dirg.Columns(1).Offset(-srCount)
    
    &#39; 清理!?
    
    sws.Rows(&quot;3:11&quot;).Hidden = True
    If Not wb Is ActiveWorkbook Then wb.Activate
    dws.Select
    
    Application.ScreenUpdating = True

    &#39; 通知。
    
    MsgBox &quot;插入筛选行。&quot;, vbInformation

End Sub
英文:

Insert Filtered Rows

<!-- language: lang-vb -->

Sub InsertFilteredRows()

    Application.ScreenUpdating = False
    
    Dim wb As Workbook: Set wb = ThisWorkbook &#39; workbook containing this code
    
    &#39; Source
    
    Dim sws As Worksheet: Set sws = wb.Sheets(&quot;Main&quot;)
    If sws.FilterMode Then sws.ShowAllData
    If sws.AutoFilterMode Then sws.AutoFilterMode = False
    
    Dim srg As Range: Set srg = sws.Range(&quot;A12&quot;).CurrentRegion
    srg.AutoFilter Field:=19, Criteria1:=&quot;Yes&quot;
        Dim svrg As Range: Set svrg = srg.SpecialCells(xlCellTypeVisible)
    sws.AutoFilterMode = False
    
    Dim sarg As Range, srCount As Long
    For Each sarg In svrg.Areas: srCount = srCount + sarg.Rows.Count: Next sarg
    
    &#39;Debug.Print srg.Address, svrg.Address, srCount
    
    &#39; Destination
    
    Dim dws As Worksheet: Set dws = wb.Sheets(&quot;Sheet1&quot;)
    If dws.FilterMode Then dws.ShowAllData
    
    Dim durg As Range: Set durg = dws.UsedRange
    Dim dlCell As Range: Set dlCell = durg.Cells(durg.Cells.CountLarge)
    
    &#39; Starting with the first cell of the used range searching by rows,
    &#39; attempt to find the first cell that contains the search string.
    &#39; The search is by default case-insensitive (&#39;A=a&#39;).
    Dim dfCell As Range: Set dfCell = dws.Cells.Find( _
        What:=&quot;Mean&quot;, After:=dlCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows) &#39; the rest are default parameters
    If dfCell Is Nothing Then Exit Sub &#39; string not found
    Dim dirg As Range: Set dirg = Intersect(durg, dfCell.EntireRow) _
        .Resize(srCount) &#39; your code additionally suggests &#39;.Offset(-1)&#39; !?
    
    &#39;Debug.Print svrg.Address, dfCell.Address, dirg.Address
    
    &#39; Insert and copy.
    
    dirg.Insert Shift:=xlShiftDown
    &#39; Cannot determine the &#39;CopyOrigin&#39; parameter without seeing the data.
    
    &#39; Copy.
    svrg.Copy dirg.Columns(1).Offset(-srCount)
    
    &#39; Clean up!?
    
    sws.Rows(&quot;3:11&quot;).Hidden = True
    If Not wb Is ActiveWorkbook Then wb.Activate
    dws.Select
    
    Application.ScreenUpdating = True

    &#39; Inform.
    
    MsgBox &quot;Filtered rows inserted.&quot;, vbInformation

End Sub

huangapple
  • 本文由 发表于 2023年2月18日 04:30:01
  • 转载请务必保留本文链接:https://go.coder-hub.com/75489005.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定