CODEWITHSUNDEEP
vbaexcel
Carl O' Beirne
Carl O' Beirne

Reputation: 71

How to add certain records to certain worksheets using VBA

I have written a VBA user form to populate an excel sheet with records with computer information. I also have 4 different worksheets, one for each branch that are listed in a combo box.

I was wondering if it is possible to write an if statement that will put the records into the correct worksheets accordingly.

I currently have an If statement that will insert it into branch "SNN" but I was wondering if I could write the statement similar to below

If branch = worksheet name then insert the record into that worksheet.

This is what I have currently working but there are too many branches to have to do this code for each one individually

Private Sub submitBtn_Click()

Dim wsDub, wsORK, wsSNN, wsBFS As Worksheet
Set wsDub = ThisWorkbook.Sheets("DUB")
Set wsORK = ThisWorkbook.Sheets("ORK")
Set wsSNN = ThisWorkbook.Sheets("SNN")
Set wsBFS = ThisWorkbook.Sheets("BFS")

If newPcForm.cmbBranch.Value = "SNN" Then

mynr = wsSNN.Cells(Rows.Count, 1).End(xlUp).Row + 1

wsSNN.Cells(mynr, 1) = newPcForm.cmbBranch
wsSNN.Cells(mynr, 2) = newPcForm.pcname
wsSNN.Cells(mynr, 3) = newPcForm.cmbPcModel
wsSNN.Cells(mynr, 4) = newPcForm.cmbCPU
wsSNN.Cells(mynr, 5) = newPcForm.cmbDiskSpace
wsSNN.Cells(mynr, 6) = newPcForm.cmbRAM
wsSNN.Cells(mynr, 7) = newPcForm.cmbMonth + "-" + newPcForm.cmbYear
wsSNN.Cells(mynr, 8) = newPcForm.assetTag
wsSNN.Cells(mynr, 9) = newPcForm.serviceTag

End If


newPcForm.Hide

End Sub

Would it be possible to do something like this or would a better solution be to put them all into one and apply a filter on the headings.

Any help is appreciated.

Upvotes: 4

Views: 56

Answers (1)

ashleedawg
ashleedawg

Reputation: 21639

Absolutely All-in-one...

I'm glad that you recognized that as an option; some people like to fight that suggestion because, frankly, change can be scary. It's always best to eliminate duplicates, and there are more advantages than I can name to having it all in one document. (Perhaps other will chime in on this?)

In my opinion, ideally this would be a good time to move your data to MS Access, since you're in the process of reorganizing anyway. You're begun to use as a database, which will probably work fine, but there is so much more you can do with Access, not to mention that it's much better suited to multi-user environments, and is much more stable as compared to a huge worksheet. ...but if not, having all your data in one sheet will prevent you have having to write repetitious code that could make it exponentially hard to make changes or track down problems in the future.

If for some reason you decide that you like having the data on individual worksheets for reasons of presentation or interaction, it can still easily be accomplished with a "faux front" type of setup, where the users only think that the data's split onto multiple sheets while it's actually nicely organized into one hidden sheet -- plus, in that case, if they accidentally mess with the data, nothing is lost.

Upvotes: 3

Related Questions