• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Macro - Copy and paste data match by column headings

Hwee Kian

New Member
Hi Guys,

For the below code, if let's say i wanted to only copy and paste data matched by column headings, how can that be done?

Could someone please help? Many many thanks.

Code:
Option Explicit

Sub ConsolidateDta()
Dim i As Integer
Dim fil As String
Dim Col As String
Dim cpy As String
Dim ws As Worksheet
Dim twb As Workbook

Set ws = Sheet1 ' List sheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set twb = ThisWorkbook
On Error GoTo Err

  For i = 2 To ws.Range("B65536").End(xlUp).Row 'Sheet1 is MasterSheet
  fil = ws.Range("C" & i) & ws.Range("B" & i) 'File Location plus XL name
  cpy = ws.Range("D" & i) & ":" & ws.Range("E" & i) 'Copy Range
  Col = Left(ws.Range("B" & i), 1) 'Col to paste to
  Workbooks.Open fil, 0, 1 'Open Read Only
  Range(cpy).Copy
  twb.Sheets(ws.Range("F" & i).Value).Cells(Rows.Count, Col).End(xlUp)(2).PasteSpecial 12 'Vals only
  ActiveWorkbook.Close False 'Close no save
  Next i
Application.DisplayAlerts = False
Application.ScreenUpdating = True
Exit Sub
Err:
MsgBox "The file " & ws.Range("b" & i) & " is missing. Operation incomplete."
End Sub
 
Hwee
Can you please post the associated file and explain what your trying to do to simplify our work?
 
Hwee
Can you please post the associated file and explain what your trying to do to simplify our work?

Hi Hui,
Please see my attachment.

What i'm trying to do is to copy all data under the specific column headings listed in MasterData sheet from the source file where the column headings match.
 

Attachments

  • VBAmacroCpyDataFiles.xlsm
    23.5 KB · Views: 111
Can you attach:
Addendum B - INFORMATION SHEET 288 2015-02-26.xls
 
see how this goes

I haven't implemented the "Data Range Start Cell" as I didn't know if it refereed to the Source or Destination file

I have tested it with 2 files with different column layouts and it works well
 

Attachments

  • VBAmacroCpyDataFiles.xlsm
    25.1 KB · Views: 186
Hi Hui,

Many many thanks, you're a star.

The data range start cell refers to the source file.

When i run the file, if say the source file did not have "Currency" heading available, and there is no match against the same heading in the Destination file, why is the data under "Currency" column in Destination file not blank?

Thanks.

BR,
HK
 
I don't know
It ran fine for me ?

I changed the Path on the List sheet in Column B, maybe check that is correct on your system

Here's what I get
upload_2015-2-27_14-38-26.png
 
Last edited:
Also why do you need a Start Cell ?
I determine which is the next cell automatically
 
Also why do you need a Start Cell ?
I determine which is the next cell automatically

Hi Hui,

I actually require the start cell because the source file data does not necessary start at A1. Could you help me please?

In addition, I wanted to specify "Copy to Sheet" to be available to be copied to new excel worksheet tab. Would this be possible also?
 
Both implemented

You will need to be careful as it is possible to overwrite the first set of data with subsequent data if that is what the ranges in the List sheet allow
 

Attachments

  • VBAmacroCpyDataFiles.xlsm
    27.8 KB · Views: 71
Hi Hui,

Thanks, but not sure why i'm running the file with errors.

I just want to understand for the "Copy to Sheet" column if i enter a new name say called "ABC" in the List sheet, when the data is copied to new worksheet "ABC" could i have the first row of headings to be always listing these 5 columns?

Country |Network |Price |Currency |Comments|

So the macro will copy data from the source file to the destination file as long as the column headings match.

BR,
HK
 
See how this goes:

Remember the names of Files and Worksheets are one I made up
You may not have the files named the same

Also I have an error is like telling the Dr "I am sore"

How about telling me what the error is and where it is
Maybe a screen shot ?
 

Attachments

  • VBAmacroCpyDataFiles2.xlsm
    28.9 KB · Views: 25
Hi Hui,

It says "Object or Object Variable not found"

A few questions to check with you please:
1. The "Data Range Start Cell" is indicating the cell of the source file where the data is copied, not the cell of the destination file where data will be copied to. For eg, when i enter cell A4, i see the below column heading appearing in the new worksheet starting from cell A4.
Country Network Price Currency Comments

2. I have attached a file called aaa.xls.
In this example, i would like the data to be copied over from aaa.xls to a new worksheet called "John".
So the "Data Range Start Cell" for this example should be B3, because the macro will compare the column headings and copy the relevant data over. In other words, there will be relevant data copied over for columns "Country", "Network" and "Price" since they match the 3 out of the 5 column headings.
I believe it might be useful that the MasterData sheet be used as the default for column headings comparison against the source file.
Then the result in the "John" sheet would reflect in A1 with the 5 column headings, but there would be no data under columns "Currency" and "Comments".

Many thanks again for your help, I really appreciate it.
 
A few things

1. It runs fine on my system Excel 2010 & 2013 on Win 7
2. It says "Object or Object Variable not found" where?
3. There is no file attached?
 
Put these 3 files somewhere
and read the next post
 

Attachments

  • Addendum B - INFORMATION SHEET 288 2015-02-27.xls
    33 KB · Views: 52
  • Addendum B - INFORMATION SHEET 288 2015-02-26.xls
    36 KB · Views: 50
  • Addendum B - INFORMATION SHEET 288 2015-02-28.xls
    33.5 KB · Views: 47
Put this somewhere
Change the directory in this file to point to where the 3 files are
Run the macro
 

Attachments

  • VBAmacroCpyDataFiles3.xlsm
    29.2 KB · Views: 72
Hi Hui,

Thanks for your help. It works now!

One last thing - as it seem the vb code only allows the source file data to be copied without any errors if the source file column headings start from A1.

j = Range("A1:F1").Find(c).Column

When i have a source file (see attachment) where the column headings start from somewhere in the middle, the error will appear "Runtime error '91' Object Variable or With block variable not set" and when i click debug the above code row is highlighted.

So could you please help because not all source files have column headings starting from A1?
 

Attachments

  • CPL for SPSPTL-S-SPK 20150226.xls
    173 KB · Views: 37
HK
Yes, I will assist you
But at a time that is socially acceptable to me

If you want to pay me $225/Hr as a Sub-contractor (Yes that is what I charge) you can have my time at your request. Send me a PM if this is the case.

Otherwise I'd suggest you read the Site Rules at:
http://chandoo.org/forum/threads/new-users-please-read.294/
or should I not entertain my guests at my house on a Sunday night to solve your problem?

You will see at the above link that I have NO Obligation to assist you at all
So I would suggest loosing your immediacy.

FFS Its Sunday Night!
 
Last edited:
Please see attached file:

Hi Hui,

The code used for this question is really good & has helped me a lot. thank you.


i have a question if you dont mind.

can the code be changed to send the data to the file on the list Tab rather than copying the data from that file ?

regards

Kelvin
 
HK
Yes, I will assist you
But at a time that is socially acceptable to me

If you want to pay me $225/Hr as a Sub-contractor (Yes that is what I charge) you can have my time at your request. Send me a PM if this is the case.

Otherwise I'd suggest you read the Site Rules at:
http://chandoo.org/forum/threads/new-users-please-read.294/
or should I not entertain my guests at my house on a Sunday night to solve your problem?

You will see at the above link that I have NO Obligation to assist you at all
So I would suggest loosing your immediacy.

FFS Its Sunday Night!
hahah! I'm at work and was reading this thinking the exact same thing as I moved down the page. Nice work, though.
 
Back
Top