1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

code for copying till last row

Discussion in 'VBA Macros' started by Veeru106, Jul 20, 2017.

  1. Veeru106

    Veeru106 Member

    Messages:
    125
    Hi,


    I am looking for a code , which copy my data till last row and paste in next sheet "Var".

    Sample file attached

    Thanks in advance

    Attached Files:

  2. YasserKhalil

    YasserKhalil Active Member

    Messages:
    740
    Hello
    Try this code
    Code (vb):
    Sub Test()
        Dim ws As Worksheet
        Dim sh As Worksheet
        Dim lr As Long
       
        Set ws = Sheets("Raw")
        Set sh = Sheets("Var")
        lr = ws.Cells(Rows.Count, 2).End(xlUp).Row
       
        ws.Range("A1:D" & lr).Copy
        sh.Range("A1").PasteSpecial xlPasteValues
       
        Application.CutCopyMode = False
    End Sub
    Monty likes this.
  3. Veeru106

    Veeru106 Member

    Messages:
    125
    Thanks but ....what I want it should not pick last row..which is total.....all the data above total only
  4. Veeru106

    Veeru106 Member

    Messages:
    125
    yeah I find out......
    Thanks for quick reply buddy
  5. Monty

    Monty Well-Known Member

    Messages:
    836
    Hello Veeru.

    Yasser provided you the code which works perfectly...

    As per the change you asked for not to copy the last line..

    Try this!

    Code (vb):
    Sub Test()
        Dim ws As Worksheet
        Dim sh As Worksheet
        Dim lr As Long
     
        Set ws = Sheets("Raw")
        Set sh = Sheets("Var")
        lr = ws.Cells(Rows.Count, 2).End(xlUp).Row - 1
     
        ws.Range("A1:D" & lr).Copy
        sh.Range("A1").PasteSpecial xlPasteValues
     
        Application.CutCopyMode = False
    End Sub
     
    YasserKhalil likes this.
  6. YasserKhalil

    YasserKhalil Active Member

    Messages:
    740
    Thanks a lot Mr.Monty
    And alternative you can get the last row from column A
    Code (vb):
    lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
    So replace 2 with 1 in the code
  7. Monty

    Monty Well-Known Member

    Messages:
    836
    Noted Boss!
  8. Veeru106

    Veeru106 Member

    Messages:
    125
    Yes exactly ...what I did...replacing 2 with 1....another thing can we do something that after pasting data in col. B. if I change
    Code (vb):
    sh.Range("A1").PasteSpecial xlPasteValues
    to B1....

    it will give serial numbers in col. A till last row...
  9. Monty

    Monty Well-Known Member

    Messages:
    836
    What is your confusion?
  10. Veeru106

    Veeru106 Member

    Messages:
    125
    now I have data copied in col. b from other sheet...what i want is serial number to appear in col. A from row 2 till last row....
  11. Monty

    Monty Well-Known Member

    Messages:
    836
    So you want to copy data from sheet "RAW" to sheet "VAR" but only from column b and wanted in "A" column serial numbers?
  12. Veeru106

    Veeru106 Member

    Messages:
    125
    Yes excatly
  13. Monty

    Monty Well-Known Member

    Messages:
    836
    Try this!

    Code (vb):
    Sub Test()
        Dim ws As Worksheet
        Dim sh As Worksheet
        Dim lr As Long
       
        Set ws = Sheets("Raw")
        Set sh = Sheets("Var")
        lr = ws.Cells(Rows.Count, 2).End(xlUp).Row - 1
        ws.Range("A1:D" & lr).Copy
        sh.Range("b1").PasteSpecial xlPasteValues
        lr = ws.Cells(Rows.Count, 2).End(xlUp).Row - 1
            For i = 1 To lr
                Range("A" & i) = i
            Next i
           
        Application.CutCopyMode = False
    End Sub
     
  14. Veeru106

    Veeru106 Member

    Messages:
    125
    This is not what I want..

    it is copying data from raw to Var and pasting in Col. B
    but creating serial number in sheet Raw , where as it should do that in Var sheet col. A...
  15. Monty

    Monty Well-Known Member

    Messages:
    836
    Code (vb):
    Sub Test()
        Dim ws As Worksheet
        Dim sh As Worksheet
        Dim lr As Long
     
        Set ws = Sheets("Raw")
        Set sh = Sheets("Var")
        lr = ws.Cells(Rows.Count, 2).End(xlUp).Row - 1
        ws.Range("A1:D" & lr).Copy
        sh.Range("b1").PasteSpecial xlPasteValues
        lr = sh.Cells(Rows.Count, 2).End(xlUp).Row - 1
            For i = 1 To lr
                sh.Range("A" & i) = i
            Next i
         
        Application.CutCopyMode = False
    End Sub
     
  16. Veeru106

    Veeru106 Member

    Messages:
    125
    Need more correction...it is doing fine but we have 7 rows but is counting till 6...any idea why
  17. Veeru106

    Veeru106 Member

    Messages:
    125
    ok got it,,,,i have removed -1 and it is working fine thank you a ton
  18. Monty

    Monty Well-Known Member

    Messages:
    836
    Am glad..You solved it!
  19. Veeru106

    Veeru106 Member

    Messages:
    125
    Sorry for coming back again...can we have serial numbers from A2 instead of A1
  20. Monty

    Monty Well-Known Member

    Messages:
    836
    Code (vb):
    Sub Test()
        Dim ws As Worksheet
        Dim sh As Worksheet
        Dim lr As Long
        Set ws = Sheets("Raw")
        Set sh = Sheets("Var")
        lr = ws.Cells(Rows.Count, 2).End(xlUp).Row - 1
        ws.Range("A1:D" & lr).Copy
        sh.Range("b1").PasteSpecial xlPasteValues
        lr = sh.Cells(Rows.Count, 2).End(xlUp).Row - 1
            Range("A1").Value = "S.No"
            For i = 1 To lr
                lr1 = sh.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                sh.Range("A" & lr1).Value = i
            Next i
       
        Application.CutCopyMode = False
    End Sub
     
    Veeru106 likes this.
  21. Veeru106

    Veeru106 Member

    Messages:
    125
    It is still not solved my purpose but I have made some changes and it is working fine....Thank you for your time and efforts...Really appreciate it
  22. Veeru106

    Veeru106 Member

    Messages:
    125
    Hi... again coming back on this...below is working fine except it is picking data till row 6,, and not picking row 7...Monty can you please look into the sam
    Code (vb):
    Sub hhh()

        Dim ws As Worksheet
        Dim sh As Worksheet
        Dim lr As Long
    'this will copy data from sum if order 2 to sum if order 3 tab

        Set ws = Sheets("Raw")
        Set sh = Sheets("Sheet1")
        lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
        ws.Range("A2:B" & lr).Copy
        sh.Range("b1").PasteSpecial xlPasteValues
        lr = sh.Cells(Rows.Count, 2).End(xlUp).Row
            For i = 1 To lr
                sh.Range("A" & i) = i
            Next i
       
        Application.CutCopyMode = False
    End Sub
     
    e...

    Attached Files:

  23. Monty

    Monty Well-Known Member

    Messages:
    836
    Row 7 is your grand total..which you do not want!
    Please tell me what is your plan we can guess
  24. Veeru106

    Veeru106 Member

    Messages:
    125
    sorry for the confusion.....here I am talking about new worksheet "Net", which I have attached....there is no total therein and I want to capture everything till last row into new sheet...thanks
  25. Veeru106

    Veeru106 Member

    Messages:
    125
    File attached

    Attached Files:

Share This Page