0

I've been looking around for this for the past couple days and can't seem to find anything to help me accomplish what I'm trying to do.

I have a sheet that contains data in multiple cells that a user inputs - I would like for when the user hits the submit button that a VBA Macro would copy the data from the multiple cells and paste it into another worksheet on a single row (last row) so I can pull that data later and make changes to it if needed. The worksheet has a unique ID on the top and when searched with that I could pull the data back to the worksheet and make edits to it and save it again.

When I record a Macro and try to multiple select it doesn't let me copy but this is the code that is supplies for the select

Sub Copy()
'
' Copy Macro
'

    Union(Range( _
        "J22:K22,M22,I24:J24,K24:L24,M24,I26:J26,K26:L26,M26,B29:D29,E29:G29,B30:D30,B31:D31,B33:D33,E33:G33,I29,J29:K29,M29,I31:J31,K31:L31,M31,I33:J33,K33:L33,M33,B36:D36,B37:D37,B38:D38,E36:G36,B40:D40,E40:G40,I36,J36:K36,M36" _
        ), Range( _
        "I38:J38,K38:L38,M38,I40:J40,K40:L40,M40,B2:F3,B2:F3,B6:E6,F7:G7,B7:E7,B8:E8,B9:E9,B11:C11,D11:E11,B13:C13,D13:E13,I3:L3,L2,M1,I6:L6,I7:L7,I8:L8,I9:L9,M7,I11:J11,K11:L11,I13:J13,K13:L13,B15:M16,B18:M19,B22:D22" _
        ), Range("B23:D23,B24:D24,E22:G22,B26:D26,E26:G26,I22")).Select

End Sub

This is all the cells I need to copy over and paste to a "Entries" Worksheet in the same workbook. I'm sure someone has asked this before but I'm not sure I'm not using the right key words to search but I'm not finding anything. Allot of stuff to copying multiple row and columns and pasting but nothing for multiple cells.

shA.t
  • 16,580
  • 5
  • 54
  • 111
Bertsector
  • 223
  • 1
  • 3
  • 15
  • How would those cells all fit on a single row? It'd be easy to find the last row, but would you want to print them in any specific way? – Raystafarian Jul 14 '15 at 17:06
  • One way of performing the mapping is to put the source cell addresses in a row at the top of your Entries sheet - each destination column will then map to a specific cell on the data entry sheet. – Tim Williams Jul 14 '15 at 17:11
  • @Raystafarian thx for your reply. Id like the data to be on one Row from A:BK on a single row and I'd adjust the headers based on the copy Macro. Is that what you meant? – Bertsector Jul 14 '15 at 17:14

1 Answers1

2

I think this is what you're trying to do

Sub test()
Dim rng As Range
Set rng = Union(Range( _
        "J22:K22,M22,I24:J24,K24:L24,M24,I26:J26,K26:L26,M26,B29:D29,E29:G29,B30:D30,B31:D31,B33:D33,E33:G33,I29,J29:K29,M29,I31:J31,K31:L31,M31,I33:J33,K33:L33,M33,B36:D36,B37:D37,B38:D38,E36:G36,B40:D40,E40:G40,I36,J36:K36,M36" _
        ), Range( _
        "I38:J38,K38:L38,M38,I40:J40,K40:L40,M40,B2:F3,B2:F3,B6:E6,F7:G7,B7:E7,B8:E8,B9:E9,B11:C11,D11:E11,B13:C13,D13:E13,I3:L3,L2,M1,I6:L6,I7:L7,I8:L8,I9:L9,M7,I11:J11,K11:L11,I13:J13,K13:L13,B15:M16,B18:M19,B22:D22" _
        ), Range("B23:D23,B24:D24,E22:G22,B26:D26,E26:G26,I22"))

Dim WSEntries As Worksheet
Set WSEntries = Sheets("Entries")

Dim lastrow As Integer
lastrow = WSEntries.Cells(Rows.Count, "A").End(xlUp).Row

Dim i As Integer
i = 1
For Each c In rng
    WSEntries.Cells(lastrow + 1, i) = c
    i = i + 1
Next


End Sub

It will iterate through your range left to right from top to bottom.

Are you sure you want to use unionkb though? You could just set your range as all the cells I think it used a union due to the character limit of a function. Did you mean to use intersect kb?

To be clear you're working with three ranges here -

Range("J22:K22,M22,I24:J24,K24:L24,M24,I26:J26,K26:L26,M26,B29:D29,E29:G29,B30:D30,B31:D31,B33:D33,E33:G33,I29,J29:K29,M29,I31:J31,K31:L31,M31,I33:J33,K33:L33,M33,B36:D36,B37:D37,B38:D38,E36:G36,B40:D40,E40:G40,I36,J36:K36,M36")

Range("I38:J38,K38:L38,M38,I40:J40,K40:L40,M40,B2:F3,B2:F3,B6:E6,F7:G7,B7:E7,B8:E8,B9:E9,B11:C11,D11:E11,B13:C13,D13:E13,I3:L3,L2,M1,I6:L6,I7:L7,I8:L8,I9:L9,M7,I11:J11,K11:L11,I13:J13,K13:L13,B15:M16,B18:M19,B22:D22")

Range("B23:D23,B24:D24,E22:G22,B26:D26,E26:G26,I22")

No cells intersect all three ranges.

B2:F3 is twice listed in the second range. Otherwise I don't see any overlap.

Raystafarian
  • 2,902
  • 2
  • 29
  • 42
  • The "Union" was auto generated by Excel itself - I used the record Macro function and this was the code it supplied. Base on your knowledge would intersect work better for something like this? – Bertsector Jul 14 '15 at 17:23
  • Well do you want all of the cells or just cells that are within both ranges? – Raystafarian Jul 14 '15 at 17:24
  • Ahh I see what you mean now, Just the cells that are within both ranges. – Bertsector Jul 14 '15 at 17:27
  • [Union](https://msdn.microsoft.com/en-us/library/office/ff834621.aspx) vs [intersect](https://msdn.microsoft.com/EN-US/library/office/ff835030.aspx) – Raystafarian Jul 14 '15 at 17:29
  • I tried the code and it copied only Cells (M20, I24, K24, M24, I26, K26, M26) But it did help me maybe realize whats going on here. I have Cells that are merged together and I think once I recorded the macro M$ wasn't smart enough to realize that. I think I might have to label each data cell like Tim Williams mentioned above – Bertsector Jul 14 '15 at 17:46
  • Yeah excel isn't great about merged cells, instead just turn off wrapping and center across cells. And I agree with Tim - it might be better to use a named range, but watch out for the character limit - that might be why it used a union. – Raystafarian Jul 14 '15 at 17:47
  • @Bertsector if this solves your issue, please click the green checkmark next to the answer with the solution to mark the question as solved. – Raystafarian Jul 15 '15 at 13:15
  • @Ratstafarian I did try to bud, and I really appreciate your help, but something with my profile says I'm not allowed to yet! Ahh disregard I found what you meant. Done! Thanks again friend! – Bertsector Jul 15 '15 at 13:18