Printing Forumla question

[superkopite]

Hi Guys and Gals,

I have a page that I need to print multiple times with different pieces of info, but cannot work out how to approach it.

Let me explain...

I have a worksheet called LocationCard

On it is 4 bits of data from a worksheet called Overview. The data is genertated with the code =Overview!A1. The next step of data is B1,C1,D1

The data is 50 rows deep.

What I need to do is be able to setup the LocationCard worksheet to print 50 times taking data from the next row each time.

Is there an easy way of doing this?

Thanks

James

 

[Dannyh1]

If it wasn't for the name I might help you

 

[Dannyh1]

maybe try somthing like this

Sub Macro1()
'
'
    Dim rng As Range
    Dim Cl As Range
    
    Set rng = Sheet("Overview").Range("A1:AX50")
    
    For Each Cl In rng
    
    Sheets("Sheet1").Select
    
    Sheets("Sheet1").Range("A1") = Cl.Value
    
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    Next Cl
    
    
End Sub

 

[superkopite]

If it wasn't for the name I might help you

Normally I wouldn't accept help from somebody who is quite obviously a bitter but right now I will take anything!!!

 

[superkopite]

Thanks Danny,

If I am right (not the greatest using code) I should paste this code onto my LocationCard workesheet's code section.

I have edited the code as below;

Sub Macro1()
'
'
Dim rng As Range
Dim Cl As Range

Set rng = Sheets("Overview").Range("A1:AX50")

For Each Cl In rng

Sheets("LocationCard").Select

Sheets("LocationCard").Range("A1") = Cl.Value

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Next Cl


End Sub

However It doesn't seem ti be updating the bits that I want and tried to print 1000's of pages

What have i done wrong?

Thanks

 

[superkopite]

Sub Macro1()
'
'
Dim rng As Range
Dim Cl As Range

Set rng = Sheets("Overview").Range("A1:AX50")

For Each Cl In rng

Sheets("LocationCard").Select

Sheets("LocationCard").Range("A1") = Cl.Value

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Next Cl


End Sub

Actually I think that should be ;

Sheets("Overview").Select

Sheets("Overview").Range("A1") = Cl.Value


But i am unsure as to where the code states to pick the information from

 

[Dannyh1]

Hi James,

Apologies I have made an error try:

[I]Sub Macro1()
'
'
Dim rng As Range
Dim Cl As Range

Set rng = Sheets("Overview").Range("A1:AX1")

For Each Cl In rng

Sheets("LocationCard").Select

Sheets("LocationCard").Range("A1") = Cl.Value

Sheets("LocationCard").PrintOut Copies:=1, Collate:=True
Next Cl


End Sub
[/I]

Have I got the correct understanding though:

On LocationCard, all the data on this sheet is dependent on what is in cell A1

Cell A1 on Location card will =Overview!A1 through to AX1 ?

 

[superkopite]

Ok I am nearly there....

Sub Macro1()
'
'
    Dim L1 As Range
    Dim L2 As Range
    Dim Cl As Range
    
    Set L1 = Sheets("Overview").Range("A2:A50")
    Set L2 = Sheets("Overview").Range("B2:B50")
    
    For Each Cl In L1
    
    Sheets("LocationCard").Select
    
    Sheets("LocationCard").Range("A6") = Cl.Value
    
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    Next Cl
    
    For Each Cl In L2
    
    Sheets("LocationCard").Select
    
    Sheets("LocationCard").Range("e9") = Cl.Value
    
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    Next Cl
    
End Sub

I have the first set of data performing how I want, however I think i have buggered up the second with my naive coding. So in the code above the values in A6 are updating as require however those in E9 are not.

Any ideas about what I have done wrong???

Thanks

 

[superkopite]

Have I got the correct understanding though:

On LocationCard, all the data on this sheet is dependent on what is in cell A1

Cell A1 on Location card will =Overview!A1 through to AX1 ?

I think I may have explained myself poorly.

LocationCard has 4 bits of data to be picked up

LocationCard A6 picks up from Overview!A2
LocationCard E9 picks up from Overview!B2
LocationCard E10 picks up from Overview!C2
LocationCard F11 picks up from Overview!D2

Does that make more sense?

 

[Dannyh1]

Ah I think this is it:

[I]Sub Macro1()
'
'
Dim rng As Range
Dim Cl As Range

Set rng = Sheets("Overview").Range("A2:A51")

For Each Cl In rng

Sheets("LocationCard").Select

Sheets("LocationCard").Range("A6") = Cl.Value[/I]
[I]Sheets("LocationCard").Range("E9") = Cl.Offset(0,1).Value[/I]
[I]Sheets("LocationCard").Range("E10") = Cl.Offset(0,2).Value[/I]
[I]Sheets("LocationCard").Range("F11") = Cl.Offset(0,3).Value

Sheets("LocationCard").PrintOut Copies:=1, Collate:=True
[/I]
[I]Next Cl


End Sub

[/I]