PDA

View Full Version : Solved: Select columns to copy



BonnieG
02-17-2010, 05:30 PM
I am trying to copy all active columns and paste to another worksheet. When I do this without a macro, it works, but I cannot get the same result with the macro. The following macro was recorded. When I clicked on column B and hit end, right, it chose columns B-I which are all of the active columns and inserted them on the other sheet. However, when I tried running the macro, it only copied columns B-E. HELP PLEASE!

Sheets("Sheet1").Select
Columns("B:B").Select
Range("B2").Activate
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet2").Select
Columns("B:B").Select
Range("B2").Activate
Selection.Insert Shift:=xlToRight

p45cal
02-17-2010, 06:24 PM
does
Sheets("Sheet1").Range(Range("B2"), Range("B2").End(xlToRight)).EntireColumn.Copy
Sheets("Sheet2").Select
Columns("B:B").Select
Range("B2").Activate
Selection.Insert Shift:=xlToRight
work?

BonnieG
02-17-2010, 06:34 PM
It gives me "Application-defined or object-defined error"

GTO
02-18-2010, 12:35 AM
Greetings Bonnie,

I see you just joined! Welcome to vbaexpress:hi: . You will be glad you joined, as there's some great folks here.

As to your question, if there is nothing to the right of this block of data, maybe locate the last column with data, and set our range that way.

In a junk copy (lest I goober up your wb), try:


Option Explicit

Sub exa()
Dim rngLastCol As Range

With ThisWorkbook
With .Worksheets("Sheet1")

Set rngLastCol = RangeFound(Range(.Range("B2"), .Cells(Rows.Count, Columns.Count)), , , , , xlByColumns)

If rngLastCol Is Nothing Then Exit Sub

Range(.Range("B2"), .Cells(Rows.Count, rngLastCol.Column)).Copy
End With

.Worksheets("Sheet2").Range("B2").Insert xlToRight
End With

Application.CutCopyMode = False
End Sub

Function RangeFound(SearchRange As Range, _
Optional FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range

If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange(1)
End If

Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=False)
End Function

Hope that helps,

Mark

p45cal
02-18-2010, 06:12 AM
It gives me "Application-defined or object-defined error"Ah yes, that fails if sheet 1 isn't active. Try this:
With Sheets("Sheet1")
Range(.Range("B2"), .Range("B2").End(xlToRight)).EntireColumn.Copy
End With
Sheets("Sheet2").Select
Columns("B:B").Select
Range("B2").Activate
Selection.Insert Shift:=xlToRight
Application.CutCopyMode = False

p45cal
02-18-2010, 06:15 AM
deleted (duplicate post)

p45cal
02-18-2010, 06:20 AM
deleted (duplicate post)

p45cal
02-18-2010, 06:32 AM
There are odd things going on with the posting system. Any, even shorter:

With Sheets("Sheet1")
Range(.Range("B2"), .Range("B2").End(xlToRight)).EntireColumn.Copy
Worksheets("Sheet2").Range("B1").Insert xlToRight
End With
Application.CutCopyMode = False

BonnieG
02-18-2010, 07:05 AM
Thanks Mark. It looks like this is going to work.

BonnieG
02-18-2010, 09:28 AM
Thank you all. This is wonderful. I'm so glad that I joined.