New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Need VBA Code to Insert Row and Paste Text

Macros, VBA, Excel Automation, etc.

Need VBA Code to Insert Row and Paste Text

Postby ajricoh » Fri Dec 16, 2011 9:26 am

New to VBA and struggling...
Here is what I need:
If text in row 1, column G (Shot2), then insert row below and copy text from row 1, columns A thru C (Patient1, Name, Demographic) to Row 2, columns A thru C, cut text from row 1, column G thru column I and paste to Row 2, columns D thru F

Here's a graphical representation of the starting layout:
Patient1, Name, Demographic, Shot1, Record, MMDDYYYY, Shot2, Record, MMDDYYYY, Shot3, Record, MMDDYYYY
Patient2, Name, Demographic, Shot1, Record, MMDDYYYY
Patient3, Name, Demographic, Shot1, Record, MMDDYYYY, Shot2, Record, MMDDYYYY

Here's what I need (A single shot record per row with patient info repeated until the next new patient):
Patient1, Name, Demographic, Shot1, Record, YYYYMMDD
Patient1, Name, Demographic, Shot2, Record, YYYYMMDD
Patient1, Name, Demographic, Shot3, Record, YYYYMMDD
Patient2, Name, Demographic, Shot1, Record, YYYYMMDD
Patient3, Name, Demographic, Shot1, Record, YYYYMMDD
Patient3, Name, Demographic, Shot2, Record, YYYYMMDD

I'm sure for experienced VBAers this is simple code but for a newbie, it's an hours long, frustrating, self-teaching endeavor. Many, many thanks in advance!
  • 0

ajricoh
Rookie
 
Posts: 2
Joined: Dec 16, 2011
Reputation: 0

Re: Need VBA Code to Insert Row and Paste Text

Postby lucky027 » Sat Dec 17, 2011 9:41 am

try this in a thisworkbook or module code
assume that the fist record is in row 1 else change line = ? the first record you want check

Code: Select all
Sub Macro_ajricoh()
Dim line
line = 1
1 If Cells(line, 1) <> "" Then
    If Cells(line, 7) <> "" Then
    Rows(line + 1).Insert Shift:=xlDown
    Range("A" & line & ":C" & line).Copy Destination:=Range("A" & line + 1)
    Range(Range("G" & line), Range("G" & line).End(xlToRight)).Cut Destination:=Range("D" & line + 1)
    GoTo 1
    End If
line = line + 1
GoTo 1
End If
End Sub


Hope that help you
HAve a nice day
  • 0

lucky027
Rookie
 
Posts: 10
Joined: Dec 7, 2011
Reputation: 0

Re: Need VBA Code to Insert Row and Paste Text

Postby ajricoh » Mon Dec 19, 2011 10:10 am

Thank you, thank you, thank you!!
  • 0

ajricoh
Rookie
 
Posts: 2
Joined: Dec 16, 2011
Reputation: 0


Return to Macros and VBA Questions

Who is online

Users browsing this forum: Google [Bot] and 24 guests