?
Solved

Excel VBA How to copy data only from one sheet and paste values only into another sheet

Posted on 2010-01-08
15
Medium Priority
?
728 Views
Last Modified: 2013-11-25
Hi there,
This may be simple to some of you experts (which is why I"m here ;o)  but as beginner I'm struggling. I'm trying to copy Data only (no Header (first row) and no total row (last row) from a second table within the same workbook. My broken code below copies over the data with header and last row,  and in addition, which I can't figure out why it shift's down one and over one data block of data and copies the data again, and again, etc.  So two things I'm looking to accomplish, 1) to copy data only, and 2) so perform just one paste values only data activity.

Much thanks for the help ! !


Here's the code broken I'm using

Sub TransTblData2()

Dim rTable As Range
Dim lHeadersRows As Long
Dim intRow As Long
Set rTable = Sheet2.ListObjects("TblData2").Range.SpecialCells(xlCellTypeVisible)
    lHeadersRows = rTable.ListHeaderRows
    If lHeadersRows > 0 Then
        Set rTable = rTable.Resize(rTable.Rows.Count - lHeadersRows)
        Set rTable = rTable.Offset(1)
    End If
    rTable.Copy
        intRow = Sheet3.Cells(Sheet3.Rows.Count, 1).End(xlUp).Row + 1
        Sheet3.Rows(intRow).PasteSpecial xlPasteValues
        Sheet3.Rows(Sheet3.UsedRange.Rows.Count).Clear
 End Sub
0
Comment
Question by:Calvin LeBlanc
  • 8
  • 7
15 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26234699
Try this:

Sub TransTblData2()

    Dim rTable As Range
    Dim lHeadersRows As Long
    Dim intRow As Long

    With Sheet2.ListObjects("TblData2")
        Set rTable = .Resize(.Rows.Count - .ListHeaderRows - 1).Offset(1).SpecialCells(xlCellTypeVisible)
    End With
    rTable.Copy
    intRow = Sheet3.Cells(Sheet3.Rows.Count, 1).End(xlUp).Row + 1
    Sheet3.Rows(intRow).PasteSpecial xlPasteValues
   
 End Sub

Kevin
0
 

Author Comment

by:Calvin LeBlanc
ID: 26238236
Thanks very much for the quick turn reply.

Almost. I now get an "Invalid Qualifyer" error on .Resize.
I tried rTable just before it and I get another error "Object doesn't support this property method".

Any suggestions?  

Thank you !
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26239141
Change:

    With Sheet2.ListObjects("TblData2")

to:

    With Sheet2.ListObjects("TblData2").Range

Kevin
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:Calvin LeBlanc
ID: 26239644
almost,, you solved the copying over of data only,, but the repetitive copying is still occurring,, Now, rather than cascading in a downward right angle, the code is copying to the right of the first copy, until it reaches the right side of the sheet.

your thoughts?
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 26261629
Try this:

Sub TransTblData2()

    Dim rTable As Range
    Dim lHeadersRows As Long
    Dim intRow As Long

    With Sheet2.ListObjects("TblData2").Range
        Set rTable = .Resize(.Rows.Count - .ListHeaderRows - 1).Offset(.ListHeaderRows).SpecialCells(xlCellTypeVisible)
        rTable.Copy
        intRow = Sheet3.Cells(Sheet3.Rows.Count, 1).End(xlUp).Row + 1
        Sheet3.Rows(intRow).Resize(.Rows.Count - .ListHeaderRows - 1, .Columns.Count).PasteSpecial xlPasteValues
    End With

End Sub

Kevin
0
 

Author Closing Comment

by:Calvin LeBlanc
ID: 31674887
Spot on ! Zorvek/Kevin, thank you very much for getting me over this hurdle. This was so very helpful, and much appreciated.

C
0
 

Author Comment

by:Calvin LeBlanc
ID: 26280543
Hi there,
Not sure why, but this code is breaking now. It keeps copy/pasting multiple occurrence of the data rather than copying the filtered table data just one time.

Ant thoughts?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26281131
When this line executes:

        Sheet3.Rows(intRow).Resize(.Rows.Count - .ListHeaderRows - 1, .Columns.Count).PasteSpecial xlPasteValues

what are the values in .Rows.Count and .ListHeaderRows? Do they seem reasonable?

Kevin
0
 

Author Comment

by:Calvin LeBlanc
ID: 26282699
Thanks for the follow-up. the result does not look like a reasonable amount.   xl paste values = 4163  when it should be counting 2 rows as a result or more along the lines of 30-40 total values. as an FYI, I have about 250,000 rows of data in this one table that is being copied. If we can get just the header row not to copy over (Table Data Only) that would fix me.

Many Thanks!
0
 

Author Comment

by:Calvin LeBlanc
ID: 26282713
correction =
 FYI, I have about 250,000 rows of data in this one table that is being copied.

correct =
 FYI, I have about 250,000 rows of data in this one table that is being filtered.



Current results are two rows that need to be copied over.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26286216
I'm kind of lost here. What are you trying to do?

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26286229
You didn't answer the question: When this line executes:

        Sheet3.Rows(intRow).Resize(.Rows.Count - .ListHeaderRows - 1, .Columns.Count).PasteSpecial xlPasteValues

what are the values in .Rows.Count and .ListHeaderRows? Do they seem reasonable?

Kevin
0
 

Author Comment

by:Calvin LeBlanc
ID: 26289246
My apologies Kevin !
.Rows.Count = 256102   ' the 256102 is the total amount of "unfiltered" rows in the table
.ListHeaderRows = 1   ' this is accurate as there is only 1 header row.

What I'm trying to accomplish is copy the "filtered results" of the second of two data tables ("TblData2") and paste the results in the first blank row within the results sheet (Sheet3). the paste area is directly under the results of the first filtered table's pasted results.  

1) copy data without the header row (filtered results only)
2) paste in sheet 3 in the first empty row
3) optional - after the paste process (not sure when this should take place) deselect "TblData2". something like hitting the escape key

Sorry about the confusion,
Thanks for the follow-up and willingness to assist.
0
 

Author Comment

by:Calvin LeBlanc
ID: 26289850
Hey Kevin,
after looking at the values per your request, I revised the line of code shown below to count the filtered lines (intRow) as apposed to all the table rows (.Rows.Count) and this resolved the repetative copying.

initial line
Sheet3.Rows(intRow).Resize(.Rows.Count - .ListHeaderRows - 1, .Columns.Count).PasteSpecial xlPasteValues

revised line
        Sheet3.Rows(intRow).Resize(intRow - .ListHeaderRows - 1, .Columns.Count).PasteSpecial xlPasteValues

Now it works like a charm.

Thanks soooo much for the followup :o)
Its experts like you that give newbies like me courage to keep hanging in there by providing insite on methods to troubleshoot .
Many thanks again
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26289874
Aha!

Now I see!

Excellent work.

Kevin
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question