Copy specified columns from workbook1 to a specified columns in workbook2

[edlim85]

Hi all,

Everyday i need to copy specific/fixed columns in one workbook to specified column in another workbook. There must be a faster way to do this.
For example I need to copy/import data from column C7-C140 of book1 to column C of book2.

Is there a macro to do this?

Need some enlightenment here.

Thanks in advance.

 

[AlphaFrog]

Your question is too vague.

Use the macro recorder to record all the steps you do when you manually copy the data. You will then probably have to modify the recorded macro but you'll at least have the base code to work with.

If you post the code here and ask a specific question, you'll get a lot of help.

 

[edlim85]

Hi Alpha,

I know how to work with macros within a worksheet. ie a macro for formatting my reports. Can a macro use to import cells of another opened workbook to my report workbook?

 

[Sunline]

Hi , i got this here last week from Sektor , this exports data , amend as needed , change w/book names , ranges etc .

Sub TransferData()
Workbooks("Book1").Sheets("Sheet1").Range("CG2:DJ170000").Copy _
Workbooks("Book2").Sheets("Sheet1").Range("CF2")
End Sub

Ive transfered alot with this 160,000 rows at a time , took approx 30-40 seconds , hope it helps .

 

[AlphaFrog]

@Sunline

This should be faster if you only want to copy values (no formatting or formulas etc.)

Sub TransferData()
Workbooks("Book2").Sheets("Sheet1").Range("CF2:DI170000").Value = _
Workbooks("Book1").Sheets("Sheet1").Range("CG2:DJ170000").Value
End Sub

 

[Sunline]

Thanks AlphaFrog will try it out .
Cheers .

 

[edlim85]

Hi guys,


Sub TransferData() Workbooks("Book2").Sheets("Sheet1").Range("CF2:DI170000").Value = _ Workbooks("Book1").Sheets("Sheet1").Range("CG2:DJ170000").Value End Sub</pre>
Sorry for the late reply, can you explain your range value stated Range("CF2:DI170000"), When i tried it out and i got this error.

"subscript out of range"

 

[Sunline]

Hi , im still learning my self but this works .
Range("CG2:DJ170000"). This is the range that your about to export from book1 .
Range("CF2") . This cell is where the data will begin to export too in book2 .
This was my original macro .

Sub TransferData()
Workbooks("Book1").Sheets("Sheet1").Range("CG2:DJ170000").Copy _
Workbooks("Book2").Sheets("Sheet1").Range("CF2")

Goodluck .

 

[AlphaFrog]

can you explain your range value stated Range("CF2:DI170000")

When i tried it out and i got this error.

"subscript out of range"

If you have Excel 2003 or earlier, the max number of rows on any sheet is 65536. You wouldn't be able to use a range reference like DI170000. You would have to use DI65536

Worksheet size:
Excel 2003 or earlier: 65,536 rows by 256 columns (A1:IV65536)
Excel 2007 or later: 1,048,576 rows by 16,384 columns (A1:XFD1048576)

Forum Tip: Pasting VBA code in the forum editor
It would be best if you surround your VBA code with code tags e.g [CODE]your VBA code here[/CODE]
It makes reading your VBA code much easier.
When you're in the forum editor, highlight your pasted VBA code and then click on the icon with the pound or number sign #.