Excel_how to paste copied rows into a table having a filter

Get help with using AutoHotkey and its commands and hotkeys
inseption86
Posts: 89
Joined: 19 Apr 2018, 00:24

Excel_how to paste copied rows into a table having a filter

15 Mar 2020, 09:06

Hi, how to paste copied rows into a table having a filter

Code: Select all


FormatTime, TimeString, , dd.MM.yyyy

Xl := ComObjCreate("Excel.Application")
book := Xl.Workbooks.Open("....")
Xl.Visible := True

book.workSheets(1).Range("A1", "A" . book.workSheets(1).UsedRange.Rows.Count).Copy

book2 := Xl.Workbooks.Open("........") 

 If (book2.Worksheets(1).FilterMode)
	book2.Worksheets(1).ShowAllData
	
	
book2.Worksheets(1).Range("A1:B1").AutoFilter(1, TimeString)


book2.workSheets(1).Range("B1", "B" . book2.workSheets(1).UsedRange.Rows.Count).PasteSpecial() ???????


Last edited by inseption86 on 26 Mar 2020, 11:53, edited 3 times in total.
inseption86
Posts: 89
Joined: 19 Apr 2018, 00:24

Re: Excel_how to paste copied rows into a table having a filter

22 Mar 2020, 08:14

how to Paste to Visible Cells in Excel ?
User avatar
flyingDman
Posts: 568
Joined: 29 Sep 2013, 19:01
Location: Burbank, California

Re: Excel_how to paste copied rows into a table having a filter

26 Mar 2020, 14:11

I believe that pasting rows at the bottom of an existing filtered range is not different than at the bottom of a non-filtered range. (I assume that that pasting at the bottom was your intent).If the added range does not leave a "gap" (a blank row), Excel figures out that the intent is for the filter to apply to the new rows as well. This code works for me:

Code: Select all

Xl := ComObjCreate("Excel.Application") 
file2open1 := "C:\Users\xxx\Documents\Spreadsheets\test1.xlsx"               ; main spreadsheet
file2open2 := "C:\Users\xxx\Documents\Spreadsheets\test2.xlsx"               ; contains the row to be added to the main sheet
wrkbk1 := Xl.Workbooks.Open(file2open1)
wrkbk2 := Xl.Workbooks.Open(file2open2)

wrkbk2.worksheets(1).range("a1").currentregion.copy				; you can use usedrange if there is only one range
data := clipboard
Wrkbk2.close(1) 											;save and close 

lstrw := wrkbk1.worksheets(1).range("a1").currentregion.rows.count     	;calculates last row
wrkbk1.worksheets(1).range("a" lstrw + 1).pastespecial(-4104)			;pastes in the row below lstrw starting in col 1
wrkbk1.worksheets(1).range("a1").currentregion.AutoFilter(1, "x")		;filters on "x" (use timestring in your example)
;Xl.Visible := True
Wrkbk1.close(1) 											;save and close
xl.quit()
inseption86
Posts: 89
Joined: 19 Apr 2018, 00:24

Re: Excel_how to paste copied rows into a table having a filter

27 Mar 2020, 01:21

This example is not suitable for me, first I have to use a filter in one table, then copy data from two other tables and paste it into the original table that has a filter.


Here I am doing this macro now

Code: Select all

Option Explicit       модуль
Dim rCopyRange As Range
Sub My_Copy()
    If Selection.Count > 1 Then
        Set rCopyRange = Selection.SpecialCells(xlVisible)
    Else: Set rCopyRange = ActiveCell
    End If
End Sub

Sub My_Paste()
    Dim rCell As Range, li As Long, le As Long, lCount As Long, iCol As Integer, iCalculation As Integer
    Application.ScreenUpdating = False
    iCalculation = Application.Calculation: Application.Calculation = -4135
    For iCol = 1 To rCopyRange.Columns.Count
        li = 0: lCount = 0: le = iCol - 1
        For Each rCell In rCopyRange.Columns(iCol).Cells
            Do
                If ActiveCell.Offset(li, le).EntireColumn.Hidden = False And _
                   ActiveCell.Offset(li, le).EntireRow.Hidden = False Then
                    rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1
                End If
                li = li + 1
            Loop While lCount >= rCell.Row - rCopyRange.Cells(1).Row
        Next rCell
    Next iCol
    Application.ScreenUpdating = True: Application.Calculation = iCalculation
End Sub


Return to “Ask For Help”

Who is online

Users browsing this forum: akaza_dorian, Bing [Bot], SashaChernykh, Scr1pter and 395 guests