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"
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.