Copying a range of cells and pasting into one.

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
Hi guys,

I'm trying to copy a range of cells and paste them into one cell.

The code I have so far is
Code:
Sub copy_details()
    
    Dim qty As String
    Dim unit As String
    Dim ref As String
    Dim desc As String
    Dim tradeprice As String
    Dim disc As String
    Dim invprice As String
    Dim total As String
    
    qty = Sheets("QUOTE DETAILS ENTRY").[I14] & [I15] & [I16] & [I17] & [I18] & [I19] & [I20] & [I21] & [I22] & [I23] & [I24] & [I25] & [I26] & [I27] & [I28] & [I29] & [I30] & [I31] & [I32] & [I33] & [I34] & [I35] & [I36] & [I37] & [I38] & [I39] & [I40] & [I41] & [I42] & [I43] & [I44] & [I45] & [I46] & [I47] & [I48] & [I49] & [I50] & [I51] & [I52] & [I53] & [I54] & [I55] & [I56] & [I57] & [I58] & [I59] & [I60] & [I61] & [I62] & [I63] & [I64] & [I65] & [I66] & [I67] & [I68] & [I69] & [I70] & [I71] & [I72] & [I73] & [I74] & [I75] & [I76] & [I77] & [I78] & [I79] & [I80] & [I81] & [I82] & [I83] & [I84] & [I85] & [I86] & [I87] & [I88] & [I89] & [I90] & [I91] & [I92] & [I93] & [I94] & [I95] & [I96] & [I97] & [I98] & [I99] & [I100] & [I101] & [I102] & [I103] & [I104] & [I105] & [I106] & [I107] & [I108] & [I109] & [I110] & [I111] & [I112] & [I113]
    unit = Sheets("QUOTE DETAILS ENTRY").[J14] & [J15] & [J16] & [J17] & [J18] & [J19] & [J20] & [J21] & [J22] & [J23] & [J24] & [J25] & [J26] & [J27] & [J28] & [J29] & [J30] & [J31] & [J32] & [J33] & [J34] & [J35] & [J36] & [J37] & [J38] & [J39] & [J40] & [J41] & [J42] & [J43] & [J44] & [J45] & [J46] & [J47] & [J48] & [J49] & [J50] & [J51] & [J52] & [J53] & [J54] & [J55] & [J56] & [J57] & [J58] & [J59] & [J60] & [J61] & [J62] & [J63] & [J64] & [J65] & [J66] & [J67] & [J68] & [J69] & [J70] & [J71] & [J72] & [J73] & [J74] & [J75] & [J76] & [J77] & [J78] & [J79] & [J80] & [J81] & [J82] & [J83] & [J84] & [J85] & [J86] & [J87] & [J88] & [J89] & [J90] & [J91] & [J92] & [J93] & [J94] & [J95] & [J96] & [J97] & [J98] & [J99] & [J100] & [J101] & [J102] & [J103] & [J104] & [J105] & [J106] & [J107] & [J108] & [J109] & [J110] & [J111] & [J112] & [J113]
    ref = Sheets("QUOTE DETAILS ENTRY").[K14] & [K15] & [K16] & [K17] & [K18] & [K19] & [K20] & [K21] & [K22] & [K23] & [K24] & [K25] & [K26] & [K27] & [K28] & [K29] & [K30] & [K31] & [K32] & [K33] & [K34] & [K35] & [K36] & [K37] & [K38] & [K39] & [K40] & [K41] & [K42] & [K43] & [K44] & [K45] & [K46] & [K47] & [K48] & [K49] & [K50] & [K51] & [K52] & [K53] & [K54] & [K55] & [K56] & [K57] & [K58] & [K59] & [K60] & [K61] & [K62] & [K63] & [K64] & [K65] & [K66] & [K67] & [K68] & [K69] & [K70] & [K71] & [K72] & [K73] & [K74] & [K75] & [K76] & [K77] & [K78] & [K79] & [K80] & [K81] & [K82] & [K83] & [K84] & [K85] & [K86] & [K87] & [K88] & [K89] & [K90] & [K91] & [K92] & [K93] & [K94] & [K95] & [K96] & [K97] & [K98] & [K99] & [K100] & [K101] & [K102] & [K103] & [K104] & [K105] & [K106] & [K107] & [K108] & [K109] & [K110] & [K111] & [K112] & [K113]
    desc = Sheets("QUOTE DETAILS ENTRY").[L14] & [L15] & [L16] & [L17] & [L18] & [L19] & [L20] & [L21] & [L22] & [L23] & [L24] & [L25] & [L26] & [L27] & [L28] & [L29] & [L30] & [L31] & [L32] & [L33] & [L34] & [L35] & [L36] & [L37] & [L38] & [L39] & [L40] & [L41] & [L42] & [L43] & [L44] & [L45] & [L46] & [L47] & [L48] & [L49] & [L50] & [L51] & [L52] & [L53] & [L54] & [L55] & [L56] & [L57] & [L58] & [L59] & [L60] & [L61] & [L62] & [L63] & [L64] & [L65] & [L66] & [L67] & [L68] & [L69] & [L70] & [L71] & [L72] & [L73] & [L74] & [L75] & [L76] & [L77] & [L78] & [L79] & [L80] & [L81] & [L82] & [L83] & [L84] & [L85] & [L86] & [L87] & [L88] & [L89] & [L90] & [L91] & [L92] & [L93] & [L94] & [L95] & [L96] & [L97] & [L98] & [L99] & [L100] & [L101] & [L102] & [L103] & [L104] & [L105] & [L106] & [L107] & [L108] & [L109] & [L110] & [L111] & [L112] & [L113]
    tradeprice = Sheets("QUOTE DETAILS ENTRY").[M14] & [M15] & [M16] & [M17] & [M18] & [M19] & [M20] & [M21] & [M22] & [M23] & [M24] & [M25] & [M26] & [M27] & [M28] & [M29] & [M30] & [M31] & [M32] & [M33] & [M34] & [M35] & [M36] & [M37] & [M38] & [M39] & [M40] & [M41] & [M42] & [M43] & [M44] & [M45] & [M46] & [M47] & [M48] & [M49] & [M50] & [M51] & [M52] & [M53] & [M54] & [M55] & [M56] & [M57] & [M58] & [M59] & [M60] & [M61] & [M62] & [M63] & [M64] & [M65] & [M66] & [M67] & [M68] & [M69] & [M70] & [M71] & [M72] & [M73] & [M74] & [M75] & [M76] & [M77] & [M78] & [M79] & [M80] & [M81] & [M82] & [M83] & [M84] & [M85] & [M86] & [M87] & [M88] & [M89] & [M90] & [M91] & [M92] & [M93] & [M94] & [M95] & [M96] & [M97] & [M98] & [M99] & [M100] & [M101] & [M102] & [M103] & [M104] & [M105] & [M106] & [M107] & [M108] & [M109] & [M110] & [M111] & [M112] & [M113]
    disc = Sheets("QUOTE DETAILS ENTRY").[N14] & [N15] & [N16] & [N17] & [N18] & [N19] & [N20] & [N21] & [N22] & [N23] & [N24] & [N25] & [N26] & [N27] & [N28] & [N29] & [N30] & [N31] & [N32] & [N33] & [N34] & [N35] & [N36] & [N37] & [N38] & [N39] & [N40] & [N41] & [N42] & [N43] & [N44] & [N45] & [N46] & [N47] & [N48] & [N49] & [N50] & [N51] & [N52] & [N53] & [N54] & [N55] & [N56] & [N57] & [N58] & [N59] & [N60] & [N61] & [N62] & [N63] & [N64] & [N65] & [N66] & [N67] & [N68] & [N69] & [N70] & [N71] & [N72] & [N73] & [N74] & [N75] & [N76] & [N77] & [N78] & [N79] & [N80] & [N81] & [N82] & [N83] & [N84] & [N85] & [N86] & [N87] & [N88] & [N89] & [N90] & [N91] & [N92] & [N93] & [N94] & [N95] & [N96] & [N97] & [N98] & [N99] & [N100] & [N101] & [N102] & [N103] & [N104] & [N105] & [N106] & [N107] & [N108] & [N109] & [N110] & [N111] & [N112] & [N113]
    invprice = Sheets("QUOTE DETAILS ENTRY").[O14] & [O15] & [O16] & [O17] & [O18] & [O19] & [O20] & [O21] & [O22] & [O23] & [O24] & [O25] & [O26] & [O27] & [O28] & [O29] & [O30] & [O31] & [O32] & [O33] & [O34] & [O35] & [O36] & [O37] & [O38] & [O39] & [O40] & [O41] & [O42] & [O43] & [O44] & [O45] & [O46] & [O47] & [O48] & [O49] & [O50] & [O51] & [O52] & [O53] & [O54] & [O55] & [O56] & [O57] & [O58] & [O59] & [O60] & [O61] & [O62] & [O63] & [O64] & [O65] & [O66] & [O67] & [O68] & [O69] & [O70] & [O71] & [O72] & [O73] & [O74] & [O75] & [O76] & [O77] & [O78] & [O79] & [O80] & [O81] & [O82] & [O83] & [O84] & [O85] & [O86] & [O87] & [O88] & [O89] & [O90] & [O91] & [O92] & [O93] & [O94] & [O95] & [O96] & [O97] & [O98] & [O99] & [O100] & [O101] & [O102] & [O103] & [O104] & [O105] & [O106] & [O107] & [O108] & [O109] & [O110] & [O111] & [O112] & [O113]
    total = Sheets("QUOTE DETAILS ENTRY").[P14] & [P15] & [P16] & [P17] & [P18] & [P19] & [P20] & [P21] & [P22] & [P23] & [P24] & [P25] & [P26] & [P27] & [P28] & [P29] & [P30] & [P31] & [P32] & [P33] & [P34] & [P35] & [P36] & [P37] & [P38] & [P39] & [P40] & [P41] & [P42] & [P43] & [P44] & [P45] & [P46] & [P47] & [P48] & [P49] & [P50] & [P51] & [P52] & [P53] & [P54] & [P55] & [P56] & [P57] & [P58] & [P59] & [P60] & [P61] & [P62] & [P63] & [P64] & [P65] & [P66] & [P67] & [P68] & [P69] & [P70] & [P71] & [P72] & [P73] & [P74] & [P75] & [P76] & [P77] & [P78] & [P79] & [P80] & [P81] & [P82] & [P83] & [P84] & [P85] & [P86] & [P87] & [P88] & [P89] & [P90] & [P91] & [P92] & [P93] & [P94] & [P95] & [P96] & [P97] & [P98] & [P99] & [P100] & [P101] & [P102] & [P103] & [P104] & [P105] & [P106] & [P107] & [P108] & [P109] & [P110] & [P111] & [P112] & [P113]
    
    Sheets("QUOTE").[a12] = qty
    Sheets("QUOTE").[b12] = unit
    Sheets("QUOTE").[c12] = ref
    Sheets("QUOTE").[d12] = desc
    Sheets("QUOTE").[e12] = tradeprice
    Sheets("QUOTE").[f12] = discount & discount2
    Sheets("QUOTE").[g12] = invprice
    Sheets("QUOTE").[h12] = total
End Sub

It comes up with a message saying "Run-time error '13': Type mismatch" and it highlights the "disc" line.

Does anyone know how to fix it?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
just meant as encouragement:
comparing your code with the cleanedup version you will see the last one is easier to read, understand and edit
it would be a chance to learn more about it
testing should only cost you 2 minutes
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
I've now tested the cleaned up code you posted and it doesn't work for me.

My original code works though.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
doesn't work
doesn't tell much about the problem
I tried to replace everything correctly, but didn't test for results, as this is difficult to reproduce your entire situation
the general purpose of "reduction and clarity" is achieved but typos are possible
does it bug ?
does it do nothing ?
does it generate unexpected results ?
feel free to not respond if you are not really intrested in enhancements or if you have other priorities: I don't want to "push" this further then you would like

God bless you!
Erik
 

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
I'm grateful that you take the time to try and help me.

I'm sorry I wasn't very clear before, but the codes runs but it appears to do nothing.

It may do something but I cannot see anything happen.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

if you want, you may send me your workbook
I'll take a look
 

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
I have sent you the workbook via email.

I have filled in a coupleof details as the sheet looks up details on stock items etc..
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
my main error within the code was simple
qty = qty & .Cells(10, i)
rownumber
columnnumber
10 should be 9 (column I)
so this should have been
qty = qty & .Cells(i, 9)

then an edit-error, which you can easily see
qty = qty & .Cells(i)
unit = qty & .Cells(11, i)
ref = qty & .Cells(12, i)

result
Code:
Sub copy_details()
    
    Dim qty As String
    Dim unit As String
    Dim ref As String
    Dim desc As String
    Dim tradeprice As String
    Dim disc As String
    Dim invprice As String
    Dim total As String
    With Sheets("QUOTE DETAILS ENTRY")
        For i = 14 To 113
        qty = qty & .Cells(i, 9)
        unit = unit & .Cells(i, 10)
        ref = ref & .Cells(i, 11)
        desc = desc & .Cells(i, 12)
        tradeprice = tradeprice & .Cells(i, 13)
        disc = disc & .Cells(i, 14)
        invprice = invprice & .Cells(i, 15)
        total = total & .Cells(i, 16)
        Next i
    End With
    
    With Sheets("QUOTE")
    .[a12] = qty
    .[b12] = unit
    .[c12] = ref
    .[d12] = desc
    .[e12] = tradeprice
    .[f12] = discount & discount2
    .[g12] = invprice
    .[h12] = total
    End With
    
End Sub

I see you got this code twice
then you can put it in a separate macro and call it

Code:
Sub Main()
MsgBox "Main routine started"
macro1
MsgBox "back in main routine"
End Sub

Sub macro1()
MsgBox "this is macro1"
End Sub

your code still contains the "error" which I mentioned (or didn't I?) before
qty = Sheets("QUOTE DETAILS ENTRY").[I14] & [I15] etcetera
it doesn't make sense to write the sheetname before the first cellreference and not before the others
since you are running this macro with a button located on the "QUOTE DETAILS ENTRY"sheet you don't need the sheetreference
but when you would run it from another sheet you would get strange results
[I15] is refering to activesheet !
therefore I used the "With" syntax which makes it easy to just add a dot before the references

I hope this can help you to get more insight in this stuff :)

kind regards,
Erik
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,550
Members
417,151
Latest member
ChickenTenderer

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
Top