Copy and Paste Cell value

vijay2482

Board Regular
Joined
Mar 3, 2009
Messages
142
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

thanks in advance
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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


any better solution please
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top