Generating Data based on a List

[Kiwi Rob]

Hi all, Just wondering if someone can help me.

I have Sheet A with Col A with Town Names... and Col B with
the postcode. there is about 150 towns.

I then have another sheet... Sheet B... which has about 20
rows of information...some of which grabs information the
contents of A1 and B1 from Sheet B.... into functions.

That works fine. However I dont want to have to enter the
150 A1 and B1 items manuelly... and then have to save them
as text files manuelly each time....

Basically If i could have it somehow read the values from
Sheet A and put them into Sheet B... one by one.. it would
then put the 20 rows into a database or a main sheet... so
that in the end I would have the 3000 listings in a file...
in the layout of Sheet B

Any Ideas?

 

[Dave Peterson]

Maybe:

Option Explicit
Sub testme()

Dim namesWks As Worksheet
Dim CalcWks As Worksheet
Dim ResWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim RngToCopy As Range
Dim DestCell As Range

Set namesWks = Worksheets("sheet1")
Set CalcWks = Worksheets("sheet2")
Set ResWks = Worksheets.Add 'new worksheet

With namesWks
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

Set RngToCopy = CalcWks.Range("a1:G20") 'modify to suit

Set DestCell = ResWks.Range("a1")

With CalcWks
For Each myCell In myRng.Cells
.Range("a1").Value = myCell.Value
.Range("b1").Value = myCell.Offset(0, 1).Value
RngToCopy.Copy
With DestCell
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
End With
Set DestCell = DestCell.Offset(RngToCopy.Rows.Count, 0)
Next myCell
End With

End Sub