PDA

View Full Version : EntireRow.Copy Issues VBA



qqtest
10-03-2018, 08:41 AM
Thanks in advance for help, I am new to VBA and really appreciate it!

I have created a workbook that I am hoping will help to organize my clinic's record requests. The plan is to be able to input information (under the "Input" sheet) and to then be able to direct the information to another sheet by making a selection in the "Provider" column (F) or the "Type of Record" Column (G).

If "AG" is selected in column "F" in input sheet> entire row is copied to the "AG" Sheet
If "DS" is selected in column "F" in input sheet> entire row is copied to the "DS" Sheet
If "NL" is selected in column "F" in input sheet > entire row is copied to the "NL" Sheet
If "EH" is selected in column "F" in input sheet> entire row is copied to the "EH" Sheet
If "JH" is selected in column "F" in input sheet> entire row is copied to the "JH" Sheet
If "RW" is selected in column "F" in input sheet> entire row is copied to the "RW" Sheet
If "LS" is selected in column "F" in input sheet> entire row is copied to the "LS" Sheet
If "SP" is selected in column "F" in input sheet> entire row is copied to the "SP" Sheet

If "Clouded Images" is selected in column "G" in input sheet> entire row is copied to the "Rad Cloud" Sheet
If "Faxed Record" is selected in column "G" in input sheet> entire row is copied to the "Faxed" Sheet

I have entered some code that does not seem to work, please let me know what I am doing wrong!

p45cal
10-03-2018, 01:14 PM
cross posted https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1209388-entirerow-copy-issues-vba
See http://www.excelguru.ca/content.php?184

qqtest
10-03-2018, 01:28 PM
I'll be sure to remove my post. Any chance you can help me with my VBA code question?

Paul_Hossler
10-04-2018, 08:47 AM
I'll be sure to remove my post. Any chance you can help me with my VBA code question?

Or at least indicate here and there that you've asked in other forums

Anyways …

I think you vastly over complicated it.

I doubt you really needed event handlers, although you could use them if written correctly

1. This is written as a manually run sub

2. Note that there is no check in the output records for duplicates, so you can append the same record from Input many times

3. You could clear the AG..SP output sheets in the macro before running the copy part of the macro so that what ever is in Input as of macro run time would go to the output sheets cleanly





Option Explicit
Sub CopyRow()
Dim rInput As Range, rRow As Range
Dim F As String, G As String

Set rInput = Worksheets("Input").Cells(1, 1).CurrentRegion

For Each rRow In rInput.Rows
With rRow
.Cells(1, 6).Value = UCase(.Cells(1, 6).Value) ' 6 = col F
.Cells(1, 7).Value = UCase(.Cells(1, 7).Value) ' 7 = col G

F = .Cells(1, 6).Value
G = .Cells(1, 7).Value

Select Case F
Case "AG", "DS", "LS", "NL", "EH", "RW", "RF", "JH", "SP"
.Copy Sheets(F).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Select Case G
Case "CLOUDED IMAGES"
.Copy Sheets("Rad Cloud").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Case "FAXED RECORD"
.Copy Sheets("Faxed").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End Select

End Select
End With
Next
End Sub