copy selected cells to new sheet in a row Solved/Closed

Question

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 



Configuration: Windows XP / Internet Explorer 8.0

venkat1926 · Accepted Answer

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

suryam · Answer

HI, BIG THANKS FOR YOUR HELP/

problem is solved
Many thanks again
bye suryam

suryam · Answer

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

venkat1926 · Answer

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

suryam · Answer

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 · Answer

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.

suryam · Answer

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 · Answer

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

suryam · Answer

hi, thanks very much.It worked without trouble. Really you have helped me greatly. BIG thanks to you. bye suryam

Copy selected cells to new sheet in a row

9 responses

Similar discussions

Subscribe To Our Newsletter!