Macro to IF A=x, COPY/PASTE TO sheet1, A=y, sheet2

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
Worksheet 1, columns A to G contain raw data.

Column A only contains a number 1 to 10 inclusively.

I'd like to create a MACRO button that when pressed will do the following:-

IF A = 1, copy paste that row of data to Sheet1,
IF A = 2, copy paste that row of data to Sheet2
etc to A = 10 ..... sheet10.

As far as macro's go ... I'm guessing that this will be a fairly easy task and I'm interested to see the structure of the VBA coding so that I can use something similar in some other macro's that I need to do.

Thanks in advance.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
What range in Worksheet 1 are you going to process?

Where in the other sheets should the pasting occur? Is there some logic to it?
 

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
Hi Glenn,

I'll be putting the raw data in a worksheet called DATA, that way it'll be easier to copy/paste the data if A=1 to Sheet1 etc etc.

Worksheet DATA will be A2:G40000

Thanks ...
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Have you thought about using Advanced filter to split your data instead? I'm sure it would be much faster than copying 1 row at a time.

You can filter to another sheet when doing this within VBA.
 

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176

ADVERTISEMENT

If that is quicker, then that would be the better option. I'd just like the user to click on the Button and all the data appears in the relevant worksheets as this is a daily exercise that they currently do manually.

Thanks for your help.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Are there titles in the range A1:G1? If not, then you need to set some up. You need those same titles in the target sheets.

Put the title that is for column A in cell J1.

Then try this code:
Code:
Sub CopyMyWay()
  For i = 1 to 10
    Range("J2").Value=i
    Sheets("DATA").Range("A1:G40000").AdvancedFilter Action _
        :=xlFilterCopy, CriteriaRange:=Sheets("DATA").Range("J1:J2"), CopyToRange:=ThisWorkbook.Sheets("Sheet" & i).Range("A1:G1"), _
        Unique:=False
  Next
End Sub

P.S. code has not been tested.
 

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176

ADVERTISEMENT

Compile error, variable not defined .... stopping at For i = 1 to 10.

I'll take another look at this tomorrow .... to see if there are any changes I can make ..... cheers
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
You must have Option Explicit, in which case insert
Code:
Dim i as Integer
after the Sub statement.
 

Forum statistics

Threads
1,141,681
Messages
5,707,796
Members
421,529
Latest member
Balintn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top