Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!
  • 0
Dougie79

VBA To Copy Columns Between Workbooks Based on Column Headings

Question

Hi Guys

Nice site Caleeco :)

I am looking for a VBA to copy the values from one workbook to another based on the column headings, sample data attached.

The "Conversion_Sheet" has the data that I want to copy to the "Import_Sheet".

The "Conversion_Sheet" has to contain the VBA code as the "Import_Sheet" has to be saved as xls to be imported.

The column headings are constantly changing and there could be anything between 2 columns to 200.  Additionally there could be 1000s of rows.

Any help as always will be a great help.

Thanks

Dougie

Conversion_Sheet.xlsm

Import_Sheet.xlsx

  • Like 1

Share this post


Link to post
Share on other sites

8 answers to this question

Recommended Posts

  • 0

Hi Dougie, 

Aha, thats a slightly more difficult problem! I have done some testing on my end and written the code below. Let me know if it works for you:

Code:

Sub ExportData()
'Source: www.ExcelWTF.com
'Purpose: To transfer selective data between workbooks

'---- Variables
Dim wbC As Workbook, wbI As Workbook
Dim wsC As Worksheet, wsI As Worksheet
Dim r As Range, RNG As Range, c As Range
Dim lr As Long, lc As Long
'---- Code
'Define the workbooks & Sheets being worked with
Set wbC = Workbooks("Conversion_Sheet")
Set wsC = wbC.Sheets("Sheet1")

Set wbI = Workbooks("Import_Sheet")
Set wsI = wbI.Sheets("Sheet1")

' Find the last row on the Import_Sheet and clear data
wbI.Activate
lr = wsI.Range("A" & Rows.Count).End(xlUp).Row
If lr > 1 Then Rows("2:" & lr).EntireRow.Delete

'Set the column headers from Import_Sheet to Lookup
lc = wsI.Cells(1, Columns.Count).End(xlToLeft).Column
Set RNG = wsI.Range(Cells(1, 1), Cells(1, lc))

'Find Last used row on Conversion_Sheet
lr = wsC.Range("A" & Rows.Count).End(xlUp).Row

'Loop the headers and copy data accordingly
Application.ScreenUpdating = False
On Error Resume Next
For Each r In RNG
    wbI.Activate
    Set c = wsC.Rows(1).Find(r.Value, LookIn:=xlValues)
    If Not c Is Nothing Then
        wbC.Activate
        wsC.Range(c.Offset(1, 0), Cells(lr, c.Column)).Copy
        r.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    End If
Next r
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub

Regards

Caleeco

Share this post


Link to post
Share on other sites
  • 0

Hi Dougie!

Welcome to the forum, I'm glad you decided to sign up :D Thank you, I'm still making some design improvements/modification but I'm nearly there!

I will take a look at the sample sheets you have provided and get back to you with some code to try

Many Thanks

Caleeco

  • Like 1

Share this post


Link to post
Share on other sites
  • 0

Couple of questions for the method you have proposed:

  1. Does the "Import_Sheet" always have matching headers to the "Conversion Sheet"?
  2. Does the "Import Sheet" contain data that needs to be deleted before copying data to it?
  3. What are the File Paths on your machine that these two files are located?

Alternatively, 

I could code something as follows:

  1. With the "Conversions Sheet" workbook open
  2. Duplicate the sheet with Data on it to a new blank workbook
  3. Save this new workbook as "Import_Sheet.xls" in the same file location as the "Conversion_Sheet"

^^ That would mean having to delete the "Import_Sheet.xls" being created inbetween code runs, or adding a Date/Time stamp to the file name so you dont create duplicate file names. It would also remove the need to work out what data is on the sheet, as it will be a perfect copy of that 1 sheet.

Let me know what you think

Thanks

Caleeco

Share this post


Link to post
Share on other sites
  • 0

Hello Caleeco

 

1.  There are extra columns on the "Conversion Sheet" that I don't want to be copied over.  All of the columns headings on the "Import Sheet" are on the "Conversion Sheet"

2.  The "Import Sheet" may have data on it which will need to be cleared leaving the header, so from Row 2 down.

3.  The file paths on this comp are: 

C:\Users\Dougie\Desktop\Data to Upload\[Conversion_Sheet]Sheet1

C:\Users\Dougie\Desktop\Data to Upload\[Import_Sheet]Sheet1

 

Both the workbook with the Conversion Sheet and the workbook with the Import Sheet will be open when copying over the data.

 

Thanks Again Caleeco

Dougie

 

Share this post


Link to post
Share on other sites
  • 0
16 minutes ago, Dougie79 said:

Hello Caleeco

 

1.  There are extra columns on the "Conversion Sheet" that I don't want to be copied over.  All of the columns headings on the "Import Sheet" are on the "Conversion Sheet"

2.  The "Import Sheet" may have data on it which will need to be cleared leaving the header, so from Row 2 down.

3.  The file paths on this comp are: 

C:\Users\Dougie\Desktop\Data to Upload\[Conversion_Sheet]Sheet1

C:\Users\Dougie\Desktop\Data to Upload\[Import_Sheet]Sheet1

 

Both the workbook with the Conversion Sheet and the workbook with the Import Sheet will be open when copying over the data.

 

Thanks Again Caleeco

Dougie

 

Hi Dougie, 

Thanks for the additonal information. Ok, having both workbooks open when the code is being run does make things easier :) The last point of clarification I need is on the statement below:

There are extra columns on the "Conversion Sheet" that I don't want to be copied over.  All of the columns headings on the "Import Sheet" are on the "Conversion Sheet"

Is it always Columns A-H you are copying? or do we need to check the each column title on the Import_Sheet Workbook and find the corresponding column on the Converstion_sheet workbook to copy across?

Let me know

Thanks

Caleeco

 

Share this post


Link to post
Share on other sites
  • 0

^^ Assuming you always copy Columns A:H from the Conversions_Sheet workbook, give this a spin:

Code:

Sub ExportData()
'Source: www.ExcelWTF.com
'Purpose: To transfer selective data between workbooks

'---- Variables
Dim wbC As Workbook, wbI As Workbook
Dim wsC As Worksheet, wsI As Worksheet
Dim lr As Long

'---- Code
'Define the workbooks & Sheets being worked with
Set wbC = Workbooks("Conversion_Sheet")
Set wsC = wbC.Sheets("Sheet1")

Set wbI = Workbooks("Import_Sheet")
Set wsI = wbI.Sheets("Sheet1")

' Find the last row on the Import_Sheet and clear data
wbI.Activate
lr = wsI.Range("A" & Rows.Count).End(xlUp).Row
If lr > 1 Then Rows("2:" & lr).EntireRow.Delete

'Find the last row on the Conversion_Sheet and copy
wbC.Activate
lr = wsC.Range("A" & Rows.Count).End(xlUp).Row
wsC.Range("A2:H" & lr).Copy wsI.Range("A2")
Application.CutCopyMode = False

End Sub

Let me know how you get on

Caleeco

Share this post


Link to post
Share on other sites
  • 0

Hello Caleeco

 

Thanks for the quick reply.

 

There could be up to 140 different columns of data to copy from the conversion sheet to the import sheet.

 

So yes if possible the vba would need to look at the column heading on the import sheet and match it to one on the conversion sheet to copy the values or dates from that column on the conversion sheet to the import sheet.  I hope that makes sense.

 

Thanks Again

Dougie 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×