# Copy and Paste Cell value

#### vijay2482

##### Board Regular
i have 2 sheets.
i want to copy 3 cell values from sheet1 to one cell in sheet2.
what exactly i need:
From sheet1 - value of cell AI4,AK4,AU4
To sheet2 - in cells A5 to An(n cells)
and an "-" inbetween AK4 and AUK
example output:
F12345-01
F from AI4
12345 from AK4
01 from AU4

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Willem!

##### Board Regular
Hi,

you can solve this with very simple string-operations, you've should be able to find such operations anyware on the web. Try this:

=Sheet1!AI4 & Sheet1!AK4 & "-" & Sheet1!AU4

#### Colin Legg

##### MrExcel MVP
Using formulas

The formula in Sheet2 and copy down as required. If you want hard coded values then just copy the formulas and then pastespecial values.

Rich (BB code):
``=Sheet1!AI4&Sheet1!AK4&"-"&Sheet1!AU4``

Using VBA, where 20 is the value "n" you referred to:
Rich (BB code):
``````Sub Example()
With Sheet2.Range("A5:A20")
'put in the formulas
.Formula = "=Sheet1!AI4&Sheet1!AK4&""-""&Sheet1!AU4"

'convert to values (if required)
.Value = .Value
End With

End Sub``````

#### vijay2482

##### Board Regular
thanks for the replies.
my sheets r in different workbooks

i have altered the code as follows, but it enters #Ref in the cells from A5 to A20 in sheet2
Code:
`````` Sub Example()
With Workbooks("ECN_Modele_bh").Worksheets("ECN Number").Range("A5:A20")
'put in the formulas
Set sheet1 = Workbooks("ECN_Modele_bh").Worksheets("Front_Page")
.Formula = "=sheet1!AI4&sheet1!AK4&""-""&sheet1!AU4"

'convert to values (if required)
'.Value = .Value
End With

End Sub``````

tell me where im wrong

#### vijay2482

##### Board Regular
Code:
`````` Sub copy()
Workbooks("ECN_Modele_bh").Worksheets("Front_Page").Range("AI4:AK4").copy
Workbooks("ECN_Modele_bh").Worksheets("ECN Number").Range("A5").PasteSpecial xlPasteValues
End Sub``````

i tried to do this but it enters only the value of AI4 from sheet1 in workbook1 to A5 in sheet2 in workbook2

#### Colin Legg

##### MrExcel MVP
thanks for the replies.
my sheets r in different workbooks

i have altered the code as follows, but it enters #Ref in the cells from A5 to A20 in sheet2
Code:
`````` Sub Example()
With Workbooks("ECN_Modele_bh").Worksheets("ECN Number").Range("A5:A20")
'put in the formulas
Set sheet1 = Workbooks("ECN_Modele_bh").Worksheets("Front_Page")
.Formula = "=sheet1!AI4&sheet1!AK4&""-""&sheet1!AU4"

'convert to values (if required)
'.Value = .Value
End With

End Sub``````

tell me where im wrong

Code:
``````Sub Example()
With Workbooks("ECN_Modele_bh").Worksheets("ECN Number").Range("A5:A20")
'put in the formulas
.Formula = "=Front_Page!AI4&Front_Page!AK4&""-""&Front_Page!AU4"

'convert to values (if required)
'.Value = .Value
End With

End Sub``````

#### vijay2482

##### Board Regular
it prints only "-" because the workbook of Front_Page is not mentioned."-" is printed only in A5
Code:
``````Sub Example()
With Workbooks("ECN_Modele_bh").Worksheets("ECN Number").Range("A5:A20")
'put in the formulas
.Formula = "=Front_Page!AI4&Front_Page!AK4&""-""&Front_Page!AU4"
End With
End Sub``````

#### Colin Legg

##### MrExcel MVP
Hi,

What is the name of it's parent workbook then? Just add it into the formula in front of each of the range references. In the code you posted earlier, both the ECN Number and FrontPage worksheets seemed to be in the same workbook which is why I omitted it from the formula:

thanks for the replies.
my sheets r in different workbooks

i have altered the code as follows, but it enters #Ref in the cells from A5 to A20 in sheet2
Rich (BB code):
`````` Sub Example()
With Workbooks("ECN_Modele_bh").Worksheets("ECN Number").Range("A5:A20")
'put in the formulas
Set sheet1 = Workbooks("ECN_Modele_bh").Worksheets("Front_Page")
.Formula = "=sheet1!AI4&sheet1!AK4&""-""&sheet1!AU4"

'convert to values (if required)
'.Value = .Value
End With

End Sub``````

tell me where im wrong

#### vijay2482

##### Board Regular
Re: Copy and Paste Cell value[SOLVED]

Sorry for troubling you...

i have mentioned the same workbook in both the lines.
i have sorted it our now.

thanks a lot

You're welcome!

Replies
1
Views
356
Replies
3
Views
129
Replies
3
Views
228
Replies
1
Views
179
Replies
4
Views
284

1,191,025
Messages
5,984,198
Members
439,877
Latest member
kellylet

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back