Grabing Data... Saving.... Repeat

[Kiwi Rob]

Hi all, Just wondering if someone can help me.

I have Sheet A with Col C with Town Names... and Col A 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 C1 from Sheet B.... into functions.

Basically If i could have it somehow read the values from
Sheet A and put them into Sheet B... one by one (town name and post code)..
it would
then save the contents of the sheet to either a new sheet.. or preferabibly
to a file which collects all the out puts of this function.
... so that in the end I would have the 3000 listings in a file...
in the layout of Sheet B

Any Ideas?

I have a file if someone would like to look at it... just email me robport @
inspire . net . nz (take out the spaces)

 

[Frank Kabel]

Hi
not completly sure what you're trying to do. <You may post some example
data and your expected result (plain text - no attachment please)

 

[Dave Peterson]

You didn't like yesterday's suggestion?

http://groups.google.com/[email protected]

 

[Kiwi Rob]

Do my replys to this group work?
Just wondering.
Cheers Rob


Untitled Document

 

[Dave Peterson]

I can see your reply.

But I sometimes suffer from SSS (Sixth Sense Syndrome).

I see posts. They're everywhere.

 

[Kiwi Rob]

ok.... in sheet A.. there is two cols ... the town name.. and the town
code.... these are in A and C

such as...

Auckland 0001
Wellington 0022
Palmerston North 0203

what I need.. is for sheet B to pull in Auckland and 0001 into cells A1 and
C1... then the sheet b should update because some of the feilds draw on the
data from A1 and C1.....

This then would save the outputs of sheet B into either a new sheet... or
file.....
and it would then go and do the same for the next entry in sheet A... in
this case.. Wellington and 0022....

there is around 150 towns and codes in sheet A and around 20 rows on Sheet
B....

Any Ideas? ... Cheers guys

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim namesWks As Worksheet
Dim CalcWks As Worksheet
Dim CopiedSheet As Worksheet
Dim myRng As Range
Dim myCell As Range

Set namesWks = Worksheets("sheet1")
Set CalcWks = Worksheets("sheet2")

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

With CalcWks
For Each myCell In myRng.Cells
'where does the data from A and C go?
'I used A1 and B1
.Range("a1").Value = myCell.Value
.Range("b1").Value = myCell.Offset(0, 2).Value
Application.Calculate 'just in case

'make a copy
CalcWks.Copy _
after:=CalcWks

'get rid of formulas????
Set CopiedSheet = ActiveSheet
With CopiedSheet
.UsedRange.Copy
.UsedRange.PasteSpecial Paste:=xlPasteValues
End With
Next myCell
End With

End Sub

If you want to leave the formulas, get rid of that .copy & .pastespecial stuff.

ok.... in sheet A.. there is two cols ... the town name.. and the town
code.... these are in A and C

such as...

Auckland 0001
Wellington 0022
Palmerston North 0203

what I need.. is for sheet B to pull in Auckland and 0001 into cells A1 and
C1... then the sheet b should update because some of the feilds draw on the
data from A1 and C1.....

This then would save the outputs of sheet B into either a new sheet... or
file.....
and it would then go and do the same for the next entry in sheet A... in
this case.. Wellington and 0022....

there is around 150 towns and codes in sheet A and around 20 rows on Sheet
B....

Any Ideas? ... Cheers guys

 

[Kiwi Rob]

Awesome, that works well.. but only does the first 11..... im wondering
maybe if its something to do with the copy and paste... any ideas?

Cheers

 

[Kiwi Rob]

Aha I fixed that little problem.. and its working fine...
Is there anyway I could modify it so that instead of making a new sheet for
each one.. it adds them all to a master sheet... so that they are all in one
place.....

Cheers Rob

Awesome, that works well.. but only does the first 11..... im wondering
maybe if its something to do with the copy and paste... any ideas?

Cheers

Option Explicit
Sub testme()

Dim namesWks As Worksheet
Dim CalcWks As Worksheet
Dim CopiedSheet As Worksheet
Dim myRng As Range
Dim myCell As Range

Set namesWks = Worksheets("sheet1")
Set CalcWks = Worksheets("sheet2")

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

With CalcWks
For Each myCell In myRng.Cells
'where does the data from A and C go?
'I used A1 and B1
.Range("a1").Value = myCell.Value
.Range("b1").Value = myCell.Offset(0, 2).Value
Application.Calculate 'just in case

'make a copy
CalcWks.Copy _
after:=CalcWks

'get rid of formulas????
Set CopiedSheet = ActiveSheet
With CopiedSheet
.UsedRange.Copy
.UsedRange.PasteSpecial Paste:=xlPasteValues
End With
Next myCell
End With

End Sub

If you want to leave the formulas, get rid of that .copy & .pastespecial stuff.

A1

and

on

the

and

post

email

 

[Dave Peterson]

That's what the first version did.

Back to the first version...

Change:
With CalcWks
For Each myCell In myRng.Cells
.Range("a1").Value = myCell.Value
.Range("b1").Value = myCell.Offset(0, 1).Value
to:

With CalcWks
For Each myCell In myRng.Cells
.Range("a1").Value = myCell.Value
.Range("b1").Value = myCell.Offset(0, 2).Value
and
change:
Set RngToCopy = CalcWks.Range("a1:G20") 'modify to suit
to the address you want copied.

Aha I fixed that little problem.. and its working fine...
Is there anyway I could modify it so that instead of making a new sheet for
each one.. it adds them all to a master sheet... so that they are all in one
place.....

Cheers Rob