Copy columns from one excel sheet to another with different formatting

0
votes

I have 5 sheets in my Excel file. Out of these 5, one is the master sheet which has all the data I want to copy to the other 4 sheets. All the sheets have one common column, ID. The master has more than 10000 IDs.

What I want to do is:

If ID in sheet1 = 24356 = ID in master sheet then copy x, y, z columns from master sheet to sheet1. This is same for all other sheets.

Also, since the master sheet is from other source than the rest of the sheets, its formatting is different. Is there a way to remove all formatting in sheets before running copy/paste?

Can anyone please tell me the VBA code to do this.

This is how my Master sheet looks like: enter image description here I want the other sheet (eg sheet1) in this case to look like: enter image description here

Is it possible to do this without entering a lookup formula in every cell?

1
excelvba

1 Answers

0
votes

If I'm understanding correctly, I don't think you actually need to use a macro for this. Let's say that your MasterSheet is something like:

enter image description here

and your various sheets have the IDs in column A:

enter image description here

You could put this in B1:

=IF(ISNA(VLOOKUP($A1,MasterSheet!$A:$D,2,FALSE)),"",VLOOKUP($A1,MasterSheet!$A:$D,2,FALSE))

then simply increment the VLOOKUP's return column for each other column:

=IF(ISNA(VLOOKUP($A1,MasterSheet!$A:$D,**3**,FALSE)),"",VLOOKUP($A1,MasterSheet!$A:$D,**3**,FALSE))

Result:

enter image description here

You should probably use a better/quicker formula for the lookup to see if the ID exists (like Match)...

Hope this helps.

Cheers,

iso