Solved: What's wrong with my copy/paste VBA code

[genracela]

I have a code that I'm working at...

Sub CopyTo()
    Application.ScreenUpdating = True
    
    ThisWorkbook.Sheets("Total").Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(100).Value = _
    Workbooks("CN2.xls").Sheets("Sheet1").Range("J2:J6000").Value
    ThisWorkbook.Sheets("Total").Range("B9:B6000").AutoFit
    ThisWorkbook.Sheets("Total").Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(100).Value = _
    Workbooks("CN2.xls").Sheets("Sheet1").Range("E2:E6000").Value
    ThisWorkbook.Sheets("Total").Range("C9:C6000").AutoFit
        
    ThisWorkbook.Sheets("Total").Range("D" & Rows.Count).End(xlUp).Offset(1).Resize(100).Value = _
    Workbooks("CN2.xls").Sheets("Sheet1").Range("A2:A6000").Value
    ThisWorkbook.Sheets("Total").Columns("D").AutoFit
    
    ThisWorkbook.Sheets("Total").Range("E" & Rows.Count).End(xlUp).Offset(1).Resize(100).Value = _
    Workbooks("CN2.xls").Sheets("Sheet1").Range("C2:C6000").Value
    ThisWorkbook.Sheets("Total").Columns("E").AutoFit
    
    ThisWorkbook.Sheets("Total").Range("F" & Rows.Count).End(xlUp).Offset(1).Resize(100).Value = _
    Workbooks("CN2.xls").Sheets("Sheet1").Range("G2:G6000").Value
    ThisWorkbook.Sheets("Total").Columns("F").AutoFit
        
    ThisWorkbook.Sheets("Total").Range("G" & Rows.Count).End(xlUp).Offset(1).Resize(100).Value = _
    Workbooks("CN2.xls").Sheets("Sheet1").Range("F2:F6000").Value
    ThisWorkbook.Sheets("Total").Columns("G").AutoFit
        
    ThisWorkbook.Sheets("Total").Range("H" & Rows.Count).End(xlUp).Offset(1).Resize(100).Value = _
    Workbooks("CN2.xls").Sheets("Sheet1").Range("H2:H6000").Value
    ThisWorkbook.Sheets("Total").Columns("H").AutoFit
          
    ThisWorkbook.Sheets("Total").Range("J" & Rows.Count).End(xlUp).Offset(1).Resize(100).Value = _
    Workbooks("CN2.xls").Sheets("Sheet1").Range("K2:K6000").Value
    ThisWorkbook.Sheets("Total").Columns("J").AutoFit
    
    ThisWorkbook.Sheets("Total").Range("P" & Rows.Count).End(xlUp).Offset(1).Resize(100).Value = _
    Workbooks("CN2.xls").Sheets("Sheet1").Range("I2:I6000").Value
    ThisWorkbook.Sheets("Total").Columns("P").AutoFit
    
    ThisWorkbook.Sheets("Total").Range("T" & Rows.Count).End(xlUp).Offset(1).Resize(100).Value = _
    Workbooks("CN2.xls").Sheets("Sheet1").Range("L2:L6000").Value
    ThisWorkbook.Sheets("Total").Columns("T").AutoFit
       
    Application.ScreenUpdating = True
         
End Sub

It's suppose to copy columns from CN1.xls file to the sample.xls workbook.
The code is supposed to start pasting at the blank rows of each column(the blank row after the last data on the column).

But it didn't work. Can you help me figure out what's wrong?

[p45cal]

Try the likes of:
[VBA]ThisWorkbook.Sheets("Total").Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(5999).Value = Workbooks("CN2.xls").Sheets("Sheet1").Range("J2:J6000").Value
[/VBA]
It would be helpful if you said where it went wrong and the error message.

[mdmackillop]

You need to match your range sizes. When repeating the same procedure many times, it's better to pass data to another sub.
[vba]
Option Explicit

Dim wsTot As Worksheet
Dim wsCN2 As Worksheet

Sub CopyTo()
Application.ScreenUpdating = True
Set wsTot = ThisWorkbook.Sheets("Total")
Set wsCN2 = Workbooks("CN2.xls").Sheets("Sheet1")

Call DoStuff("B", "J")
Call DoStuff("C", "E")
Call DoStuff("D", "A")

Application.ScreenUpdating = True
End Sub

Sub DoStuff(x As String, y As String)
Dim Rng As Range
Set Rng = wsTot.Range(x & Rows.Count).End(xlUp).Offset(1).Resize(100)
Rng.Value = wsCN2.Range(y & "2").Resize(100).Value
wsTot.Columns(x).AutoFit
End Sub

[/vba]