Free Microsoft Excel 2013 Quick Reference

Userform data to multiple worksheets

Hi all,

I have created a userform with textboxes, comboboxes and command buttons.

It basically serves as a meeting agenda database.

I have about 20 options under my "Meeting Attendee" combobox. The options consist of the first and last name initial of people whom my boss will be meeting with. I have so far created it so that all of the information from the userform goes into one worksheet. But I have realized that this is not the best option. I require for each entry to go to the respective sheet of the meeting attendee (i.e. if he chooses Allan as his meeting attendee the information should go to a worksheet named "Allan", if he chooses KT the info should go to a worksheet named "KT".

I am teaching myself vba and am not very familiar with it. Can anyone please help?

Thanks


Post your answer or comment

comments powered by Disqus
I apologies if the title is confusing but I did not know how to describe it otherwise. Also something to note is that i am not looking for someone to write the code for me just a direction on the best way to approach this problem. So here we go:

My current workbook has a command button that loads a userform. The data from the userform is put into the first empty row on the first worksheet. This all works perfectly and I want this function to remain. However, I wish to output the same data to other worksheets. Best way to describe it is with an example (if you open the workbook and userform it will make more sense).

Let's say Player X, 1B, Marathon Man, and 15 are inputted into the userform. I am looking for a way to output that data to the specified team worksheet (each worksheet is a team name) and then place each Player Name and Cost in their respective sells. So upon pressing "ok" on the userform the data will show up on sheet1 in the first open row (row 2) and on its respective sheet (in this case it would be sheet2/Marathon Man). Basically I need 2 criteria met but in a particular order (team name first then position second). It does not need to be a variable clause by which I mean I am ok with hardcoding each team name in... for now.

Please see the attached workbook and let me know your thoughts. Again, I do not need any specific code just looking for a way to approach this problem without writing 200+ if/then/else statements.

thanks in advance

Hi,

I have a list of data in a single worksheet with thousands of rows. I
would like to separate them to multiple worksheets in counts of 999
rows per worksheet using a macro. How do I go about this?

So for records 1-999 is in sheet 1, records 1000-1999 is in sheet
2...and so on...

I just have a really basic knowledge on using macros.

Thanks,
Lee

--
lqfong
------------------------------------------------------------------------
lqfong's Profile: http://www.excelforum.com/member.php...o&userid=35774
View this thread: http://www.excelforum.com/showthread...hreadid=555445

I have a financial workbook and would like to have a user form that would enter the data on multiple worksheets.
My actual workbook is too large to upload even compressed so here is a small example.
I'm thinking a wizard might be the answer but not sure how to set it up.
I know how to make a user form for one sheet.
The idea is to open a user form with a comand button then click paycheck or expence.
That inturn would give an option for a month then enter amount and it wouold fall
in place next empty row.

Hi,

I have a list of data in a single worksheet with thousands of rows. I would like to separate them to multiple worksheets in counts of 999 rows per worksheet using a macro. How do I go about this?

So for records 1-999 is in sheet 1, records 1000-1999 is in sheet 2...and so on...

I just have a really basic knowledge on using macros.

Thanks,
Lee

I have created a button on sheet1 that opens a userform, this form has a textbox which displays the clients name and a textbox where notes can be added about the client.

When I click the 'add notes' button I would like to save the data to another worksheet with the clients name in column A and the notes in Column B. Because there are a number of clients I would like a new row for each client, and if i was to add more than one note to a client it would add the note rather than create a new row.

I hope this makes sense.

I often have to move Rows of data from multiple worksheets on to one master sheet, and wondered if anyone had a macro that would go through all of these and do it for me?
I need something exactly like for particular rows ( i no need to copy the entire rows from the sheets)... I have very limited experience with VBA.

Thanks!
Nirmal

Private Sub CommandButton4_Click()

ListBox2.RowSource = "R11:R15"

End Sub

Hi,
With this Code and pressing a button i get the data shown on a list box
within a userform from the active worksheet. However, I need it to add the
data from multiple worksheets and show it too me on the userform!

Thanks

Hello all, I am trying to get my head around adding data to a worksheet
via a userform. I want to add to lists in sheet 3 in the next available
row but I keep getting a application-defined or object defined error.
Debug points me to the 2nd line of the macro. (Set LastRow).
Can any one help? Thanks
Kezza

Private Sub CommandButton6_Click()
Dim LastRow As Object

Set LastRow = Sheet3.Range("a65536").End(x1Up)

LastRow.Offset(1, 0).Value = TextBox6.Text
LastRow.Offset(1, 1).Value = TextBox7.Text
LastRow.Offset(1, 2).Value = TextBox8.Text

MsgBox "One record written to Sheet3"
response = MsgBox("Do you want to enter another record?", vbYesNo)

If response = vbYes Then
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""

TextBox6.SetFocus

Else
Unload Me
End If
End Sub

Hi, guys..
I know how to enter or edit the same data on multiple worksheets simultaneously...but..
How to create a chart for the same data on multiple worksheets simultaneously?

Please help me...
Thank you.

I've searched around a bit, but haven't been able to find a solution to this exact issue. All the other threads about copying data from multiple worksheets that I was able to find were looking to combine all the data from worksheets into one. However, I have been unable to adapt the codes to my needs.
I have a workbook with a variable number of worksheets. There are two fixed sheets "errors" and "non-errors," plus a number of other worksheets, the exact quantity of which will vary (as will the names of the sheets). If you are curious, the worksheets are created by another macro as a result of a particular data input, so depending on the nature of the input, the number/names will be different each time the program is run. So the layout of these worksheets is standard across all of them, the data I need are in the same set of cells on each worksheet.

There is a small range of data (containing formulas, but I only need the values that the formulas output, not the formulas themselves) on each of those worksheets that I need to copy onto a new worksheet, under a column whose title header is the name of the worksheet that set of data came from. I am running a Monte Carlo simulation, which is why I need this rather specific set of data.
So, say each of the variable worksheets is set up like this:
(Worksheet name: hotel)

A B C D data data DATA DATA

The data is all numerical. The lowercase data is the stuff I don't need, the uppercase is the stuff I do need.

I want to be able to take the DATA from each of those worksheets, and have it copied to a new worksheet that looks something like this (exact layout isn't important, really, I know enough about Excel macro coding to fine tune the layout, I just don't know how to do this type of parsing):

hotel potato church DATA C DATA C DATA C DATA D DATA D DATA D

Hotel potato and church are all worksheet names. DATA C and DATA D are the DATA from columns C and D of the original worksheet, as I explained above.

Anyway, can anyone help me out with this?

I apologize that I don't have any code to provide, but I am literally at a complete loss as to how to complete this. Even if you just point me in the right direction I would be most grateful.

Hi all.

Say that Filtered the table (Autofilter) using a criteria from HEADER B "Dog" and from HEADER C "Sunday". I then get this filtered table: (Sheet 1)

HEADER A | HEADER B | HEADER C | HEADER D | HEADER E
---------------------------------------------------------
A3 | Dog | C3 | Sunday | E3
A5 | Dog | C5 | Sunday | E5
A6 | Dog | C6 | Sunday | E6

Criteria is picked thru a combobox via userform. So each combobox have a variable fe:


	VB:
	
 
    .AutoFilter Field:=3, Criteria1:=Me.cmbHeaderB.Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How do I copy data to another worksheet (Sheet 2) so that it will look as below? (The column with the filtered data will not be included, but is written on the top of the table, assume C1)

Header B: Dog
Header D: Sunday

HEADER A | HEADER C | HEADER E
----------------------------------
A3 | C3 | E3
A5 | C5 | E5
A6 | C6 | E6

Thanks a lot

hi I have two worksheets (Sheet1 and Sheet2).

For example I have three columns named Name, City and Telephone.

UserForm1 has three textbox(TextBox1, TextBox2 and TextBox3).
All three textboxes data populated from sheet1 (Name, City and Telephone) by selecting a comboBox.

The code is below... and which is working fine.. The problem is in my next code. I am trying to amend data in all worksheets but it is not working.


	VB:
	
 ComboBox1_Change() 
     
    Dim strNamedRange As String 
    Dim lRelativeRow As Long 
     
    With ComboBox1 
        If .ListIndex > -1 Then 
            strNamedRange = .RowSource 
             
             'ListIndex starts at zero
             
            lRelativeRow = .ListIndex + 1 
             
             'Fill with adjacent column to  Combobox choice
             
            TextBox1 = Range(strNamedRange)(lRelativeRow, 1) 
             'TextBox1 is Name
             
            TextBox2 = Range(strNamedRange)(lRelativeRow, 2) 
             'TextBox2 is City
             
            TextBox3 = Range(strNamedRange)(lRelativeRow, 3) 
             'TextBox3 is Te,
        End If 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Amend data in all worksheets code is not working .....

	VB:
	
 
Private Sub goButton_Click() 
    Dim lRelativeRow As Long 
     
    Dim ws As Worksheet 
     
     
    For Each ws In ActiveWorkbook.Worksheets 
         
         'Problem is here- for each worksheet is not working
         'it is only update one worksheets
         ' The reason i want to use for each worksheets is- I might have more than 2 worksheet
         
         'On Error Resume Next
         
        With ComboBox1 
            If .ListIndex > -1 Then 
                strNamedRange = .RowSource 
                 
                 'ListIndex starts at zero
                lRelativeRow = .ListIndex + 1 
                 'Fill with adjacent column to  Combobox choice
                Range(strNamedRange)(lRelativeRow, 1) = TextBox1.Value 
                Range(strNamedRange)(lRelativeRow, 2) = TextBox2.Value 
                Range(strNamedRange)(lRelativeRow, 3) = TextBox3.Value 
                 
                 
            End If 
        End With 
         
    Next 
     
     
     
     'On Error GoTo 0
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I've designed a Key performance indicator spread sheet updated by staff at the end of each day. To enable sharing (so they don't have to cue up to enter the info.) I've given each staff member a separate worksheet with in the book. (it also helps with data interogation later).

It has a series of Userforms that all feed back to 'main' userform. one of the values is the staff number.

What I'd like to do initially is to be able to use the staff number - value (from userform combobox1002) as the worksheet identifier, i.e. save the userform info to THAT worksheet.

At the moment I'm using the following method to transfer the userfom values. (I'm hoping it will only be an additional line or three added to it or an ammendment to the bold lines).


	VB:
	
[B]ActiveWorkbook.Sheets("data").Activate [/B] 'sets the "data" worksheets as active
[B]NextRow = Worksheets("data").Range("A" & Rows.Count).End(xlUp).Row + 1[/B] 'finds the next empty row
 
With Worksheets("data").Range("A" & NextRow) 'inserts the data from the user form
     
    .Value = TextBox1.Value 
    .Offset(, 1) = TextBox2.Value 
    .Offset(, 2) = TextBox3.Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

can any one help??

Hello helpful Excel/VBA people!

I have a new issue this year. We are trying to sort a spreadsheet by the data in column I. This column refers to a state. I need help creating a macro that can sort column I so that different states go into different worksheets.

States ME, NH, MA, RI, CT, VT go to a worksheet titled 357899, states NY, NJ would go into worksheet 351835, states MI, IN, OH would go into worksheet 351857, and everything else would go into worksheet 351836. The main data worksheet where the info is being sorted from is named All_Accounts. Column I has a header labeled State, so data actually starts in Row 2. I need the full rows copied to the new worksheets while leaving the main All_Accounts worksheet in tact.

We need to run this on newly imported data each month, so a macro would help greatly.

Anybody have any ideas?

As always, thank you for any help you can provide!

hello this is my first post here on ozgrid forums so here my question.

when adding my data to my worksheet from my userform nothing happens.

what i have is 3 userforms (userform1, 2 & 3) userform1 + 2 take imput from the user and userform3 shows what data has been gathered. userform3 had a commandbutton to upload the data to the worksheet.

here is what code i have in the commandbutton click event on userform3.

	VB:
	
 CommandButton1_Click() 
    If UserForm2.Btype.Caption = "Bus Type Is    Mini Bus" & UserForm2.ComboBox1.Value = "Monday" Then 
        Worksheets(1).Range("E2").Value = UserForm1.Label1.Caption 
    Else 
         'nothing
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
now this dosnt work but it does if i remove this part of the code

	VB:
	
 
    Worksheets(1).Range("E2").Value = UserForm1.Label1.Caption 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
can someone possibly explain why

what i want is data to be added if say label1.caption = 1 and combobox1.text = tuesday.

any ideas

thanks for your help in advance.

i must add that i do code in vb6 language and i thought it would be a doddle but with vba being slightly different im having trouble.

Hi Guys, i hope you can help me.

Im trying to retrieve data from multiple worksheets, into one main spreadsheet.

So say i have a folder "c:tempexcel" and in this folder i have 50 workbooks.

I want to run a piece of code so that when excel will root through all 50 workbooks, take out the relivant criteria, and place into a new worksheet.

Hi everyone, the below code is taken from Contextures, i have added a filter criteria, which filters for anything that begins with OPT and copies the filtered data to worksheet ING. This works perfect, but I want to filter through 15 criterias and then copy each filtered data for each criteria to each worksheet just like the below code, i code use the below code 15 times, but that would slow the macro, any suggestions how the below code code be adjusted?

I will provide you with couple of criterias

Criteria1: OPS, copy to worksheet BOA
Criteria2: CVA, copy to worksheet WCA
Criteria3: MSF, Copy to worksheet NWA

Code:
 
Sub CopyFilter()

Dim rng As Range
Dim rng2 As Range
 
With Range("A5")
    .AutoFilter Field:=1, Criteria1:="=OPT*" _
    , Operator:=xlAnd
End With

With ActiveSheet.AutoFilter.Range
 On Error Resume Next
   Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
       .SpecialCells(xlCellTypeVisible)
 On Error GoTo 0
End With
If rng2 Is Nothing Then
   MsgBox "No data to copy"
Else
   
   Set rng = ActiveSheet.AutoFilter.Range
   rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
     Destination:=Worksheets("ING").Range("A7")
End If
   ActiveSheet.ShowAllData
End Sub


Does anyone know how to reference data on multiple worksheets or all of
the worksheets in the same workbook? I know you can reference all data
in a given column by using A:A for example. How can you do the same
thing with worksheets?

--
ChrisPrather
------------------------------------------------------------------------
ChrisPrather's Profile: http://www.excelforum.com/member.php...o&userid=37743
View this thread: http://www.excelforum.com/showthread...hreadid=573836

How do I display data from multiple worksheets for the same template fields
in one place without physically linking each worksheet's cell to the one
place for each worksheet and cell? I attempted to do this by first putting
the path names to the worksheets in a column. Then I concatenated that
cell's text with the cell position I wanted (ex. $A$3). However, I can't get
Excel to evaluate the contents to display the value in the worksheet's cell
(that is, to link to the value) rather than the path name followed by the
cell name. Any suggestions?

Is there a way to link data from multiple worksheets in the same workbook to
a single worksheet used as a "running total" also in the same workbook?

Hi I am trying create a macro that will copy filtered data from multiple
worksheets into one master worksheet. I have created the following code using
examples from this forum.

Sheets("PRINT - MILL").Select
Set Rng = ActiveSheet.AutoFilter.Range
If Rng.Columns(1).SpecialCells(xlVisible).Count > 1 Then
Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1).Copy _
Destination:=Worksheets(2).Range("A4")
Else
MsgBox "No visible data"
End If
Selection.Copy
Sheets("MASTER PRINT").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(14, 0).Range("A1").Select
Sheets("PRINT - SVR").Select
Set Rng = ActiveSheet.AutoFilter.Range
If Rng.Columns(1).SpecialCells(xlVisible).Count > 1 Then
Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1).Copy _
Destination:=Worksheets("MASTER PRINT").Range("A4")
Else
MsgBox "No visible data"
End If
Selection.Copy
Sheets("MASTER PRINT").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=18
ActiveCell.Offset(11, 0).Range("A1").Select
Sheets("PRINT - BRZ").Select
Set Rng = ActiveSheet.AutoFilter.Range
If Rng.Columns(1).SpecialCells(xlVisible).Count > 1 Then
Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1).Copy _
Destination:=Worksheets("MASTER PRINT").Range("A4")
Else
MsgBox "No visible data"
End If
Selection.Copy
Sheets("MASTER PRINT").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(11, 0).Range("A1").Select
Sheets("PRINT - WHT").Select
Set Rng = ActiveSheet.AutoFilter.Range
If Rng.Columns(1).SpecialCells(xlVisible).Count > 1 Then
Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1).Copy _
Destination:=Worksheets("MASTER PRINT").Range("A4")
Else
MsgBox "No visible data"
End If
Selection.Copy
Sheets("MASTER PRINT").Select
ActiveWindow.SmallScroll Down:=12
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

I am currently experiencing a few problems with this coding. I need it to
paste the data from each sheet on to the master sheet and leave a blank row
in between. Please can you help?

Regards

Paul

I'm having trouble figuring out how to simply take data from multiple
worksheets and list it in one "total" worksheet all within the same workbook.
I want to have this perform automatically as opposed to me using the
copy/paste function and do this manually. All the data will be in the same
layout from page to page. Any help would be appreciated.

Hello,
I've been searching through the forums, but haven't found anything yet.
Does anyone know of a way to copy data from multiple worksheets (i.e. 30) to
one master form? I do NOT want to summarize the data, I want to replicate it
exactly, and have a live link so that if the individual sheets are changed,
the master will update. All column headings are identical, but there are
variable numbers of rows.

My only thoughts so far are simply typing =Sheet1!A2 and dragging, repeating
for each sheet (or I guess writing a macro to do this). However, if I insert
a new row on any of the sheets, it does not update. Any way to do this also?

Thanks in advance!

I am trying to automatically display data from 1 worksheet (Master) to multiple worksheets. For example: Each week I run a report for each business unit in my organization (this is captured on the master). What I would like to do is have a tab designated for each business unit and once each report is run it will automatically separate the business units to each tab. I am a novice when it comes to VBA, but a fast learner. HELP!!!


No luck finding an answer? You could always try Google.