MS Excel macro to copy only specific columns

Azrin

New Member
Joined
Mar 5, 2012
Messages
16
Hello everyone. :)

I need your help in building a macro that would allows me to copy specific colums from one workbook to another workbook.

The first worksheet consists of Column A until Column DJ. A lots, rite. :p

Of all of these columns, I just want to copy Column C, D, & AD until DH.

The columns will be copied to a new worksheet in a different workbook.

Hence, is there any macro that able to help me accomplish this?

Thanks. :)
 

Azrin

New Member
Joined
Mar 5, 2012
Messages
16
Plus, I only want to copy the column begining from cells C6 until the last row of data in that column.
This also applied to the rest of the columns.
 

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
Does this help?

Code:
Sub azrin()

Dim lr As Long

FirstWorkbook.Activate

lr = Cells(Rows.Count, 3).End(xlUp).Row

Range("C6:D" & lr).Copy Anotherworkbook.Sheets("Sheet1").Range("C6")
Range("AD6:DH" & lr).Copy Anotherworkbook.Sheets("Sheet1").Range("AD6")


End Sub
 

Azrin

New Member
Joined
Mar 5, 2012
Messages
16
Hi John. :)

Thank you so much for the solution.

Yes, it works great. All I need to change is put the workbook name inside Workbooks(firstworkbook). Activate

One more request if you don't mind, I forgot to mention that the first 2 columns (C & D) contain VLookup formulae. :p

Hence, everytime I run the macro to copy them, #REF! error appeared, can you show how to copy only cell values from the macro that you have provided?

Once again, thank you. :)
 
Last edited:

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
You're welcome. Glad to hear we are on the right track:

Maybe:

Code:
Sub azrin()

Dim lr As Long
Dim x As Long

Workbooks(FirstWorkbook).Activate

lr = Cells(Rows.Count, 3).End(xlUp).Row

x = Range("C6:C" & lr).Rows.Count

Anotherworkbook.Sheets("Sheet1").Range("C6").Resize(x, 2).Value = Range("C6:D" & lr).Value
Range("AD6:DH" & lr).Copy Anotherworkbook.Sheets("Sheet1").Range("AD6")


End Sub
 

Azrin

New Member
Joined
Mar 5, 2012
Messages
16
It Works!!! Thank you so much, John!

Hi John. :)

Thank you so much for the solution. :)
The code works wonder.

You are my hero and saviour! :biggrin:

Hope you have lots of great days and to other Excel's user who facing similar problem like me, I do hope that the solution provided by John, help you as well.

:)
 

Forum statistics

Threads
1,082,269
Messages
5,364,148
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top