• 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...

  • 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

Veeru106

Member
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
 

Attachments

  • Var.xlsx
    9.1 KB · Views: 7
Hello
Try this code
Code:
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
 
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:
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
 
Thanks a lot Mr.Monty
And alternative you can get the last row from column A
Code:
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
So replace 2 with 1 in the code
 
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:
sh.Range("A1").PasteSpecial xlPasteValues
to B1....

it will give serial numbers in col. A till last row...
 
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....
 
So you want to copy data from sheet "RAW" to sheet "VAR" but only from column b and wanted in "A" column serial numbers?
 
Try this!

Code:
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
 
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...
 
Code:
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
 
Code:
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
 
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
 
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:
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...
 

Attachments

  • Net.xlsm
    16.3 KB · Views: 2
Row 7 is your grand total..which you do not want!
Please tell me what is your plan we can guess
 
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
 
Back
Top