Copy Data one sheet to another using vba

[FaizanRoshan88]

Hi,
I have try to copy data from invoices to Worksheet("Recovery Sheet"). I have another code that generate many Invoices. I would like to copy data from new generated invoice to ws one by one. Here is code for copy data but its not work. Please help me if any one know how to do this.

Dim RS as worksheet
Set RS = worksheets("Recovery Sheet")

RS.Range(cells(Rows.Count, 2).End(xlUp).Offset(1, 0)).Select
         Selection.Value = 'value = x1up cell value + 1

    'copy paste data in same row with next column cell

    RS.Range(cell.Offset(0, 1)).Value = Activesheet.Range("A8").Value
    RS.Range(cell.Offset(0, 1)).Value = Activesheet.Range("A9").Value
    RS.Range(cell.Offset(0, 1)).Value = Activesheet.Range("F12").Value
    RS.Range(cell.Offset(0, 1)).Value = Activesheet.Range("F15").Value
    RS.Range(cell.Offset(0, 1)).Value = Activesheet.Range("F16").Value

 

[vletm]

... it works, if You would like to copy five cells value one-by-one to one cell.

 

[Monty]

FaizanRoshan

Can i request you to upload file to help you!

Thanks

 

[Marc L]

Hi !

to ws one by one

Unclear ! What does it crystal clear mean ?

Here is code for copy data but its not work

What does exactly not work ?

We can't guess without a workbook attachment as per forum rules …
​

 

[Monty]

@Marc L

I alway see you asking people to upload file, but it's not happening.

There should be condition in VBA macros thread without sample file should not allow user to post question.

 

[Marc L]

Oh, you're a bit strict !

As this forum is about VBA, some technical questions may have answers
without any attachment. And I know forums without attachment !

With a crystal clear explanation, often an attachment is not needed …

This forum should be about technical VBA questions for
developers, for those writing codes whatever if not profesionals
or students and not for those asking
« do my job without I burn some neurons (and I keep money!) » …
​

 

[Monty]

Thank you..I respect it.

 

[FaizanRoshan88]

Hi,
Thank for replay, here is file i hope its helpful for this.
In this file i have generate some invoices, when i use this code its may be create hundred of invoices.

 

[Monty]

Hello

No sheet exists as Invoice.

Set ws = WB.Sheets("Invoice")

 

[FaizanRoshan88]

Its not sheet name "Invoice" its customer billing invoices. Like, Abbas Rizvi, Abdul Rahman etc

 

[FaizanRoshan88]

Hello

No sheet exists as Invoice.

Set ws = WB.Sheets("Invoice")

Hi,
I need to copy Cell(A8, A9, F12, F15 & F16) data from all sheets except (filter data, Recovery sheet) to ("Recovery Sheet")

 

[FaizanRoshan88]

Hi,
I need to copy Cell(A8, A9, F12, F15 & F16) data from all sheets except (filter data, Recovery sheet) to ("Recovery Sheet")

is this help you to understand?

 

[Monty]

Try this.

Worked other way round.

 

[FaizanRoshan88]

Hi,
Thank You. It working, but just need little bit adjustment. one sheet data will be show in same row.

 

[Monty]

Faizan.

Iam not clear with your question.

 

[FaizanRoshan88]

This code work Perfectly

RS.Range(cells(Rows.Count, 2).End(xlUp).Offset(1, 0)).Select
        Selection.Value = 1

    'insert the value under postcode
    ' this OFFSET refers to the cell 1 row below the "Cell"
   
    RS.Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("A8").Value
    RS.Range("D" & Rows.Count).End(xlUp).Offset(0, 0).Value = Range("A9").Value
    RS.Range("E" & Rows.Count).End(xlUp).Offset(0, 0).Value = Range("F12").Value
    RS.Range("F" & Rows.Count).End(xlUp).Offset(0, 0).Value = Range("F15").Value
    RS.Range("G" & Rows.Count).End(xlUp).Offset(0, 0).Value = Range("F16").Value

 

[FaizanRoshan88]

Faizan.

Iam not clear with your question.

Thank for Help
I it possible to insert Sr No In column "B" and increase. like 1,2,3,4,5,6 etc

 

[Monty]

FaizanRoshan

Try this!

Sub Test()
    Dim ws As Worksheet
    Dim lRows As Long
    Dim i As Integer
   
    Set ws = Worksheets("Recovery Sheet")
    lRows = ws.Range("B" & Rows.Count).End(xlUp).Row
   
    For i = 2 To lRows
        ws.Range("B" & i + 1).Value = ws.Range("B" & i).Value + 1
    Next i
End Sub

 

[FaizanRoshan88]

FaizanRoshan

Try this!

Sub Test()
    Dim ws As Worksheet
    Dim lRows As Long
    Dim i As Integer
  
    Set ws = Worksheets("Recovery Sheet")
    lRows = ws.Range("B" & Rows.Count).End(xlUp).Row
  
    For i = 2 To lRows
        ws.Range("B" & i + 1).Value = ws.Range("B" & i).Value + 1
    Next i
End Sub

Thank You very much for Help.

 

[FaizanRoshan88]

Final Code:

Dim ws as worksheet
Dim 1rows as Long
Dim i as Integer

Set ws = worksheets ("Recovery Sheet")

lRows = RS.Range("B" & Rows.Count).End(xlUp).Row
 
    For i = 5 To lRows
        RS.Range("B" & i + 1).Value = RS.Range("B" & i).Value + 1
    Next i
    RS.Range(cells(Rows.Count, 2).End(xlUp).Offset(0, 0)).Select
        'Selection.FormulaR1C1.Value = "=R[54]C+1"
    RS.Range("C" & Rows.Count).End(xlUp).Offset(0, 0).Value = Range("A8").Value
    RS.Range("D" & Rows.Count).End(xlUp).Offset(0, 0).Value = Range("A9").Value
    RS.Range("E" & Rows.Count).End(xlUp).Offset(0, 0).Value = Range("F12").Value
    RS.Range("F" & Rows.Count).End(xlUp).Offset(0, 0).Value = Range("F15").Value
    RS.Range("G" & Rows.Count).End(xlUp).Offset(0, 0).Value = Range("F16").Value

 

[Marc L]

You can delete from code all .Offset(0, 0) !​