Results 1 to 3 of 3

Thread: Find and paste data into first blank row in table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find and paste data into first blank row in table

    I am working on a macro that needs to be able to pastedata into a table. The macro does several different filters and after eachfilter the results should be copied and pasted to a table. The issue I amhaving is the table it is copying to starts on B17 (there are blank rows andother tables above it).
    I have had no issue accomplishing the same thing on othermacros but the table always starts in A1.

    Below is the start of the macro code (I know it can becleaner, I just use the macro recorder and clean it up as best I can). Thefirst filter, copy, and paste is fine but then once the second filter is done Idon’t know how to tell it to paste in the first blank row after the data itjust pasted since that cell will always be different.

    Windows("Pivot Data Template.xlsx").Activate
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:= _
    "JohnSmith"
    ActiveSheet.Range("A1").AutoFilter Field:=6,Criteria1:="10", _
    Operator:=xlTop10Items
    Range("A2", Cells(Rows.Count,"A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Windows("XH Reports Template-OR.xlsm").Activate
    Range("Table17[Manager]").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,SkipBlanks _
    :=False,Transpose:=False
    Windows("Pivot Data Template.xlsx").Activate
    ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:= _
    "JaneDoe"
    Range("A2", Cells(Rows.Count,"A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
    Range(Selection,ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Windows("XH Reports Template-OR.xlsm").Activate
    Range("Table17[Manager]"& Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues,Operation:=xlNone, SkipBlanks _
    :=False,Transpose:=False

  2. #2
    Board Regular
    Join Date
    Mar 2013
    Posts
    814
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and paste data into first blank row in table

    maybe something along the lines of this
    Code:
    Dim oLo As ListObject
    Dim nr As Long
    
    With WhatEverTheSheetIs
        Set oLo = .ListObjects("WhateverTheTableNameIs")
        nr = oLo.HeaderRowRange.Row + oLo.ListRows.Count + 1
        MsgBox nr
    End With
    A couple of links to sites dealing with tables
    https://www.thespreadsheetguru.com/b...t-excel-tables
    https://www.jkp-ads.com/Articles/Exc...lComments=True

  3. #3
    New Member
    Join Date
    Feb 2017
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and paste data into first blank row in table

    Thanks, I have used the spreedsheetguru page a lot for working with tables.

    I was able to get the code to work by doing Range("Table17").End(xlDown).Offset(1).PasteSpecial xlPasteValues

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •