Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy columns and paste with blank column between
This will get the job done, but seems really clunky to me.
Where I have a number of columns on sheet 1, some are longer than others, and want to copy them to sheet 2 with a blank column between each copied column. So I could just continue to duplicate the lines column A to A and then column B to C and then column C to E and so on, but there has to be a cleaner way. Also, the number of columns on the first sheet (copy from) could vary so I would need to use something like this to determine the number of columns. i = Cells(1).End(xlToRight).Column Thanks, Howard Option Explicit Sub CopyCol() Range("A1:A" & Range("A1").End(xlDown).Row).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues Range("B1:B" & Range("B1").End(xlDown).Row).Copy Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues Range("C1:C" & Range("C1").End(xlDown).Row).Copy Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy columns and paste with blank column between
Hi Howard,
Am Fri, 8 Nov 2013 08:24:16 -0800 (PST) schrieb Howard: This will get the job done, but seems really clunky to me. Where I have a number of columns on sheet 1, some are longer than others, and want to copy them to sheet 2 with a blank column between each copied column. try: Sub CopyCols() Dim LCol As Integer Dim i As Integer Dim j As Integer With Sheets("Sheet1") LCol = .Cells(1, .Columns.Count).End(xlToLeft).Column j = 1 For i = 1 To LCol .Range(.Cells(1, i), .Cells(.Rows.Count, i).End(xlUp)).Copy Sheets("Sheet2").Cells(Rows.Count, j).End(xlUp).Offset(1, 0) _ .PasteSpecial xlPasteValues j = j + 2 Next End With End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy columns and paste with blank column between
A couple of things you should get into the habit of doing...
1. When copying/pasting values, you can assign directly to the target range like so... rngTarget.Value = rngSource.Value ...where the ranges are identical in size. 2. When your source/target ranges vary, select the source range and prompt the user for the target range... rngTarget = Application.InputBox("Choose the target range", Type:=8) rngTarget.Value = Selection.Value Otherwise, you'll need some other mechanism to get refs for the source/target ranges. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy columns and paste with blank column between
Hi Howard,
combined with Garrys suggestion you can try: Sub CopyCols() Dim LCol As Integer Dim LRow As Long Dim varOut As Variant Dim i As Integer Dim j As Integer With Sheets("Sheet1") LCol = .Cells(1, .Columns.Count).End(xlToLeft).Column j = 1 For i = 1 To LCol LRow = .Cells(.Rows.Count, i).End(xlUp).Row varOut = .Range(.Cells(1, i), .Cells(LRow, i)) Sheets("Sheet2").Cells(Rows.Count, j).End(xlUp) _ .Offset(1, 0).Resize(rowsize:=LRow) = varOut j = j + 2 Next End With End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy columns and paste with blank column between
combined with Garrys suggestion you can try: Sub CopyCols() Dim LCol As Integer Dim LRow As Long Dim varOut As Variant Dim i As Integer Dim j As Integer With Sheets("Sheet1") LCol = .Cells(1, .Columns.Count).End(xlToLeft).Column j = 1 For i = 1 To LCol LRow = .Cells(.Rows.Count, i).End(xlUp).Row varOut = .Range(.Cells(1, i), .Cells(LRow, i)) Sheets("Sheet2").Cells(Rows.Count, j).End(xlUp) _ .Offset(1, 0).Resize(rowsize:=LRow) = varOut j = j + 2 Next End With End Sub Regards Claus B. Well, at least my thought process was heading in the right direction now that I see these workable samples. I knew I would need a variable to determine the number of columns on sheet 1 which I can do well enough and a method to advance the copied columns to sheet 2by two. I didn't know what that was going to look like. Not so bad seeing it properly written, and I can read it pretty well. Composing is another thing. And I'm getting more adept at using this method as you suggest, Garry. Although here you are using .Value on target and source. Don't you set both target and source to ranges? Probably confusing what you are trying to convey. rngTarget.Value = rngSource.Value I'm missing your point on the InputBox suggestion for <When your source/target ranges vary When you prompt for a destination range, isn't the "uppermost left cell" really all you need, ranges same or different? Thanks to both of you for the info. Howard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy columns and paste with blank column between
Well, at least my thought process was heading in the right direction
now that I see these workable samples. I knew I would need a variable to determine the number of columns on sheet 1 which I can do well enough and a method to advance the copied columns to sheet 2by two. I didn't know what that was going to look like. Not so bad seeing it properly written, and I can read it pretty well. Composing is another thing. And I'm getting more adept at using this method as you suggest, Garry. Although here you are using .Value on target and source. Don't you set both target and source to ranges? Probably confusing what you are trying to convey. This assumes the variables have been defined/set prior to usage. Given the amount of programming you do.., I didn't think this needed explaining! rngTarget.Value = rngSource.Value I'm missing your point on the InputBox suggestion for <When your source/target ranges vary When you prompt for a destination range, isn't the "uppermost left cell" really all you need, ranges same or different? True for a copy/paste! It's required that the target range is resized to match the source range when directly assigning values in this fashion. Note that this is faster because there's no background calculation done for the target range size. Thanks to both of you for the info. Howard -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Multiple Columns, Paste Under a Single column | Excel Worksheet Functions | |||
Copy and Paste Formula from ActiveCell to cells in 13 columns priorto the active column | Excel Programming | |||
Need macro to check if cell is not blank & previous cell is blank, copy information from row above & paste | Excel Programming | |||
Cut & Paste into column only if column cell is blank. | Excel Programming | |||
Macro copy and paste = blank worksheet | Excel Discussion (Misc queries) |