Copying and pasting ranges from one workbook to another

[DF10]

Hello,
this is driving me crazy and I really need some help.
It must be some basic stuff I just cannot grasp. Anyway:


I am using Excel 2010 on Windows 7
Typical situation of copying and pasting from one workbook to another.
I do a similar procedure in another bit of the macro and it works perfectly.
At this point, however it looks like it's copying but not pasting.
When I hover with the mouse I see that it gives me an Application-defined or object defined error (?!?)
Specifically I copy from

Workbooks(datafilename2).Activate
Ws2.Select
LastCol = Ws2.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
LastRow = Ws2.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Ws2.Range(Ws2.Cells(1, 1), Ws2.Cells(LastRow, LastCol)).Copy


' and paste here
    Workbooks(datafilename1).Activate
    Ws1.Select
    Ws1.Range(Ws1.Cells(1, col + 1), Ws1.Cells(LastRow, col + 1 + LastCol)).PasteSpecial xlPasteValues 'Application-defined or object defined error on the second .Cells

When I hover with the mouse I get (in the specific case)
LastRow=435 as it should be
LastRow=408 as it should be
col = 48 as it should be


BUT when I hover over Ws1.Cells(LastRow, col + 1 + LastCol) I get a Application-defined or object defined error


I realised it has something to do with the number of columns as the error appears if the value of column exceeds 256.
If for example I set LastCol = 100 then it works fine.

Why is it? Am I missing something?
Why does it work then when I copy and paste the range on the same workbook?

Workbooks(datafilename2).Activate
Ws2.Select
LastCol = Ws2.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
LastRow = Ws2.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Ws2.Range(Ws2.Cells(1, 1), Ws2.Cells(LastRow, LastCol)).Copy
Ws2.Range(Ws2.Cells(1, LastCol + 1), Ws2.Cells(LastRow, LastCol + 1 + LastCol)).PasteSpecial xlPasteValues 'works fine

Any help would be much appreciated
Thanks

 

[lancerj017]

Try this.

Workbooks(datafilename2).Activate 
Ws2.Select 
LastCol = Ws2.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column 
LastRow = Ws2.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 
Ws2.Range(Ws2.Cells(1, 1), Ws2.Cells(LastRow, LastCol)).Copy workbooks(datafilename1).ws1.cells(1,col + 1)

 

[DF10]

Thanks for replying. Does not work, though.
Not that I get a runtime error.
It just does not paste.

 

[lancerj017]

can you provide the whole code that you have?

 

[DF10]

here we go, I hope this helps clarifying.

Sub Merge_Screener_CLT()

Dim WB As Workbook
Dim fd As FileDialog
Dim thisfilepath As String
Dim objfl As Variant
Dim Ws1     As Worksheet
Dim Ws2     As Worksheet
Dim LastCol As Long
Dim LastRow As Long
Dim LastRowSup As Long
Dim col As Long
Dim r As Long
Dim c As Long
Dim MyString As String
Dim supportbookname As Variant
Dim datafilename As Variant
Dim sheetname As Variant
Dim Screenername As Variant
Dim intInteger As Integer
Dim strString As String
Dim IDMatch As Long
Dim LookupWorkbook As String
Dim LookupRange As Range
Dim count_insert As Long




Screenername = ActiveWorkbook.Name




'************GET CLT FILE *********
'supportbookname = ThisWorkbook.Name
MsgBox "Open the  data file"
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
    .ButtonName = "Select"
    .AllowMultiSelect = False
    .Title = "Choose Transactions file to import"
    .Filters.Add "Excel", "*.xls; *.xlsx; *.xlsm", 1
    .InitialView = msoFileDialogViewDetails
    .Show
    For Each objfl In .SelectedItems
        thisfilepath = objfl
    Next objfl
    On Error GoTo 0
End With
Set fd = Nothing


Set WB = Workbooks.Open(thisfilepath)
WB.Activate
datafilename = ActiveWorkbook.Name
sheetname = ActiveSheet.Name
Application.ScreenUpdating = False




'**********SORT BY Reference Code


' get the sample size
    Set Ws2 = Sheets(sheetname) 'or generally Ws2 = ActiveSheet
    Ws2.Select
    LastCol = Ws2.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    LastRow = Ws2.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Ws2.Range(Cells(2, 1), Cells(LastRow, LastCol)).Select
    
 'by Reference Code
col = Application.WorksheetFunction.Match("Reference Code", Ws2.Range(Ws2.Cells(1, 1), Ws2.Cells(1, LastCol)), 0)
Selection.Sort Key1:=Cells(2, col), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortTextAsNumbers


  
   Workbooks(Screenername).Activate
    
    Set Ws1 = Sheets("Raw Values") 'Ws1 = ActiveSheet
    Ws1.Select
    col = Ws1.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
' This is the original bit that gives me the error    
'Workbooks(datafilename).Activate
'Ws2.Select
'Copy CLT data
'LastCol = Ws2.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
'LastRow = Ws2.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'Ws2.Range(Ws2.Cells(1, 1), Ws2.Cells(LastRow, LastCol)).Copy
'Workbooks(Screenername).Activate
'Ws1.Select
'Ws1.Range(Ws1.Cells(1, col + 1), Ws1.Cells(LastRow, col + 1 + LastCol)).PasteSpecial xlPasteValues
' Ws1.Cells(LastRow, col + 1 + LastCol) gives me an Application-defined or object defined error if the number of columns exceeds 256


'this is your suggestion
Workbooks(datafilename).Activate
Ws2.Select
LastCol = Ws2.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
LastRow = Ws2.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Ws2.Range(Ws2.Cells(1, 1), Ws2.Cells(LastRow, LastCol)).Copy Workbooks(Screenername).Ws1.Cells(1, col + 1)




Application.ScreenUpdating = True
Ws1.Cells(1, 1).Select


End Sub