userform to 2 seperate sheets

XL2000 userform 
I am using this code for a userform to add parts to a sheet named parts 
and all works well as is
 I would also like it to add just the value of this line
ws.Range("A" & r).Value = txtPartnumber.Value
to sheet2 column DR starting at DR2
is it possible?

Private Sub cmdAdd_Click()
Dim r As Long
Dim ws As Worksheet
Set ws = Worksheets("Parts")
With ws
  If .AutoFilterMode Then
    If .FilterMode Then
      .ShowAllData
    End If
  End If
End With
r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Range("A" & r).Value = txtPartnumber.Value
ws.Range("B" & r).Value = txtQty.Value
ws.Range("C" & r).Value = txtdescription.Value
ws.Range("d" & r).Value = txtmanufacture.Value
ws.Range("E" & r).Value = txtprice.Value
ws.Range("F" & r).Value = txtunits.Value
txtPartnumber.Value = ""
txtQty.Value = ""
txtdescription.Value = ""
txtmanufacture.Value = ""
txtprice.Value = ""
txtunits.Value = ""
txtParts.SetFocus
End Sub
0
methane (42)
5/11/2004 12:55:33 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
581 Views

Similar Articles

[PageSpeed] 55

with worksheets("sheet2")
  .cells(.rows.count,"DR").end(xlup).offset(1,0).value _
    = txtpartnumber.value
end with

will use the next available cell in column DR of sheet2.

(do it before you set txtpartnumber.value to "")

~Alan wrote:
> 
> XL2000 userform
> I am using this code for a userform to add parts to a sheet named parts
> and all works well as is
>  I would also like it to add just the value of this line
> ws.Range("A" & r).Value = txtPartnumber.Value
> to sheet2 column DR starting at DR2
> is it possible?
> 
> Private Sub cmdAdd_Click()
> Dim r As Long
> Dim ws As Worksheet
> Set ws = Worksheets("Parts")
> With ws
>   If .AutoFilterMode Then
>     If .FilterMode Then
>       .ShowAllData
>     End If
>   End If
> End With
> r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
> ws.Range("A" & r).Value = txtPartnumber.Value
> ws.Range("B" & r).Value = txtQty.Value
> ws.Range("C" & r).Value = txtdescription.Value
> ws.Range("d" & r).Value = txtmanufacture.Value
> ws.Range("E" & r).Value = txtprice.Value
> ws.Range("F" & r).Value = txtunits.Value
> txtPartnumber.Value = ""
> txtQty.Value = ""
> txtdescription.Value = ""
> txtmanufacture.Value = ""
> txtprice.Value = ""
> txtunits.Value = ""
> txtParts.SetFocus
> End Sub

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/11/2004 1:21:12 AM
Thank you if I have any problem with either I will get back with you,
     Thank you thank you :)

Dave Peterson wrote:
> 
> with worksheets("sheet2")
>   .cells(.rows.count,"DR").end(xlup).offset(1,0).value _
>     = txtpartnumber.value
> end with
> 
> will use the next available cell in column DR of sheet2.
> 
> (do it before you set txtpartnumber.value to "")
> 
> ~Alan wrote:
> >
> > XL2000 userform
> > I am using this code for a userform to add parts to a sheet named parts
> > and all works well as is
> >  I would also like it to add just the value of this line
> > ws.Range("A" & r).Value = txtPartnumber.Value
> > to sheet2 column DR starting at DR2
> > is it possible?
> >
> > Private Sub cmdAdd_Click()
> > Dim r As Long
> > Dim ws As Worksheet
> > Set ws = Worksheets("Parts")
> > With ws
> >   If .AutoFilterMode Then
> >     If .FilterMode Then
> >       .ShowAllData
> >     End If
> >   End If
> > End With
> > r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
> > ws.Range("A" & r).Value = txtPartnumber.Value
> > ws.Range("B" & r).Value = txtQty.Value
> > ws.Range("C" & r).Value = txtdescription.Value
> > ws.Range("d" & r).Value = txtmanufacture.Value
> > ws.Range("E" & r).Value = txtprice.Value
> > ws.Range("F" & r).Value = txtunits.Value
> > txtPartnumber.Value = ""
> > txtQty.Value = ""
> > txtdescription.Value = ""
> > txtmanufacture.Value = ""
> > txtprice.Value = ""
> > txtunits.Value = ""
> > txtParts.SetFocus
> > End Sub
> 
> --
> 
> Dave Peterson
> ec35720@msn.com
0
methane (42)
5/11/2004 1:31:45 AM
Private Sub cmdAdd_Click()

I tried it several different ways I even added in Set ws =
Worksheets("sheet2")
for some reason I cant get it too add the part number to DR it still
works in adding to sheet (parts)

Dim r As Long
Dim ws As Worksheet
Set ws = Worksheets("Parts")
Set ws = Worksheets("sheet2")
With ws
  If .AutoFilterMode Then
    If .FilterMode Then
      .ShowAllData
    End If
  End If
End With
r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Range("A" & r).Value = txtPartnumber.Value
ws.Range("B" & r).Value = txtQty.Value
ws.Range("C" & r).Value = txtdescription.Value
ws.Range("d" & r).Value = txtmanufacture.Value
ws.Range("E" & r).Value = txtprice.Value
ws.Range("F" & r).Value = txtunits.Value
With Worksheets("sheet2")
  .Cells(.Rows.Count, "DR").End(xlUp).Offset(1, 0).Value _
    = txtPartnumber.Value
End With
txtPartnumber.Value = ""
txtQty.Value = ""
txtdescription.Value = ""
txtmanufacture.Value = ""
txtprice.Value = ""
txtunits.Value = ""
txtParts.SetFocus
End Sub

Dave Peterson wrote:
> 
> with worksheets("sheet2")
>   .cells(.rows.count,"DR").end(xlup).offset(1,0).value _
>     = txtpartnumber.value
> end with
> 
> will use the next available cell in column DR of sheet2.
> 
> (do it before you set txtpartnumber.value to "")
> 
> ~Alan wrote:
> >
> > XL2000 userform
> > I am using this code for a userform to add parts to a sheet named parts
> > and all works well as is
> >  I would also like it to add just the value of this line
> > ws.Range("A" & r).Value = txtPartnumber.Value
> > to sheet2 column DR starting at DR2
> > is it possible?
> >
> > Private Sub cmdAdd_Click()
> > Dim r As Long
> > Dim ws As Worksheet
> > Set ws = Worksheets("Parts")
> > With ws
> >   If .AutoFilterMode Then
> >     If .FilterMode Then
> >       .ShowAllData
> >     End If
> >   End If
> > End With
> > r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
> > ws.Range("A" & r).Value = txtPartnumber.Value
> > ws.Range("B" & r).Value = txtQty.Value
> > ws.Range("C" & r).Value = txtdescription.Value
> > ws.Range("d" & r).Value = txtmanufacture.Value
> > ws.Range("E" & r).Value = txtprice.Value
> > ws.Range("F" & r).Value = txtunits.Value
> > txtPartnumber.Value = ""
> > txtQty.Value = ""
> > txtdescription.Value = ""
> > txtmanufacture.Value = ""
> > txtprice.Value = ""
> > txtunits.Value = ""
> > txtParts.SetFocus
> > End Sub
> 
> --
> 
> Dave Peterson
> ec35720@msn.com
0
methane (42)
5/11/2004 9:01:43 PM
That ws variable can represent one worksheet at a time.

You could use two variables, though:

Dim r As Long
Dim ws1 As Worksheet
Set ws2 = Worksheets("Parts")
Set ws = Worksheets("sheet2")
With ws
  If .AutoFilterMode Then
    If .FilterMode Then
      .ShowAllData
    End If
  End If
End With
r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Range("A" & r).Value = txtPartnumber.Value
ws.Range("B" & r).Value = txtQty.Value
ws.Range("C" & r).Value = txtdescription.Value
ws.Range("d" & r).Value = txtmanufacture.Value
ws.Range("E" & r).Value = txtprice.Value
ws.Range("F" & r).Value = txtunits.Value

With Ws2
  .Cells(.Rows.Count, "DR").End(xlUp).Offset(1, 0).Value _
    = txtPartnumber.Value
End With

txtPartnumber.Value = ""
txtQty.Value = ""
txtdescription.Value = ""
txtmanufacture.Value = ""
txtprice.Value = ""
txtunits.Value = ""
txtParts.SetFocus
End Sub

(or you could have just dropped that second "Set ws = Worksheets("sheet2")" line
and used:

With Worksheets("sheet2")
  .Cells(.Rows.Count, "DR").End(xlUp).Offset(1, 0).Value _
    = txtPartnumber.Value
End With

I like your variable approach, though.  Then if the worksheet names change, you
only have to fix it one spot.



~Alan wrote:
> 
> Private Sub cmdAdd_Click()
> 
> I tried it several different ways I even added in Set ws =
> Worksheets("sheet2")
> for some reason I cant get it too add the part number to DR it still
> works in adding to sheet (parts)
> 
> Dim r As Long
> Dim ws As Worksheet
> Set ws = Worksheets("Parts")
> Set ws = Worksheets("sheet2")
> With ws
>   If .AutoFilterMode Then
>     If .FilterMode Then
>       .ShowAllData
>     End If
>   End If
> End With
> r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
> ws.Range("A" & r).Value = txtPartnumber.Value
> ws.Range("B" & r).Value = txtQty.Value
> ws.Range("C" & r).Value = txtdescription.Value
> ws.Range("d" & r).Value = txtmanufacture.Value
> ws.Range("E" & r).Value = txtprice.Value
> ws.Range("F" & r).Value = txtunits.Value
> With Worksheets("sheet2")
>   .Cells(.Rows.Count, "DR").End(xlUp).Offset(1, 0).Value _
>     = txtPartnumber.Value
> End With
> txtPartnumber.Value = ""
> txtQty.Value = ""
> txtdescription.Value = ""
> txtmanufacture.Value = ""
> txtprice.Value = ""
> txtunits.Value = ""
> txtParts.SetFocus
> End Sub
> 
> Dave Peterson wrote:
> >
> > with worksheets("sheet2")
> >   .cells(.rows.count,"DR").end(xlup).offset(1,0).value _
> >     = txtpartnumber.value
> > end with
> >
> > will use the next available cell in column DR of sheet2.
> >
> > (do it before you set txtpartnumber.value to "")
> >
> > ~Alan wrote:
> > >
> > > XL2000 userform
> > > I am using this code for a userform to add parts to a sheet named parts
> > > and all works well as is
> > >  I would also like it to add just the value of this line
> > > ws.Range("A" & r).Value = txtPartnumber.Value
> > > to sheet2 column DR starting at DR2
> > > is it possible?
> > >
> > > Private Sub cmdAdd_Click()
> > > Dim r As Long
> > > Dim ws As Worksheet
> > > Set ws = Worksheets("Parts")
> > > With ws
> > >   If .AutoFilterMode Then
> > >     If .FilterMode Then
> > >       .ShowAllData
> > >     End If
> > >   End If
> > > End With
> > > r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
> > > ws.Range("A" & r).Value = txtPartnumber.Value
> > > ws.Range("B" & r).Value = txtQty.Value
> > > ws.Range("C" & r).Value = txtdescription.Value
> > > ws.Range("d" & r).Value = txtmanufacture.Value
> > > ws.Range("E" & r).Value = txtprice.Value
> > > ws.Range("F" & r).Value = txtunits.Value
> > > txtPartnumber.Value = ""
> > > txtQty.Value = ""
> > > txtdescription.Value = ""
> > > txtmanufacture.Value = ""
> > > txtprice.Value = ""
> > > txtunits.Value = ""
> > > txtParts.SetFocus
> > > End Sub
> >
> > --
> >
> > Dave Peterson
> > ec35720@msn.com

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/11/2004 11:17:22 PM
Reply:

Similar Artilces:

Using calculated item to calculate running balances #2
I have a Pivot Table of a cash book showing monthly transactions per account as per example below. A B C D 1 Acc Jan Feb Total 2 100 1000 500 1500 3 200 200 300 500 4 Total 1200 800 2000 5 Running 1200 2000 I would like to add a row at the bottom showing a running balance. This is easy enough with a normal formula eg B5 = sum(B4,A5) and copied to the right. This row gets overwritten if the pivot table expands downwards. I would think adding a calculated item must work, but I do not know how. Can someone help? ...

Error code #2
When I try to search for a clipart file I get the following message; Error code 0x8007000e not enough storage space available to complete this operation. What is wrong and how do i fix it? Are you using a ternimal server? 6898 � A Windows 2000 SP4 Terminal Server issues 'Not Enough Memory' when searching for clips in a Microsoft Office XP document? http://www.jsiinc.com/SUBN/tip6800/rh6898.htm If not your problem, refresh your MDAC components to 2.8 http://msdn.microsoft.com/data/ Look on the right pane. -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.msauer.mvps.o...

dll hell 2.0?
Hi everybody! I had an application developed with Visual Studio .NET 2003 and the produced binary worked fine on both Windows 2000 and Windows Server 2003. Now I converted it to Visual Studio .NET 2005 and I can't get it to run on Windows Server 2003 (no problems on Windows 2000, though). Basically if I place msvcr80.dll and mfc80.dll in the same directory as the binary, I get "This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem." when I try to run it. So instead I tried running vcredist_x8...

Printing on 2 sides of a page?
How can I print a 2 page spreadsheet on the front and back of the same sheet of paper? Using Excel 2008. Thanks! It depends on whether your printer supports duplex printing. Check your printer's features to find out. If it does, the setting will be available in the Print dialog. You can also do a "manual duplex" job by printing page 1 (or 'Odd pages'), flipping the paper yourself, then print page 2 (or 'Even pages')... If it's only 2 pages that might be the simplest approach. -- HTH |:>) Bob Jones [MVP] Office:Mac "Rick"...

Excel Lookup #2
I am trying another option here In one column I have alist of equipment and I want another column to look up the value of this column for example Row Column A Column B 1 15kva If column A shows a 15kva I want to put 2 20kva the value =A320.00 in column B 3 15kva 4 15kva So I get the result Row Column A Column B 1 15kva =A320.00 2 20kva =A30.00 3 15kva =A320.00 4 15kva =A320.00 Apologies I know this may seem simple to most but I am not too good with Excel ...

investment #2
You may have heard about Forex trading and seen many sites on the internet propagating this seemingly great investment opportunity. In fact, a lot of interest has been generated in this investment instrument. There are some organizations running TV commercials, offering this "Forex trading" as a sure fire system that is expected to bring windfall profits in an easy fashion to the individual investor; along-with the fine print of a disclaimer. The investor is well advised to ask a few questions. So, what is Forex? And what's new about it? The exchange of currencies is said to b...

insert an order number when another sheet opens
I have a form and every time I open it I need it to create a new number. Is this possible. also if I put it a number that does not correspond to an other number can it give me a warning. Thank you so much for your help Carmen cbucco@buccocouture.com ...

How to print a mail merge with a 2 page template?
I'm trying to print my Mail Merge in Publisher. The Template is 2 pages. It tells me that I can not print a 2 page document in Mail Merge. However, I can not delete page 2 or figure out a way to have the Mail Merge only pull in the first page. If anyone has any solutions on this I would really appreciate it. The Mail Merge only needs to print on page 1. Page 2 is just the backside of the Postcard that I'm creating. Thanks Are you using a template? Have you setup your page as a postcard? Can you select "current page" in the print dialogue? You can copy/paste the...

mailbox backup #2
Hello, It is better to backup each mailbox, or the mailbox store? Using verita 9.0 on e2k w2k server sp4. Right now I'm doing both, but if a user leaves and the backup fails because I can not deselect them when I am backing up the entire store. TIA Eric On Fri, 10 Jun 2005 10:08:46 -0700, Eric K <EricK@discussions.microsoft.com> wrote: >Hello, > >It is better to backup each mailbox, or the mailbox store? Using verita 9.0 >on e2k w2k server sp4. >Right now I'm doing both, but if a user leaves and the backup fails because >I can not deselect them when I am...

CCheckListBox, #2
how to make CCheckListBox not to recevie the user input? it is used to display status only. but I can programmingly call SetCheck to check it. You could make the control read only or disable it. It will display a little differently, but it will do what you want. Tom "Patrick Zou" <patrick_zou@no_spam_hotmail.com> wrote in message news:uWkyBaSaEHA.212@TK2MSFTNGP12.phx.gbl... > how to make CCheckListBox not to recevie the user input? it is used to > display status only. but I can programmingly call SetCheck to check it. > > ...

Advance filter not working #2
I have data lik Sr. no., Date, name, bill no., item, rate, bill amoun When I use advance filter with above data it works some times only I have created criteria range in A1: B7 and data are A3:B135 when I put criteria in name it works some time and after some days data done not return any information about name and other data Any help Shital Sha ...

Open Userforms from a Dropdown list
Hello, and thanks for the help, I have several userforms and would like to place the names of the forms into a dropdown list to select the form i wish to use is this possible. again thanks ...

Removing the "broom" that pops-up in Excel #2
Does anyone know how to resolve this issue? The broom appears after I copy/paste a row. The broom allows me to change formatting, but it gets in my way and I can't see the cells beneath it. Frustrating! ...

is there a way? #2
To access my VBA project codes again of which its password I've forgotten? TIA --- Outgoing mail is certified Virus Free. (Giden posta vir�ss�z olarak belgelendi.) Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.693 / Virus Database: 454 - Release Date: 31.05.2004 You can purchase VBA Key from http://www.lostpassword.com/ for $45.00. This will break VBA Project passwords. It doesn't find the original password, but rather finds a password that will work, allowing you to open the project and change the password. -- Cordially, Chip Pearson Microsoft MVP - E...

Creating a userform?
I would like to enter data to a speadsheet with a userform. I want enter the data in a form that will automatically insert the data to the correct cells in my spreadsheet. And then allow me to enter new data on the next line below. Is this possible? I have just put up a file for you at:- http://www.pierrefondes.com/ It is item number 42 towards the top of my home page. In here I have tried to explain, as clearly as possible, what you need to do to set up and use a Form in EXCEL 2007. There is a simple worked example for you to work through if you want to. Please d...

Serialization #2
Hi All, I use Serialize() to open files in my application. However, if the file is ReadOnly I get the error "Failed to open document" from CDocManager::OpenDocumentFile(LPCTSTR lpszFileName). Anyone have any ideas how to get around this? Cheers, Rob ...

Compare Data across worksheets, list differences in 3rd sheet
I have looked for this solution and I=92m sure its there but I have been unable to locate it Basically I have two worksheets, identical format, I would like to compare the rows of data in each sheet and place the found data into a 3rd blank worksheet. This can be reduced to comparing 1 column in each sheet i.e. Part Number but I would require the whole row of data to be placed in the 3rd sheet, with some sort of Identifier to which sheet the data came from. I=92m not sure how clear this is, if further clarification is required please ask. Thanking you in anticipation Check out Vlookup http:/...

Looking for FAX software FREE TRIAL
Hi... This is slightly off the NEWS topic, but I have spent 4 days searching this. I desperately need to fax something and have no options. I had fax stuff on my PC, but that PC is gone. I have an iMAC OS 9.2.2 and a good Epson scanner... but no fax software for this computer. is there any FREE TRIAL software for my system that is FULLY FUNCTIONAL TO TRY ? Please help as soon as you can... Thanks in advance, Kimi You'd be best off searching the web via google or somesuch, though someone may come along with a suggestion. Try different combinations of varying phrases, here's ...

Direct Push #2
Has anyone gotten Direct Push to work? If so how? I am trying to get a Treo 700w to work… Help!!! This answers your question. I hope. :) http://blogs.msdn.com/jasonlan/archive/2006/01/05/509621.aspx -- Neil Hobson Exchange MVP "Ted" <Ted@discussions.microsoft.com> wrote in message news:68C7D098-B368-4BB8-B852-FB560B6A5F2E@microsoft.com... > Has anyone gotten Direct Push to work? > If so how? > I am trying to get a Treo 700w to work. > Help!!! > It does, thanks. "Neil Hobson [MVP]" wrote: > This answers your question. I hope. :) >...

opening a userform from a userform
Is there a way to open and use a userform from another userform? I am using userforms for data entry and would like the option to open a second form whilst entering data into a form Can anyone help? [excel 2003] Just Show it. Call is like this... UserForm2.Show or like this... UserForm2.Show vbModeless depending on whether you want UserForm2 to be modal or non-modal. Remember to change my example UserForm name from UserForm2 to whatever the name your second UserForm is. -- Rick (MVP - Excel) "Roger on Excel" <RogeronExcel@discussions.microsof...

Extended Pricing #2
Is there a way that I can get a list of all the tables that are created with the implementation of Extended Pricing? Look in Tools > resource Descriptions > Tables and select Extended Pricing as the product. "Phil" <Phil@discussions.microsoft.com> wrote in message news:964DD94E-4E40-4C92-BAB1-A8B0E447193A@microsoft.com... > Is there a way that I can get a list of all the tables that are created > with > the implementation of Extended Pricing? It isn't listed under products. :( Hi Phil. I work with the following tables when querying the data. It ...

adding occurrences for date range #2
Someone from this group helped me get through the first steps of this process. I have 2 date columns and a column that identifies a department name. I want to create a formula that looks for a date range in the first column, if there is no entry in that column, I want to refer to the second column looking for that date range. For the records identified I want to count how many times the department is represented. First date column is B. Second date column is C. Department name is column D. I have tried the following: =SUMPRODUCT((B2:B375>=F1)*(B2:B375<=F2)+(Data!$D2:$D532="&quo...

UML #2
Hi, There is some way in an Class Diagram which have some compositions to show only de composite class of a composition and making a double clik over it so see the composite structure diagram or something like that? is there some other tool which do that ? Regards, Fernando ...

Printing in Excel #2
I am having trouble with printing Excel files that I have used for years. Of late they have re-formatted somehow. When the file is open in normal view the page looks ok, but when in print preview and when the page is printed there is too much excess space in each cell and stretching to the left and right margins automatically. What is causing this and how can I overcome it. Have you recently changed to a new operating system, or installed a new printer? If so, you may have to download a new version of the print driver from the printer manufacturer's web site. Deon Shirley wrote: ...

Profit % for Sale Price #2
Should Display Profit % for a Sale Price Item ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=655f10aa-b52a-4cdf-9564-77362e65dc23&dg=microsoft.public.pos hi, Hilten, ...