Copy selected cells to new sheet in a row

Solved/Closed
suryam - May 3, 2011 at 01:51 PM
 suryam - May 10, 2011 at 06:37 AM
Hello,
dear all, I have an invoice template in the range b6:f57 in one sheet.. I want to select only some cell contents like c17, f17, f 19 , f 32, f34, f36, f38 and paste them on to another sheet named "invoice details" all in a single row one after the other . once this is done and when i create a new invoice, the NEW cell contents should be copied just below the next blank row on pressing a button ,......and this should continue like this. can anyone help me with a macro do this task . thanks suryam



9 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
May 6, 2011 at 02:36 AM
create a form button and assign to that button this macro

Sub test()
Dim r As Range, j As Integer
Dim array1 As Variant
With Worksheets("sheet1")
array1 = Array(.Range("c17").Value, .Range("f17").Value, .Range("f19").Value, .Range("f32").Value, .Range("f34").Value, .Range("f36").Value, .Range("f38").Value)
With Worksheets("sheet2")
Set r = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
For j = 0 To 6
r.Offset(0, j) = array1(j)
Next j
End With
End With
End Sub
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
May 9, 2011 at 10:23 PM
Sub test()
Dim r As Range, j As Integer
Dim array1 As Variant
With Worksheets("sheet1")
array1 = Array(.Range("c17").Value, Mid(.Range("f17").Value, 9, 4), .Range("f19").Value, .Range("f32").Value, .Range("f34").Value, .Range("f36").Value, .Range("f38").Value)
With Worksheets("sheet2")
Set r = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
For j = 0 To 6
r.Offset(0, j) = array1(j)
Next j
End With
End With
End Sub


see change in array1= statement regarding F17

your second suggestion not clear do you want the same data to be copied in two different sheets. clarify
HI, BIG THANKS FOR YOUR HELP/

problem is solved
Many thanks again
bye suryam
hi one more additional question here. in the cell F17 of invoice worksheet, I have an alpha numeric value IND/APR/6532/11
I want to take only the number 6532 and paste it another sheet along with other cell contents.
and secondly I want to "paste" all these cell values into two different sheets.
can you pls modify suitably and guide me?
thanks
suryam
hi youe your code is very good. working perfectly. In my previous mail, I have asked one more question also ,,...I want to paste the values to TWO DIFFERENT WORKSHEETS. I tried to put "sheet3" along with sheet2 in the vb code
with worksheets("sheet2") ...it is giving error. could you pls guide me so that I can copy the cells from sheet1 and paste them to both sheet2 and sheet3?
bye suryam
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
May 10, 2011 at 06:18 AM
I have already written to you

"your second suggestion not clear do you want the same data to be copied in two different sheets. clarify"

you need clarify with examples.
Hi, you are correct. I want the same data to be copied to TWO DIFFERENT WORKSHEETS namely "SALES" and "INVOICES ".
Once the data is copied to the above worksheets, I will further enter other details manually. thanks for your concern
hoping to recv your modified code
thanks bye suryam
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
May 10, 2011 at 06:32 AM
easiest way now without too much tinkering with the macro is
repeat this below this with sheet2 changed to sheet r

With Worksheets("sheet2")
Set r = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
For j = 0 To 6
r.Offset(0, j) = array1(j)
Next j
End With
With Worksheets("sheet3")
Set r = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
For j = 0 To 6
r.Offset(0, j) = array1(j)
Next j
End With
hi, thanks very much.It worked without trouble. Really you have helped me greatly. BIG thanks to you. bye suryam