Solved: Select columns to copy

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!

[VBA]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[/VBA]

does
[vba]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
[/vba]work?

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:

Code:

---

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

Quote:

---

Originally Posted by BonnieG

It gives me "Application-defined or object-defined error"

---

Ah yes, that fails if sheet 1 isn't active. Try this:
[vba]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
[/vba]

deleted (duplicate post)

deleted (duplicate post)

There are odd things going on with the posting system. Any, even shorter:

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

Thanks Mark. It looks like this is going to work.

Thank you all. This is wonderful. I'm so glad that I joined.