Copying certain cells from each row to another sheet for invoice

Smallman · Mar 28th 2012

Re: Copying certain cells from each row to another sheet for invoice

Hey Mike

I couldn't open the attachment but that is OK. Try this version of the Save method.

Take care

Smallman

Code

Sub UpdateStock()
Dim i As Integer
Dim ans As String
ans = MsgBox("Do you want to update Stock?", vbYesNo)
    If ans = vbNo Then
    Exit Sub
    Else
        For i = 6 To 300
            Sheet1.Range("L" & i).Value = Range("E" & i).Value - Range("C" & i).Value
        Next
        Sheet1.Range("C6:C300").Value = 0
        End If
End Sub

Display More

mspeight2 · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

I've attached the updated file. I've completely lost the plot of what has been happening in the last few posts. hopefully you have an idea of where we are at??

Many Thanks
Mike

Smallman · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

I am good Mike. You will see the end of this shortly.

Have a look at the attached mate. Hope this is OK.

Take care

Smallman

mspeight2 · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

looks and works awesome mate!

i can certainly see the light and the end of the tunnel!!

The labour charges don't seem to transfer to the invoice sheet. also what is the protocol on saving and wiping the data from the invoice sheet.

Cheers mate!

Smallman · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

Hi Mike

What cells do you want to transfer to the Invoice sheet. To keep it simple just say I want F303:H303 of the Data tab into cells blah of the Invoice tab. I really don't see how they fit into the equation.

Take care

Smallman

mspeight2 · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

Hey Smallman,

Yeah F303:H303 is what i need transferring to the invoice tab.

Thanks a lot dude

Mike

Smallman · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

Mike where is the BLAH? You forgot to tell me where it is ment to go. I don't see a home for it. To you it is simple but I Can's see an appropriate place to put it. If this goes in the Subtotal on the Template sheet then the sum of the parts don't equal the details in the sheet so naturally I can't see a home for these numbers.

Smallman

mspeight2 · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

Sorry again I'm mis-reading your posts. cool.....

I need (=K301+F303) into cell M41 on the invoice tab

then....

=M41*1.15 into cell M43 (all these cells are on the invoice tab)

then....

=M41+M43 into cell M45 (all these cells are on the invoice tab)

I hope this makes sense

Regards Mike

mspeight2 · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

disregard my last post please. still working on it

Smallman · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

Hi Mike

I think the following;

Quote

=M41*1.15 into cell M43 (all these cells are on the invoice tab)

then....

=M41+M43 into cell M45 (all these cells are on the invoice tab)

Is already done in the Template sheet which means you only need point 1 and you are done. I will do this now.

Smallman

mspeight2 · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

Right here we go

1. Tab1 A303 (labour) transpose to C40 on Tab2

2. Tab1 F303 (net total) transpose to M40 Tab2

3. Tab2 M41 to be the AUTOSUM of the above column

4. Tab2 M45 (=M41*1.15)

5. Tab2 M43(=M45-M41)

Thanks Heaps
Mike

Smallman · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

Hi Mike

I have added two new named ranges to the following so if your data moves the calculation of your Net Labour figure will still be accurate. Be sure not to delete these named ranges. Here is the file I think gets you over the line.

Let me know how you go.

Take care

Smallman

mspeight2 · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

Hey Smallman,

The figures are being claculated properly and transferred properly! however the word "labour" does not appear in cell C40 on tab2.

Mike

mspeight2 · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

It also seems we have the function of cells M6 and N6, its probable that im not using the workbook correctly?

Huge thanks
Mike

Smallman · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

Mike

In answer to your first post you have two options (by the way) I just went and opened your sheet of Templates from way back and Labour does not appear on any of these sheets.

Firstly you could Put the word labour in C40 of the Template sheet and change this line

Code

Sheet4.Range("C19:M39").ClearContents

Or you could just add the following line to the bottom of the existing code.

Code

Sheet4.Range("C40").Value = "Labour"

Also what is the Function of these M6 and N6. If you speak in finace terms I will understand.

Take care

Smallman

mspeight2 · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

yeah alls cool with the options you have given me for the "labour" transposing issue

ok for an example:

2 in D6 (QTY)

4 in F6 (stock left over)

5 in G6 (maximum stock held)

1 in M6 (no.of items to order)

4 in N6 ( last saved stock)

Then press the SAVE button at the top right of tab 1. And the figures should calculate to the following.

0 in D6 (QTY)

2 in F6 (stock left over)

5 in G6 (maximum stock held)

3 in M6 (no. of items to order)

2 in N6 (Last saved stock)

Hopefully this clears it up for you.

Mucho gracias!

Smallman · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

Hi Mike

I have set this up so it is set to save the file when you open it. It has the parameters from your last post. You need to now start getting your head around the coding so these simple changes I am making are changable at your end. Otherwise you will not be able to maintain the model. Hope this helps.

Take care

Smallman

mspeight2 · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

Thanks for all your help Smallman, you have been an absolute star putting up with all my demands and poor descriptions of how i wanted this workbook to operate. When I get time at work tomorrow i will print out all the different coding from earlier models. And hopefully i will be able to see how you have changed it from one to the next.

I haven't had time to look through your latest file attachment but I will tomorrow.

Thanks again mate your a legend!!:)

Mike

mspeight2 · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

Hey mate,

Had a quick look just now all seems sweet, however the labour charges do not transpose. I will have a little play with the code tomorrow to see if i can understand the coding:)

HUGE thanks again mate!!!

Mike

Smallman · Mar 29th 2012

Re: Copying certain cells from each row to another sheet for invoice

Hi Mike

No worries. See how you go tomorrow. I tested it and from my understanding it seemed to work OK. Anyways hopefully your presentation of the model will go well. Good luck. :dance:

Take care

Smallman

Copying certain cells from each row to another sheet for invoice

Participate now!

Share