Copying multiple column data in multiple sheets to one column in Master sheet

[satyen]

Hello,

I have a Master sheet with header row of columns.
There are 15 other sheets in that same workbook.

For example: in Master sheet the column names are:
ID, Stock Name, Cost etc.

In Sheet2 the headings could be:
Code, Entity Name and Amount.

In sheet 3 it could be:
Identifier, Name and Price.

I would like to paste everything from Code column (Sheet 2) and Identifier column (Sheet 3 under) under ID column in Master sheet.

Can someone help?

I need to write a CASE statement please

[mdmackillop]

Hi Satyen
Welcome to VBAX
Give this a try
[VBA]
Option Explicit
Sub CopyStuff()
DoCopy "Sheet2", "Code"
DoCopy "Sheet3", "Identifier"
End Sub

Sub DoCopy(Sh As String, Header As String)
Dim Tgt As Range, TgtCol As Long, col As Long
'Find destination cell
TgtCol = Sheets("Master").Rows(1).Find("ID").Column
With Sheets("Master")
Set Tgt = .Cells(.Rows.Count, TgtCol).End(xlUp).Offset(1)
End With

'Copy data to target
With Sheets(Sh)
col = .Rows(1).Find(Header).Column
Range(.Cells(2, col), .Cells(.Rows.Count, col).End(xlUp)).Copy Tgt
End With
End Sub

[/VBA]

[satyen]

Hi Thanks for this.

I will be combining all worksheets in a folder to one Master workbook and the number of sheets and their names is not definite (will vary each time the code is run) but will be around 15.
No. of rows will vary too, between 50-100 rows.

I will need to add the other column information from the other sheets into the Master sheet according to what column they correspond to in the Master sheet. Is it worth setting up a lookup table in Excel or should I just reference everything from code?

[mdmackillop]

It's probably easier to maintain a lookup table, especially if it's subject to change. The code can be easily adapted to use such a table.
The code as written will append in the next vacant cell which is fine for "solid" data. Would this always be the case?

[satyen]

Do you mean if the if next cell down is empty it will paste there?
(im not sure yet of procedure i may want the data to be overwritten each time macro is run) lets go with overwritting data in Master for now.

Can you help me with writing code to reference a lookup table. Not very good with VBA. If possible a CASE statement. The number of column headings in Master are quite a lot around 15-20. Column heading names in Master will not change.

[mdmackillop]

On the face of it Case does not seem appropriate.
If you can post sample data with your mapping table I'll have a look.
To post, use Manage Attachments in the Go Advanced section

[mdmackillop]

BTW, Why the need for a Case statement, is this a homework assignment?

[satyen]

No, not homework, just that it will be easier to manage because of the no. of sheets. If there is another sheet I can just add another CASE. I will set up a sample and attach. Thanks for your help.

[satyen]

Here is the attachment. The column headers on other sheets will not always be in the header row. Some data in the other sheets may also be empty. Let me know if you need further explanations. Thanks.

[mdmackillop]

Can you repost your attachment with the Master sheet completed as you would expect. Could you also add notes regaring the logic of your entries. As I'm sure you can appreciate, without a logic, we can't create program rules.

[satyen]

Please see attached with Master sheet populated and some further notes.
Everytime the code is run it should overwrite any data in Master. It should go sheet by sheet looking at the columns in each sheet using the Lookup table to see what columns match and if they do then paste the information in Master sheet under the correct column. Do until last column in Master.

Let me know if you need further explanations. Sorry if it's not very clear. Thanks for your help

[satyen]

Any ideas on this?

[mdmackillop]

This uses your lookup table to some extent, but to be honest, your data is too inconsistent and disorganised for a simple solution.

[VBA]
Option Explicit
Sub CopyStuff()
Dim rng As Range, cel As Range, i As Long
With Sheets("lookup")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
For Each cel In rng
For i = 1 To 6
DoCopy cel.Text, cel.Offset(, i), .Cells(1, i + 1)
Next i
Next cel
End With
End Sub

Sub DoCopy(Sh As String, Header As String, MastHead As String)
Dim Tgt As Range, TgtCol As Long, col As Long


'Find destination cell
TgtCol = Sheets("Master").Range("1:2").Find(MastHead).Column
With Sheets("Master")
Set Tgt = .Cells(.Rows.Count, TgtCol).End(xlUp).Offset(1)
End With

'Copy data to target
With Sheets(Sh)
col = .Range("1:2").Find(Header).Column
Range(.Cells(2, col), .Cells(.Rows.Count, col).End(xlUp)).Copy Tgt
End With
End Sub

[/VBA]

[satyen]

Thanks for the code. It is coming up with errors, just trying to work them out. When I re-run the code it doesnt seem to overwrite information correctly. Also the column headers are copied into the Master. Will see if I can iron out the code. Thanks very much for your code and help.