Macro

cool

New Member
Joined
Sep 24, 2009
Messages
19
Hello,
I have a query in macro.
I have 2 excel sheet. In 1st sheet i have list of rows. For eg: 20 rows (may vary)(5 columns).
In 2nd sheet 1-3 names.
Now, If in 2nd sheet, i have one name, then in 1st sheet at the end column (6th column) , i want that names to be displayed.
If in 2nd sheet, there are 2 names, then in 1st sheet, i want 1st row as 1st name, 2nd row as 2nd name, 3rd row as 1st name, 4th row as 2nd name and till last row.
If in 2nd sheet, there are 3 names, then in 1st sheet, i want 1st row as 1st name, 2nd row as 2nd name, 3rd row as 3rd name, 4th row as 1st name and so on.
Is this possible in macro????
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
what is the configuration of second sheet . how are the names entered and in which column . give an example of three names
 

gbrandreth

Board Regular
Joined
Nov 26, 2009
Messages
51
Use a double loop.

Set 2 variables to count the number of names on sheet 2 (loop1), and to work out where you last row is on sheet 1 (loop2).

For Y = 1 to Loop2 step Loop1
For X = 1 to Loop1


Use Loop1 to get the value of the name off sheet 2 based on value of Loop1
Then put that value in Col 6
The step bit makes sure you know when you get to the end, you may get an odd entry at end that you have to delete if you dont have an exactly divisible quantity, but its no big deal. you can make it more sophisticated to avoid this, for example checking there is a value on sheet 1, and if blank exit, but I wouldn't bother.

Next X
Next Y

This will go down the sheet and put 1st name all way down if only 1
and will copy in blocks of 2/3 with each of name if theres more than 1


e.g.

Sheet 1 =

A
B
C
D
E
F

Sheet 2 =
Fred
Ralph
Dave


Result would be
A Fred
B Ralph
C Dave
D Fred
E Ralph
F Dave

From you description, I presume this is what your after, and it will work regardless of how many names are on sheet 2, just looping round sequentially.

As the previous poster says, without a better idea of how your sheets are layed out, I can't give you the full code, but its fairly basic stuff if you've written macros before.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,484
Messages
5,596,407
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top