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

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

--
Message posted from http://www.ExcelForum.com

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

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)

"RPIJG " wrote in message
...
I would like to be able to make it so that when I press a button on the
current worksheet, that data from the current worksheet is sent to
cells located on a seperate worksheet. Is this possible?


---
Message posted from http://www.ExcelForum.com/

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

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

--
Message posted from http://www.ExcelForum.com

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

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)

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

Private Sub CommandButton1_Click()
Worksheets("MonthlyTotals").Range("A2").Value = Worksheets("Sales
Invoice").Range("O45").Value
Worksheets("MonthlyTotals").Range("B2").Value = Worksheets("Sales
Invoice").Range("O37").Value
Worksheets("MonthlyTotals").Range("C2").Value = Worksheets("Sales
Invoice").Range("O40").Value
Worksheets("MonthlyTotals").Range("D2").Value = Worksheets("Sales
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 it
enters the data on the next row.

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

Joe


---
Message posted from http://www.ExcelForum.com/

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

This returned a Run-Time Error '1004'

Application-defined or object-defined error

the debugger highlights the first .Range sequenc

--
Message posted from http://www.ExcelForum.com

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

Bob Phillips wrote:
*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)

"RPIJG " wrote in message
...
Ok, so for whatever reason, I couldn't get the sample code to wor
right
for what I wanted it to do. Probably because I'm pretty miserabl
at
all of this. A big thank you to everyone here so far you are all
awesome. Now on to the questions.

Private Sub CommandButton1_Click()
Worksheets("MonthlyTotals").Range("A2").Value = Worksheets("Sales
Invoice").Range("O45").Value
Worksheets("MonthlyTotals").Range("B2").Value = Worksheets("Sales
Invoice").Range("O37").Value
Worksheets("MonthlyTotals").Range("C2").Value = Worksheets("Sales
Invoice").Range("O40").Value
Worksheets("MonthlyTotals").Range("D2").Value = Worksheets("Sales
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 butto
it
enters the data on the next row.

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

Joe


---
Message posted from http://www.ExcelForum.com/
*

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

Thank you again for all your help

--
Message posted from http://www.ExcelForum.com

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

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)

"RPIJG " wrote in message
...
This returned a Run-Time Error '1004'

Application-defined or object-defined error

the debugger highlights the first .Range sequence


---
Message posted from http://www.ExcelForum.com/

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

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

--
Message posted from http://www.ExcelForum.com

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

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)

"RPIJG " wrote in message
...
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 a
message box pops up that says, are you sure? With a continue and a
cancel?


---
Message posted from http://www.ExcelForum.com/

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

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

--
Message posted from http://www.ExcelForum.com

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

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

--
Message posted from http://www.ExcelForum.com

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

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)

"RPIJG " wrote in message
...
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 send
the cells?

Thanks again.

Joe


---
Message posted from http://www.ExcelForum.com/

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

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

--
Message posted from http://www.ExcelForum.com

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

What is SyntheticShieldInvoice?

--

HTH

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

"RPIJG " wrote in message
...
This opens the workbook correctly but then gives me a runtime 424 object
required...

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


---
Message posted from http://www.ExcelForum.com/

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

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

Jo

--
Message posted from http://www.ExcelForum.com