I am a trainee dermatologist. We undertake allergy patch tests. There is a long list of different patch tests which are selected based on the patients history. I have put each of the types of patch testing on a master sheet. What I would like to be able to do is to have a control button next to each set, and when clicked would add that particular set to a "new patient" worksheet tab. And with every set having its own button, more than one set could be added to a new sheet. In the new patient worksheet id like the sets added with a single row gap between them.
I had tried, and managed to get a single series to work, but then it wouldnt allow me to add the next set, saying that sheet already exists. Ideally finally, id like there to be some way, or message to make sure after printing the new patient worksheet gets deleted.
I hope that all makes sense ! Even if I had a code for a single button that added to a master sheet at the next available row +1 I think I could manipulate the code to suit.
Thanks
Grant
Copy Named Range Chosen From Drop-Down & Paste To Last Row
-
-
Re: Multisheet Copy Multiple Rows
Give the attached a try. It is only a rough start to get you pointed in a direction.
Named Ranges are used for the "sets" and a cell with Data Validation with the list option is used as a selector cell to choose which set to copy.The Select Case method is used to test which set is selected for copying.
A Forms button is used, with macro assigned, to call the macro.
There is also button with code assigned to print the sheet and clear it.This is not "robust" since there is no error checking or tests to see if you are copying duplicate data. There is likely a better approach than Select Case if you have a lot of "sets" to copy.
Code
Display MoreSub CopySet() Application.ScreenUpdating = False With Sheet1.Range("B1") Select Case .Value Case Is = "Set_1" Range("Set_1").Copy _ Destination:=Sheet2.Range("A100").End(xlUp).Offset(2, 0) Case Is = "Set_2" Range("Set_2").Copy _ Destination:=Sheet2.Range("A100").End(xlUp).Offset(2, 0) Case Is = "Set_3" Range("Set_3").Copy _ Destination:=Sheet2.Range("A100").End(xlUp).Offset(2, 0) End Select End With Application.ScreenUpdating = True End Sub Sub Print_Clear() Application.ScreenUpdating = False With Sheet2 .PrintOut MsgBox "The patient sheet will now be cleared" Sheet2.UsedRange.ClearContents End With Application.ScreenUpdating = True End Sub -
Re: Copy Named Range Chosen From Drop-Down & Paste To Last Row
Great ! Thats along the lines of what i was thinking. If possible, what i would like to do , is have a button next to each of the sets and when you click on that button the data copies and pastes to the "new patient" worksheet, and then the next would copy and paste with a space row in between. Even if I had a suitable code for one or two ( I could modify for the rest of the sets...as there is quite a few !) Attached is the master list with non functioning buttons as examples. The final button would be the print and delete button as before.
I am very grateful for your prompt reply
Thanks
Grant -
Re: Copy Named Range Chosen From Drop-Down & Paste To Last Row
Try the attached and see if this doesn't get you started on an alternative solution; it just hides the rows you don't want printed, prints (previews just for now), and unhides them again.
I've added a userform and a listbox, this code behind the userform:this code behind the Print button on the sheet:
and this code in a standard module:
Code
Display MoreWith UserForm1 .ListBox1.List = Array("27 Hairdressing", "41 Vehicles", "40 Fragrance Series ", "42 Steroids", "25 Dental Battery", " Plant Series", "25 Dental Materials (Dyes)", "44 Clothing and Footwear", "35 Sunscreen agents", "26 Plastics (Prosthetics)", "26 Plastics and Glues", "Leg Ulcer", "40 Perfumes and Flavours") .Show Dim r As Range For i = 0 To .ListBox1.ListCount - 1 If Not .ListBox1.Selected(i) Then Debug.Print .ListBox1.List(i) Set r = Sheets("Master").Columns(1).Find(.ListBox1.List(i)) If Not r Is Nothing Then r.CurrentRegion.EntireRow.Resize(r.CurrentRegion.Rows.Count + 1).Hidden = True End If End If Next i End With ActiveWindow.SelectedSheets.PrintPreview Sheets("Master").Cells.EntireRow.Hidden = False Unload Userform1 End Sub -
Re: Copy Named Range Chosen From Drop-Down & Paste To Last Row
You could automate AdvancedFilter as well to hide rows you don't want printed.
-
-
Re: Copy Named Range Chosen From Drop-Down & Paste To Last Row
P45cal - that has worked a treat ! Thanks so much.
The only slight niggle that I could easily live with, is whether it would be possible that none of the "sets" get split by a page break and would get forced onto a new page. Have a look at the attched which I have adapted slightly. Depending on which sets the user requests , sometimes tehy are quite long, but ideally I would like them appear all on the one sheet. See what you think
Cheers ! -
Re: Copy Named Range Chosen From Drop-Down & Paste To Last Row
Oh no , Ive just tried it again and its not working correctly. It doesnt seem to print preview the selected items from the userform, it is print previewing some items but in a random way.
I did try to rename the userform, perhaps that has affected things. Please have a look, as otherwise it was going great !
Grant -
Re: Copy Named Range Chosen From Drop-Down & Paste To Last Row
Attached updated worksheet
-
Re: Copy Named Range Chosen From Drop-Down & Paste To Last Row
Yes, I saw the effect your changing the userform name did - corrected in the attached. There were also several minor errors, you had leg ulcers in twice on the sheet (each header has to be unique), you misspelt one of them in the code, all corrected.
Page breaks; instead of print preview I've moved to just leaving the sheet in page break preview mode when it's finished. I've abandoned putting all rows back to visible at the end - you don't need to see them at all anyway, and I moved the button so that it can never be hidden - it doesn't matter too much where it is since it's not printed.
So now, when you've chosen what you want printed and the macro does its stuff, working from top to bottom, you manually drag the page breaks which come in the middle of a table upwards, to line up with the top of that table. Then Print or Print Preview manually. Depending on how many of these you have to do, it may not be worth automating this.
You can leave the sheet as it is (you don't even have to savethe file).
Be patient, it's quite slow.
I'm not sure about the colours.. -
Re: Copy Named Range Chosen From Drop-Down & Paste To Last Row
Youre a genius ! That works perfectly. The colours werent my choice...honest.
I did quite like to see the master list from the outset...could that be reinstated ?
Otherwise its great. I will no doubt ned to use it in action to see how things go.
Cheers.
Grant -
-
Re: Copy Named Range Chosen From Drop-Down & Paste To Last Row
Infact, just as I try it....its perfect as it is. So I would aim to leave as is. If I ever had to edit any entries or add a new series...would that be straight forward ?
-
Re: Copy Named Range Chosen From Drop-Down & Paste To Last Row
right click on the Master sheet's tab and choose 'View Code'. When the vbe shows up paste this into it:
CodePrivate Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True Cells.EntireRow.Hidden = False End Subclose the vbe and when you doubleclick the sheet all the rows will be unhidden.
..or just click the button and check all the checkboxes.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!