Need help to copy the UsedRange

[Jagdev]

Hi Experts

I have a below code which is copying the entire column and pasting it to a different sheet.

[CODE][.Columns(t.Column).EntireColumn.Copy _
Destination:=Sheets("PL Listing").Cells(5, pasteCol)/CODE]

Please help me with the code which will copy the Used Range instead of entire column in the above code.

Regards,
JD

[Jagdev]

Hi

I am trying something like


.Columns(t.Column).EntireColumn.UsedRange.Copy _
Destination:=Sheets("PL Listing").Cells(5, pasteCol)

I am getting error 438 - Object doesn't support this property or method.

Regards,
JD

[mancubus]

Parent to UsedRange is worksheet.

try like:


ActiveSheet.UsedRange


Sheet1.UsedRange


Worksheets("MySheet").UsedRange

[Jagdev]

Hi Mancubus,

I tried all the above option but still getting the same error. What I am trying to do here is copying specific column with its header from one sheet to another and below is the code.

The old post is copying the entire column from Data sheet and paste it to the other sheet. I want only the range to be copied from the Data sheet and paste to the other sheet.


Sub Section1()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim LastRow As Long
Dim iCol As Long

Sheets("Data").Activate
iCol = 1
[A1].CurrentRegion.AutoFilter iCol, "PL"
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Dim SearchCols(15) As String
SearchCols(0) = "Claim Reference"
SearchCols(1) = "Policy Year"
SearchCols(2) = "INC.DTE"
SearchCols(3) = "INSURER REF"
SearchCols(4) = "CLIENT"
SearchCols(5) = "CLAIMANT"
SearchCols(6) = "Cause"
SearchCols(7) = "TYPE/CIRCUMSTANCES"
SearchCols(8) = "NATURE OF INJURY"
SearchCols(9) = "Outstanding"
SearchCols(10) = "PAID"
SearchCols(11) = "TOTAL"
SearchCols(12) = "Status"
SearchCols(13) = "CLAIM / INCIDENT"
SearchCols(14) = "COMMENTARY"
'continue with all the column names
Dim i As Integer
'Find "Entity" in Row 1
Sheets("PL Listing").Activate
Rows("5:" & Rows.Count).ClearContents
With Sheets("Data").Rows(1)
For i = LBound(SearchCols) To UBound(SearchCols)
Set t = .Find(SearchCols(i), LookAt:=xlPart)
'If found, copy the column to Sheet 2, Column A
'If not found, present a message

If Not t Is Nothing Then
If Sheets("PL Listing").Range("B5").Value = "" Then
pasteCol = 2
Else
pasteCol = Sheets("PL Listing").Cells(5, .Columns.Count).End(xlToLeft).Offset(0, 1).Column
End If
' With Worksheets("Data")
' Range(.Cells(2, t.Column), .Cells(LastRow, t.Column)).SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("PL Listing").Cells(5, pasteCol)
'End With

.Columns(t.Column).EntireColumn.Sheet13.UsedRange.Copy _
Destination:=Sheets("PL Listing").Cells(5, pasteCol)

Else
MsgBox SearchCols(i) & " Not Found"
End If
Next
End With
Application.CutCopyMode = False

[mancubus]

obviously you did not try what i suggested.

can you see anything like this in my post?

.Columns(t.Column).EntireColumn.Sheet13.UsedRange.Copy _
Destination:=Sheets("PL Listing").Cells(5, pasteCol)


i'm not sure UsedRange is what you are looking for.

usedrange is:
http://windowssecrets.com/forums/showthread.php/93125-Insight-into-UsedRange-(Excel-2003)

[Jagdev]

Hi Mancubus

Thanks for the link. I somehow managed to solve my query.

Regards,
JD