Copy selected cells to new sheet in a row [Solved/Closed]

Report
-
 suryam -
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 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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
4
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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