Copying a range of cells and pasting into one.

[Ste_Moore01]

Hi guys,

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

The code I have so far is

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]

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

 

[Ste_Moore01]

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]

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]

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]

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

 

[Ste_Moore01]

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]

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

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

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

 

[Ste_Moore01]

Thanks very much Erik.

I'm very grateful you took the time out to help me with this!