PDA

View Full Version : Data transfer based on certain findings



swfred
10-16-2012, 07:12 PM
Hello,

I don't know anything about VBA coding, but I have been using excel for many years. I mostly use it for quantitative analysis and for making sheets for calculations, etc.

I'm currently working on a workbook that has multiple sheets with the same layout.

I want to make it so that I can have a separate worksheet that has a different layout but will respond to my entering the name of other worksheets in the workbook and will search that chosen worksheet for various indicators and copy/paste items that correspond to what the sheet searches for.

I'd like it to work so that I enter the name of the worksheet and it autopopulates that sheet with the information gathered from the sheet that I choose. I'd also like to be able to use that code for multiple sheets so that all the results will be on the same worksheet.


---------------------------------------------------------------------

Spreadsheet name: sheet5
If A1(on sheet5)=x, copy B1(on sheet5) and E1(on sheet5)
If B1(on sheet5)<>x, ignore and skip to C1(on sheet5)

etc


Also, I'll want to indiscriminately copy/paste various things from the supplied spreadsheet (in this case sheet5) to certain cells in the new sheet that will be pulled from the supplied sheet the moment I fill in a the cell requesting the name of the sheet.

I can send the actual excel file if needed so that it's easier to visualize what I'm looking for, but I'd rather not post it since it has information I'd rather not be out there for just anyone looking.

Is this possible, and is someone able to help me with it? Dunno if these requests are really what this board is for, or if it's just to get some pointers in the right direction for people stuck on a certain line of code.

I'd love to have the code and for it to be explained so that I can fiddle with it if I want to. It's a bit of a learning experience for me.

Thanks

GTO
10-17-2012, 05:01 AM
Greetings swfred,

Welcome to vbaexpress. :-)

Please note the site's motto, "Bringing VBA to the world". Certainly we'd like to help you learn VBA. Like anything, it can sometimes be a frustration, but fortitude and the desire to learn will result in doing "things" one wouldn't imagine as possible.

I would suggest putting together a simplified version of your wb, with two sheets of fake data, your output sheet, and the output sheet filled in as desired. This will make your explanation easier to accurately understand. Save the wb in .xls format (as this can get more "answerers" involved), and expect to learn along the way. I am sure this will get you great help and a fine start in vba.

Mark

swfred
10-17-2012, 09:12 AM
Thanks for the response. Any help will be appreciated.

I've attached a copy of my workbook that is share-friendly with just a few sheets rather than the large amount I have.

I've written what I'm looking for in one of the sheets.

Basically, I make copies of "Blank" and choose a few options which fill in the new sheet according to my choices. I call the new sheets by a number. I'd like to be able to input the sheet's number on the page called "List" and have it pull certain information from that sheet and fill it in based on the choices I made. All of it is outlined in the workbook with examples that I filled in by typing rather than code just so you can see what my hopeful end result will be with much less work.

Thanks again.

GTO
10-19-2012, 01:35 PM
Greetings swfred,

I looked at the workbook and started writing a bit of code to get you started, after only really studying the 'Desired Output, Etc' sheet. After getting the below written, I (small screened laptop at home and had teh screen split) scrolled down the output sheet 'List' and realized that you actually want up to three records (at least I currently presume this, if the 'List' sheet is to be able to accomodate more than three records please advise).

There appears to me to be a glitch.

There appear to be up to 17 pre-anesthesia/pre-op/anesthetic drugs. Hopefully I am missing some bit of logic, as maybe, let us say under the pre-ansthetic drugs, there are limitations as to "one of these two drugs might be administered, but not both."

That is to say, that without some non obvious limitation, if, up to 17 drugs could actually be used, then the report only having room for 8 would seem a problem. Could you clarify this a bit?

I am totally guessing at this point, but if there is a "one of these two, but not both" or "one of these three" or whatever limitation(s) built in, maybe color code the the cells just for demonstration purposes.

Anyways, just to demonstrate the first little bit, this would fill in the first pet's Date, Patient (summary) and weight information on the 'List' sheet, upon entering the 'Pet ID#' number in cell B2 (B2:C2 are merged) of 'List'.

In the Worksheet Module for 'List':

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address(False, False, xlA1, False) = "B2" Then
UpdateList Me, Target.Value
End If
Application.EnableEvents = True
End Sub

In a Standard Module:

Option Explicit

Function UpdateList(wks As Worksheet, PetID As Variant)
Dim wksRecord As Worksheet
Dim bolBadWeight As Boolean

If Not WorksheetExists(PetID) Then
MsgBox "Worksheet does not exist...", vbInformation, "Exiting"
wks.Range("B2").Value = vbNullString
Exit Function
Else
Set wksRecord = ThisWorkbook.Worksheets(PetID)
End If
With wks
.Range("B1").NumberFormat = "mmmm dd, yyyy"
.Range("B1").Value = wksRecord.Range("C2").Value
.Range("B3").Value = wksRecord.Range("S3").Value & _
Chr(32) & wksRecord.Range("U2").Value & " - " & _
wksRecord.Range("AA5").Value & "Y" & Chr(32) & _
wksRecord.Range("AC5").Value & "M" & _
Chr(32) & wksRecord.Range("V5").Value & Chr(32) & _
wksRecord.Range("X4").Value

If IsNumeric(wksRecord.Range("A8").Value) Then
If wksRecord.Range("A8").Value > 0 Then
.Range("B4").Value = _
wksRecord.Range("A8").Value & " lbs / " & _
Round(wksRecord.Range("D8").Value, 1) & " kgs"
Else
bolBadWeight = True
End If
Else
bolBadWeight = True
End If
If bolBadWeight Then .Range("B4").Value = "0 lbs / 0 kgs"
End With
End Function

Function WorksheetExists(ByVal WorksheetName As String) As Boolean

On Error Resume Next
WorksheetExists = ThisWorkbook.Worksheets(WorksheetName).Name = WorksheetName
On Error GoTo 0
End Function

BTW, you have a bunch of merged cells, both in the individual patients' sheets and in the 'List' sheet. I tend to avoid these like the plague, mostly because they are a giant PITA. In VBE (the windows you are looking at when writing code), go to the menu bar, select Tools|Options. With the Editor tab selected, tick the Require Variable Declaration checkbox. Whenever you create a new module, Option Explicit will be automatically inserted at the top. Read the VBA Help topic, but in short, this is the closest thing to SpellCheck for code, as it will catch you when you mispell some variable, as well as insist that you properly declare variables.

Mark

swfred
10-20-2012, 04:11 PM
Sorry for not clarifying.

The absolute most that will ever be used in conjunction from the list of pre anesthetics will be 3. The rest are subject to variation.

However, the most there will ever be will be 12. I didn't allow for that in the Desired Output sheet because I put the Diagnostics on the first available line. If the Diagnostics line is on the last line available in each of the 3 sections, that leaves 14 slots for entries of chosen items.

Does that make sense?

Thanks a ton for your assistance.