Macro to copy array data if condition is met

Microsoft Excel 2007
October 23, 2009 at 07:02:41
Specs: Windows Vista
I'm trying to create a macro that will copy data from on tab into 2 different tabs depending on the data value (0 or 1) in a column.

Specifically, i want to copy applicable Columns "A:L" from tab: DATA to tab:Planning if the data in column "M" is 0.

I want to copy applicable Columns "A:L " from tab: DATA to tab OnSite if the data in column "M" is 1.

Help!


See More: Macro to copy array data if condition is met

Report •


#1
October 23, 2009 at 07:10:25
Copy can mean different things.

Do you want an actual copy of the data values, or are you willing to accept a formula that will display the data in each tab based on the value in column M.

A simple =IF(Data!M1=0,Data!A1,"") style formula will do display the values (or not) depending on the value in Data!M1.

Are you looking for something more "elegant" than a formula based solution?


Report •

#2
October 23, 2009 at 07:34:35
Thank you for your response!

But, yes, I am looking for something more elegant. This is intended to be a regular process until we get proper reporting stood up; so i'm trying to execute this effort through a macro attached to a "button."


Report •

#3
October 23, 2009 at 08:46:52
re: This is intended to be a regular process until we get proper reporting stood up;

Then we are going to need some more detail.

We would need to know what ranges need to be copied and where they need to be copied to. For example...

Is all data from Data!A:L being copied as a block or is it dependent on each Row of Column M being 0 or 1?

If it's row by row, do you want the results in sequential rows or is it a Row X to Row X copy?

Will some of the data be copied to OnSite and some copied to Planning each time the button is pressed?

Are you overwriting existing data or appending to the bottom of existing data?

Is there any other data on the OnSite and Planning tabs that we need to be concerned with? i.e. can we clear the whole sheet before copying the new data or are there header rows, labels, etc. that need to remain?

This are the types of details that would help us provide the best soultion.


Report •

Related Solutions

#4
October 27, 2009 at 11:39:34
Just for future reference I figured out a macro solution :)

Dim i, j, n, m As Integer
Dim srng, d1rng, d2rng As Range

Set srng = Sheet5.Range("P:AB")
Set d1rng = Sheet9.Range("A:M")
Set d2rng = Sheet8.Range("A:M")

n = 3
m = 3

For i = 3 To 20
If srng(i, 14).Value = "0" Then
For j = 1 To srng.Columns.Count - 1
d1rng(n, j).Value = srng(i, j).Value
Next j
n = n + 1
ElseIf srng(i, 14).Value = "1" Then
For j = 1 To srng.Columns.Count - 1
d2rng(m, j).Value = srng(i, j).Value
Next j
m = m + 1
End If
Next i

that takes the data from a single sheet and uses the differentiator to separate the data. Thank you for your response and help!


Report •


Ask Question