I echo the request for consistency.
Am I right in saying that your worksheets are named:
"NEC", "SUN", "ORACLE", and "AVAYA"
all of these as upper case.
Each worksheet contains contact details for the company.
The contact details appear twice on each worksheet, both in column A
The first starts with "Template for ML" in cell A1
and the second starts with "Template for MINC" in cell A6
You then say that cells A1 to A5 contain these items:
A1: Template for ML
A2: mfr name
A4: supplier name
A5: ph no
but you then add "& so on..."
This does not make sense as you have told us that the next cell (cell A6), contains "Template for MINC"
From what you have said ph nec (you used lower case) needs to select and copy the data from a worksheet named "NEC" and the 4 cells A2, A3, A4 & A5
For pb nec it needs to select and copy the data from a worksheet named "NEC" and the 4 cells A7, A8, A9 & A10
The selected data is then copied into a worksheet named "Sheet1" and placed somewhere in column D
Four questions arise from this:
1. Is the data pasted into column D alone.
You showed us that the data on Sheet1 was like this:
A B C
ph nec tlk_21
pb sun mng_09
ph oracle odf_67
pb avaya ipg_65
As you didn't include row numbers, it appears that this data is in rows 1 to 4.
In which case does the copied data get concatenated into one cell in column D, alongside the appropriate 'ph/pb' & 'company name'
or is it placed in the 4 cells in columns D, E, F & G
2. Where does the source data start and is it in sequential rows. Is 'ph' in cell A1 and 'nec' in cell B1 and are 'pb' & 'sun' in cells A2 and B2
or is it that cells A1, B1 and C1 can be changed to show ph or pb in A1, a company name in B1 and some other data (not relevant to this proposed macro) in C1.
3. Are the address worksheets always named as the exact, but upper case version of the text in column B on Sheet1.
4. Is Sheet1 actually named "Sheet1"