1. Forum
  2. Usenet
  3. MICROSOFT.PUBLIC.EXCEL

  • Table.DataBodyRange issue

    From noodnutt@gmail.com@21:1/5 to All on Mon Jan 27 18:38:53 2020
    Hi All

    I am trying to copy all of the used range of a table and PasteSpecial the values only.

    This includes the Header as I will be rearranging the columns and manipulating the data.

    I tried the following:

    myRow = tWS.Cells(rows.Count, 1).End(xlUp).Row
    If myRow = 1 Then
    myRow = myRow
    Else
    myRow = myRow + 1
    End If

    With sWS.Range("A:AZ")
    .Copy tWS.Cells(myRow, 1)
    End With

    With sWS.ListObjects("table1").DataBodyRange
    .AutoFilter Field:=6, Criteria1:="<>"
    On Error Resume Next
    .SpecialCells(xlCellTypeVisible).Copy tWS.Cells(myRow, 1)
    On Error GoTo 0
    .AutoFilter
    End With

    The first 'With sWS' works as expected, copying the Header row across to tWS, but! when .ListObjects("table1").DataBodyRange executes it places data in row one of tWS, pushing the Header to row 2, then places data in rows 3 down with the remaining data.

    How can I tell it to copy the DataBodyRange to begin @ row 2..??

    Also, I don't want the paste data as a table, just values please.

    As always

    Many thanks in advance
    Mark.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Tue Jan 28 11:03:50 2020
    Hi Mark,

    Am Mon, 27 Jan 2020 18:38:53 -0800 (PST) schrieb noodnutt@gmail.com:

    I am trying to copy all of the used range of a table and PasteSpecial the values only.

    This includes the Header as I will be rearranging the columns and manipulating the data.

    try:

    Sub CopyValues()
    Dim myRow As Long, LRow As Long
    Dim sWS As Worksheet, tWS As Worksheet
    Dim dest As Range

    Set sWS = Sheets("Import 1")
    Set tWS = Sheets("Import 2")
    myRow = tWS.Cells(Rows.Count, "A").End(xlUp).Row
    Set dest = IIf(myRow = 1, tWS.Cells(1, 1), tWS.Cells(myRow + 1, 1))

    With sWS
    LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .ListObjects(1).Range.AutoFilter Field:=6, Criteria1:="<>"
    If myRow = 1 Then
    .Range("A1:AZ" & LRow).Copy
    dest.PasteSpecial xlPasteValues
    Else
    .Range("A2:AZ" & LRow).Copy
    dest.PasteSpecial xlPasteValues
    End If
    .AutoFilterMode = False
    End With
    End Sub


    Regards
    Claus B.
    --
    Windows10
    Office 2016

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From noodnutt@gmail.com@21:1/5 to All on Tue Jan 28 16:01:48 2020
    Hi Claus

    Once again you have dug me out of a hole.

    Thank you
    Warm regards
    Mark.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)