Sending data in certain cells to another worksheet by pressing a button

R

RPIJG

I would like to be able to make it so that when I press a button on th
current worksheet, that data from the current worksheet is sent t
cells located on a seperate worksheet. Is this possible
 
B

Bob Phillips

Why have a button, why not just link them directly, they will automatically
update then.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

RPIJG

Ok, so for whatever reason, I couldn't get the sample code to work righ
for what I wanted it to do. Probably because I'm pretty miserable a
all of this. A big thank you to everyone here so far you are al
awesome. Now on to the questions.

Private Sub CommandButton1_Click()
Worksheets("MonthlyTotals").Range("A2").Value = Worksheets("Sale
Invoice").Range("O45").Value
Worksheets("MonthlyTotals").Range("B2").Value = Worksheets("Sale
Invoice").Range("O37").Value
Worksheets("MonthlyTotals").Range("C2").Value = Worksheets("Sale
Invoice").Range("O40").Value
Worksheets("MonthlyTotals").Range("D2").Value = Worksheets("Sale
Invoice").Range("P43").Value
End Sub

That is what I have and it does more or less what I want it to do
however, I want to make it so that each time you press the button i
enters the data on the next row.

So in my example it puts the information in row 2, then the next time
press the button I want it to do it in row 3. Does that make an
sense? Thanks again for all your help.

Jo
 
B

Bob Phillips

Private Sub CommandButton1_Click()
Dim iLastRow As Long
With Worksheets("MonthlyTotals")
iLastRow = .Cells(Rows.Count,"A").End(xlUp)>Row
.Range("A" & iLastRow).Value = Worksheets("Sales Invoice"). _
Range("O45").Value
.Range("B" & iLastRow).Value = Worksheets("Sales Invoice"). _
Range("O37").Value
.Range("C" & iLastRow).Value = Worksheets("Sales Invoice"). _
Range("O40").Value
.Range("D" & iLastRow).Value = Worksheets("Sales Invoice"). _
Range("P43").Value
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

RPIJG

This returned a Run-Time Error '1004'

Application-defined or object-defined error

the debugger highlights the first .Range sequenc
 
R

RPIJG

Bob said:
*Private Sub CommandButton1_Click()
Dim iLastRow As Long
With Worksheets("MonthlyTotals")
iLastRow = .Cells(Rows.Count,"A").End(xlUp)>Row
.Range("A" & iLastRow).Value = Worksheets("Sales Invoice"). _
Range("O45").Value
.Range("B" & iLastRow).Value = Worksheets("Sales Invoice"). _
Range("O37").Value
.Range("C" & iLastRow).Value = Worksheets("Sales Invoice"). _
Range("O40").Value
.Range("D" & iLastRow).Value = Worksheets("Sales Invoice"). _
Range("P43").Value
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...

That code gave a run-time error '1004'. why is that?

Thank you again for all your help
 
B

Bob Phillips

Sorry, my typo

Private Sub CommandButton1_Click()
Dim iLastRow As Long
With Worksheets("MonthlyTotals")
iLastRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Range("A" & iLastRow).Value = Worksheets("Sales Invoice"). _
Range("O45").Value
.Range("B" & iLastRow).Value = Worksheets("Sales Invoice"). _
Range("O37").Value
.Range("C" & iLastRow).Value = Worksheets("Sales Invoice"). _
Range("O40").Value
.Range("D" & iLastRow).Value = Worksheets("Sales Invoice"). _
Range("P43").Value
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

RPIJG

Works like a charm, thank you very much Bob.

Not to be too much of a PITA but is it possible to make it so that
message box pops up that says, are you sure? With a continue and
cancel
 
B

Bob Phillips

What a PITA<vbg>

Private Sub CommandButton1_Click()
Dim iLastRow As Long
Dim ans
ans = Msgbox("Are you sure?",vbYesNo)
If ans = vbYes Then
With Worksheets("MonthlyTotals")
iLastRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Range("A" & iLastRow).Value = Worksheets("Sales Invoice"). _
Range("O45").Value
.Range("B" & iLastRow).Value = Worksheets("Sales Invoice"). _
Range("O37").Value
.Range("C" & iLastRow).Value = Worksheets("Sales Invoice"). _
Range("O40").Value
.Range("D" & iLastRow).Value = Worksheets("Sales Invoice"). _
Range("P43").Value
End With
End If
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

RPIJG

PITA= Pain in the A**, I'm just amazed at how much all of you know. An
the so little that I know. Just didn't want to come off sounding lik
I was needy :) Thank you very much Bob!

Jo
 
R

RPIJG

Can I actually send this to another (closed preferred, but optional
workbook?

If it can't be closed at the time, can I just open it, and then sen
the cells?

Thanks again.

Jo
 
B

Bob Phillips

Joe,

I got PITA. I said 'What a PITA,', not 'What's a PITA?'. Just my attempt at
reciprocated humour.

Anyway, to your question. Doing it to a closed workbook can be done, but is
not simple. Far more effective to open it, work on it, and then close it..
Just precede the sheet name with the workbook name

Workbooks.Open Filename:="C:\myFiles\myWorkbook.xls"
With Activeworkbook
With .Worksheets("MonthlyTotals")

etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

RPIJG

This opens the workbook correctly but then gives me a runtime 424 objec
required...

Private Sub CommandButton1_Click()
Dim iLastRow As Long
Dim ans
ans = MsgBox("Are you sure? Doing so will automatically create a ne
invoice.", vbYesNo)
If ans = vbYes Then
Workbooks.Open Filename:="C:\Documents and Settings\Owner\M
Documents\MonthlyTotals.xls"
With ActiveWorkbook
With .Worksheets("MonthlyTotals")
iLastRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Range("A" & iLastRow).Value = SyntheticShieldInvoice.Worksheets("Sale
Invoice"). _
Range("O3").Value
.Range("B" & iLastRow).Value = SyntheticShieldInvoice.Worksheets("Sale
Invoice"). _
Range("O4").Value
.Range("C" & iLastRow).Value = SyntheticShieldInvoice.Worksheets("Sale
Invoice"). _
Range("N37").Value
.Range("D" & iLastRow).Value = SyntheticShieldInvoice.Worksheets("Sale
Invoice"). _
Range("N40").Value
.Range("E" & iLastRow).Value = SyntheticShieldInvoice.Worksheets("Sale
Invoice"). _
Range("O43").Value
End With
End With
End If
End Su
 
B

Bob Phillips

What is SyntheticShieldInvoice?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

RPIJG

it was the name of the workbook, I figured this out though. Thank
everyone for helping me with this.

Jo
 
Top