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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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?
 
Upvote 0
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 ...
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You must have Option Explicit, in which case insert
Code:
Dim i as Integer
after the Sub statement.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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
Back
Top