Copy three column ranges to a new worksheet? Possible to do?

[heathclif]

This seems like it would be easy, but for the life of me I can't figure it out.

I have three column ranges on a worksheet and i was trying to copy them to a different worksheet.

BC-BX
CA-CV
DR-EM
EO-FJ

I think the column lengths are all equal.

Is this even possible using Excel? Or do I need to migrate the data to Access or Oracle 12c?

 

[heathclif]

Thank you Rick! Always a pleasure to see you!

 

[heathclif]

I was going to use the code below, but I need to to PasteValues only. Is there some way to insert pastevalues into this?

Public Sub My_Copy()

Sheets("Sheet1").Columns("J:K").Copy Destination:=Sheets("Sheet2").Range("J1")

End Sub

 

[heathclif]

This works, but I need some additional help getting the output right. Is there anyway to get the macro to "carriage return" (heh heh) meaning...when it is pasting into Sheet1, it will paste everything into the sheet without overriding? This I think would be the keystone and complete this thread?

(ie. I need to replace "Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues" with something.)

See code below.

----
Sub columns()

Worksheets("UNTAG").Range("BC24:BX100").Copy
Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues

Worksheets("UNTAG").Range("CA24:CV100").Copy
Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues

Worksheets("UNTAG").Range("DR24:EM100").Copy
Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues

Worksheets("UNTAG").Range("EO24:FJ100").Copy
Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues

End Sub

 

[Rick Rothstein]

That's right. BC to BX is the range from Columns BC to the Columns BX. Also the same for CA-CV, DR-EM, EO-FJ. So there are a total of 4 ranges.

<tbody>
</tbody>

What row does the data (excluding headers if any) start on?

Do all the columns in any single range all end on the same row?

 

[heathclif]

It starts on row 24 which is definate. I selected to end on row 100 because I am creating a template. The length of the data could extend past 100, but if it does, I'll edit the macro manually for that instance. I guess the macro has to be flexible to consider that the copied range is variable (hence the pasted range also variable).

 

[Rick Rothstein]

It starts on row 24 which is definate. I selected to end on row 100 because I am creating a template. The length of the data could extend past 100, but if it does, I'll edit the macro manually for that instance. I guess the macro has to be flexible to consider that the copied range is variable (hence the pasted range also variable).

We can have the macro do everything automatically... I just need to know what that everything is.

If you only have, say, 85 rows of data in a range, I am guessing you only want those 85 rows moved (not 100 rows which would mean there would be 15 blanks in the copy) correct? If so, then my second question still stands.... will all the columns in a single range all end on the same row number... or could the various columns end at different, staggered rows?

Also, I should have asked... where is the output supposed to go... a new sheet or should the first range be extended?

 

[heathclif]

Thank you for helping out Rick.

>>Do all the columns in any single range all end on the same row?

Yes, the columns in each range all end on the same row. Each range contains unified blocks on data.

>>If you only have, say, 85 rows of data in a range, I am guessing you only want those 85 rows moved (not 100 rows which would mean there would be 15 blanks in the copy) correct?

Yes, if this was the case, I would only want 85 rows moved.

>>where is the output supposed to go... a new sheet or should the first range be extended?

New sheet.

...And thank you for your patience.

 

[heathclif]

Huzzah! And there is great ringing of bells! This is the code that eventually worked. Hope it can help someone in the future.

This copies 4 columns from one worksheet and then pastevalues into another worksheet.

The great benefit now is that I will not need to migrate to Oracle. Thanks to ALL!

Sub columns()

Worksheets("UNTAG").Range("BC24:BX100").Copy
Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues

Worksheets("UNTAG").Range("CA24:CV100").Copy
Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

Worksheets("UNTAG").Range("DR24:EM100").Copy
Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

Worksheets("UNTAG").Range("EO24:FJ100").Copy
Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

End Sub

 

[Rick Rothstein]

Huzzah! And there is great ringing of bells! This is the code that eventually worked. Hope it can help someone in the future.

This copies 4 columns from one worksheet and then pastevalues into another worksheet.

The great benefit now is that I will not need to migrate to Oracle. Thanks to ALL!

Sub columns()

Worksheets("UNTAG").Range("BC24:BX100").Copy
Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues

Worksheets("UNTAG").Range("CA24:CV100").Copy
Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

Worksheets("UNTAG").Range("DR24:EM100").Copy
Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

Worksheets("UNTAG").Range("EO24:FJ100").Copy
Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

End Sub

This is how I would write the code (it adjust for however many rows are in each group automatically, even if more than 100)...

[table="width: 500"]
[tr]
	[td]Sub MoveData()
  Dim NextRow As Long, AR As Range, Rng As Range
  NextRow = 1
  For Each AR In Sheets("UNTAG").Range("BC24,CA24,DR24,EO24").Areas
    NextRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
    AR.CurrentRegion.Copy Sheets("Sheet1").Cells(NextRow, "A")
  Next
End Sub[/td]
[/tr]
[/table]