Solved

Copy Paste Rows from one sheet to another

Posted on 2013-05-30
7
321 Views
Last Modified: 2013-05-31
I have the code below that works, but I need for it to paste row from Sheet 16 to Sheet 19 below the last used row. And to loop through all the records on Sheet16 to end of last row to insert all onto Sheet19.

Thanks in advance.

Sub copy_cells()
     
    Dim nextrow As Long
     Sheets("cusip_tmp").Activate
     
    nextrow = Sheet16.Cells(Rows.Count, "A").End(xlUp).Row + 1
     
    Application.ScreenUpdating = False
     
    With Sheet16
        .Range("A2").Copy Sheet19.Range("A" & nextrow)
       
    End With
     
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
     
End Sub
0
Comment
Question by:leezac
  • 4
  • 3
7 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 39209275
Do you truly want it pasted?  You could link the cells in Sheet 19 to those in Sheet 16.
0
 

Author Comment

by:leezac
ID: 39209288
I need it pasted as there will be other items added in the rows before this code is run.  I found this code and tried it and it works except it copies the whole row from the Manual sheet and I just need the cells from Column A.

Sub MyCopy()
    Dim lastCol As Long, lastRow As Long

    'copy from
    With Sheets("Manual")
        lastCol = .Cells(2, .Columns.Count).End(xlToLeft).column
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("A2", .Cells(lastRow, lastCol)).Copy
    End With

    'paste to
    With Sheets("Cusip_tmp")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        If lastRow = 1 And .Cells(1) = "" Then lastRow = 0
        .Cells(lastRow + 1, "A").PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With

    Application.CutCopyMode = False
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 39209302
Looks to me like ".Columns.Count" is giving you all the columns.  So, if you have ten columns that code will fill "lastCol" with Column J instead of column A....Count hands back a total sum of everything in it.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:leezac
ID: 39209389
Ok - but how to I modify

' lastCol = .Cells(2, .Columns.Count).End(xlToLeft).column
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("A2", .Cells(lastRow, lastcol)).Copy
0
 
LVL 15

Accepted Solution

by:
David L. Hansen earned 500 total points
ID: 39209554
.Range("A2", .Cells(lastRow, 1)).Copy
0
 

Author Closing Comment

by:leezac
ID: 39211199
Thank you!
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 39211215
Welcome :)
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

831 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