VB Code to Copy & Paste based on Excel Column Value?

**MissMeliss** · 10-10-2012, 01:30 PM

Hello,
I am needing help with creating a macro to sort a document downloaded from PeopleSoft each morning (read only), and placed into an excel template document based on a column's value. I have attached an example excel document ("MacroHelp.xlsm") with data I am needing to extract.

As you can see in column E ("Method"), there will be either 'ACH' or CHK'.

If column E shows 'ACH', I would like to copy all of the data (Column A through Column G) and place it into a different document already created (see attachment 'T&E - ACH Template.xls').

If column E shows 'CHK', I would like to copy all of the data (Column A through Column G) and place it into a different document already created (see attachment 'T&E - Check Template.xls').

These templates are reused everday. Meaning, once these templates are filled in correctly with their data, I print and close without saving.

Thanks in advance for any help you can offer!

**mike7952** · 10-10-2012, 02:35 PM

Heres an example. All 3 workboks must be in the same folder. Download and put all three workbooks into the same folder.

MacroHelp v1.xlsm

**MissMeliss** · 10-10-2012, 05:02 PM

Mike,
That worked beautifully - many thanks!

**xladept** · 10-10-2012, 03:42 PM

Hi MissMeliss,

You may have to tweak the Source Book Name:

Sub DisperseData(): Dim i As Long, j As Long, k As Long
Dim wd As Worksheet, wc As Worksheet, wa As Worksheet
Set wd = Workbooks("Macro Help").Worksheets("Sheet1")
Set wc = Workbooks("T & E - Check Template.xls").Worksheets("Sheet1")
Set wa = Workbooks("T & E - ACH Template.xls").Worksheets("Sheet1")
For i = 3 To wd.Range("E" & Rows.count).End(xlUp).row
If wd.Range("E" & i) = "CHK" Then
j = wc.Range("E" & Rows.count).End(xlUp).row + 1
wd.Range("A" & i & ":G" & i).Copy wc.Range("B" & j)
ElseIf wd.Range("E" & i) = "ACH" Then
k = wa.Range("E" & Rows.count).End(xlUp).row + 1
wd.Range("A" & i & ":G" & i).Copy wa.Range("B" & k)
End If: Next i: End Sub