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]

Hi, Ste_Moore01,
this depends on the contents of your cells
check what's in there
example with same error

Sub foo()
Cells(1, 1).Formula = "=1/0"
MsgBox Cells(1, 1).Value
End Sub

now your code
questions in order of "urgency"
1.
qty = Sheets("QUOTE DETAILS ENTRY").[I14] & [I15] & etcetera
do you really mean one cell from sheet "QUOTE DETAILS ENTRY" and the others from the activesheet ?
2.
do you now how to put such a line in a loop (from row 14 to 113) so you would better see from which cells the information is taken ?
3.
do you think all this info is going into one cell ?

kind regards,
Erik

 

[Ste_Moore01]

Just to try and clear it up a bit..

I want cells I14:I113 to be pasted in cell A12 on sheet "Quote" and cells J14:J113 in cell B12 on sheet "Quote" etc..

 

[Ste_Moore01]

Sorry, you posted at the same time as me.

1- The "QUOTE DETAILS ENTRY" sheet is the active sheet and I want all the cells in the code, copied from that sheet.

2- I only know very basic things with VBA so I'm unsure how to do loops.

3- Yes I'd like all this info to go into 1 cell like it has been Concatenated.

 

[erik.van.geit]

did you find errors in cells ?
what do you want to do with the "errors" ?
in the mean time I'll take a look at the loop

 

[Ste_Moore01]

I've made the formulas so there should be no errors at all in these ranges.

 

[erik.van.geit]

"should be"
are you sure there aren't ?

anyway this code will tell you which cells cause bugs since it will stop within the loop
just move your cursor above "i" within .Cells(10, i) ot whatever line the code bugs
as you can see this code is "readable"

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(10, i)
        unit = qty & .Cells(11, i)
        ref = qty & .Cells(12, i)
        desc = qty & .Cells(13, i)
        tradeprice = qty & .Cells(14, i)
        disc = qty & .Cells(15, i)
        invprice = qty & .Cells(16, i)
        total = qty & .Cells(17, i)
        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

best regards,
Erik

 

[Ste_Moore01]

Sorry for the trouble.

I just checked and I'd typed a formula incorrectly

Thanks for the help anyway!

 

[erik.van.geit]

Thanks for the help anyway!

sounds like you focused only on the "main" problem and didn't test my code

"anyway"
sounds also as if my first reply

this depends on the contents of your cells
check what's in there

and the second

did you find errors in cells ?

and again insisting in the third, although you were persisting in saying there where no errors

"should be"
are you sure there aren't ?

didn't help you to find the error

am I to sensitive here ?
Erik

 

[Ste_Moore01]

Sorry for not replying sooner.

Thanks for making the code for me but I had no need to use it once I found the error so I didn't test it.


Your replies did help me to find the error as when you asked me to check I did. At first I assumed I'd typed my formulas correctly but I hadn't.


Thanks for taking the time to try and help me.