Solved: Excel Macro - Cut/Paste Data via Translation Table

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

UMCaneSimon

Thread Starter
Joined
Oct 16, 2009
Messages
18
Hi everyone,

Let me just start by saying this forum is awesome. Nothing like getting a new perspective to solving a problem!

Here's the scenario:
I have 3 sheets: Input Sheet, Output Sheet, Translation Table

Both the Input Sheet and Output Sheet has columns of data with a header row in row 1. I need to move entire columns of data from the Input Sheet to the Output Sheet based on the header row.

This is where the Translation Table comes into play. I need to move the columns of data based on what is defined in the Translation Table.

For example, all data in column "Column A" of the Input Sheet should be moved to column "Albert Column" of the Output Sheet.

A sample file is attached to better illustrate.

Thanks in advance for the help!

Simon
 

Attachments

Joined
Jul 1, 2005
Messages
8,546
Not 100% clear on your problem, so make sure your work is "safe" before you try this:

Sub test()
'input sheet must be active
For Each Cell In Sheets("Input Sheet").Range("A1:E1")
x = Application.Match(Cell, Sheets("Translation Table").Columns(1), 0)
y = Application.Index(Sheets("Translation Table").Columns(2), x)
z = Application.Match(y, Sheets("Output Sheet").Rows(1), 0)
Sheets("Input Sheet").Columns(Cell.Column).Copy Sheets("Output Sheet").Cells(1, z)
Sheets("Output Sheet").Cells(1, z) = y
Next Cell
End Sub


(tested with all data removed from Output except the header row)
 

UMCaneSimon

Thread Starter
Joined
Oct 16, 2009
Messages
18
Thanks for the help! The code works but I ran into an error. There are situations where the data in the Input Sheet might not have a corresponding field in the Translation Table.

This causes an exception b/c the variable "Z" cannot be defined. Is there a "iserror()" or "isnull" code that I could use?

Thanks!
 
Joined
Jul 1, 2005
Messages
8,546
Do you mean where the header (i.e. row 1 value) on Input has no corresponding "Output field" on Translation?

I've never used the expression IsEmpty before, but it seems to do the trick (for y).

Sub test()
'input sheet must be active
For Each Cell In Sheets("Input Sheet").Range("A1:E1")
x = Application.Match(Cell, Sheets("Translation Table").Columns(1), 0)
y = Application.Index(Sheets("Translation Table").Columns(2), x)
z = Application.Match(y, Sheets("Output Sheet").Rows(1), 0)
If IsEmpty(y) Then
Else
Sheets("Input Sheet").Columns(Cell.Column).Copy Sheets("Output Sheet").Cells(1, z)
Sheets("Output Sheet").Cells(1, z) = y
End If
Next Cell
End Sub
 

UMCaneSimon

Thread Starter
Joined
Oct 16, 2009
Messages
18
Thanks it does seem to work but I will have to play around with this code. Thanks again for the help.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top