Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a macro that increments +1 by pressing a button | Excel Discussion (Misc queries) | |||
Sending data from a form to another worksheet | Excel Discussion (Misc queries) | |||
Excel: Change content A1 with B1 by pressing one button <- | Excel Worksheet Functions | |||
print different worksheet by pressing visible button on worksheet | Excel Worksheet Functions | |||
Sending e-mail with Lotus Notes pressing an excel button | Excel Discussion (Misc queries) |