looping through one range to fill another

[Guest]

Hi there,

I have 7 sheets.

Sheet 1 is a registration sheet, name of person goes in C7. A number is
allocated to that person (by wks function) in B7.

Sheets, 2,3,4 & 7 then need to be filled in from this information, into cols
A & B. I can do this by using source range and dest range to copy it over
after the list of names is completed, but: on all of the sheets, I have a
line of wks coding that needs to be extended down for each of the names
listed. What I need to be able to express is:

For each cell in wks 1, range C7:C67 that is not empty,(has a name in it),
on sheets 2,3 , 4 & 7 extend the line of coding down from A7:AI7 for however
many lines are needed, (this is never more than 60), then place the value of
wks 1,Cells B & C into sheets 2,3 4&7 in columns A and B. I have tried
numerous ways and not managing to get it into the right place. This is how
it should look.

Sheet 1
A B C

7 101 Jack
102 jill
103 joan
104 jim
105 jenny

Sheets 2,3 4 & 7

A B C===============================AI

7 101 Jack above line copied down to match each entry (formula)
102 jill above line copied down to match each entry (formula)
103 joan above line copied down to match each entry (formula)
104 jim above line copied down to match each entry (formula)
105 jenny above line copied down to match each entry (formula)


Sheet 5 also needs the information filling in but there are 7 rows per
person involved. So the details need to be in cols A & B row 13/20/27/34
etc.(formula range C7:AF13).
Sheet 6 as above but with 8 rows per person – 14/22/30/38 etc., (formula
range C7: AK14) Both sheets also need the wks formula extending down
This project was put together as a first effort and I am reviewing it
because it is so heavy with wks formulas and functions and each sheet at the
moment carries the full coding for up to 60 entries. It works very well and
does what it needs to do, but I want to learn how to use loops etc to make
the project lighter and it would be much more efficient if I could only carry
the one line of coding for each sheet which would extend as necessary
depending on how many names were entered on the registration (sheet 1) sheet.

My last question. At the moment, sheets 5 & 6 carry 2 macros in col C which
‘open the card,& close the card’. This leaves all the other sets of 7 or 8
rows just showing the name details and a macro to open them with.

Is there any way of applying these macros only if the rows have name details
in. Finally, is there a book that explains different ways of doing these
things that would be suitable for enthusiastic learning ? but keeping it
understandable.

Any help or advice appreciated.

Thanks

Sybs

 

[Mike Fogleman]

Way too much to ask at one time. Here is some example code to help you set a
range, loop through it, and copy it, to get you started:

Option Explicit

Sub test()
Dim LRow As Long
Dim rng As Range, c As Range
Dim rng2 As Range

'Finds the last filled row in column 3
LRow = Cells(Rows.Count, 3).End(xlUp).Row
'or LRow = Worksheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row

Set rng = Range("B7:C" & LRow)
For Each c In rng
'do something
Next

LRow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Set rng2 = Worksheets("Sheet2").Range("A2:B" & LRow)
For Each c In rng2
'do something
Next
'copy rng to next empty row in sheet2
rng.Copy Worksheets("Sheet2").Range("A" & LRow + 1)
End Sub

Mike F

 

[Guest]

Thanks very much for that help Mike. I didnt expect anyone to have the time
to go through it all, but exactly what I was hoping to achieve eventually, I
thought better to set out for clarity. Getting started with looping is a
great help. Thanks. its just what I need to have an idea of how to go about
it.