Copy columns and paste with blank column between

  • Thread starter Thread starter Howard
  • Start date Start date
H

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.

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
 
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.
Click to expand...

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.
 
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
 
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.
 
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
Click to expand...
Regards

Claus B.
Click to expand...

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 badseeing 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
 
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.
Click to expand...

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?
Click to expand...

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
Click to expand...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top