PDA

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



genracela
04-15-2010, 10:25 PM
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
04-16-2010, 12:27 AM
Try the likes of:
ThisWorkbook.Sheets("Total").Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(5999).Value = Workbooks("CN2.xls").Sheets("Sheet1").Range("J2:J6000").Value

It would be helpful if you said where it went wrong and the error message.

mdmackillop
04-16-2010, 05:37 AM
You need to match your range sizes. When repeating the same procedure many times, it's better to pass data to another sub.

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