Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel
Previous Topic Next Topic

If certain 'type' is in col. K have whole row copy to new sheet [Solved/Closed]

Report
-
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
 -
Hello,

I am wondering if it is possible to have a whole row copy to a designated sheet based off of the value in a column. So for example if my row is A:P and the value in col. K = BIG, have that A:P copy to the sheet called BIG.

Thanks!



2 replies

Answer 1 / 2
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
 230
Hello Dani,

The following code may do the task for you:- 

Option Explicit
Sub TransferData()

Application.ScreenUpdating = False

        Dim ar As Variant
        Dim i As Integer
        
ar = Array("Big", "Little", "Medium")
  
  For i = 0 To UBound(ar)
         Sheets(ar(i)).UsedRange.ClearContents
         Sheet1.Range("K2", Sheet1.Range("K" & Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, ar(i)
         [A2].CurrentRegion.Copy Sheets(ar(i)).Range("A" & Rows.Count).End(3)
         Sheets(ar(i)).Columns.AutoFit
    Next i
[K2].AutoFilter
 
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data transfer completed!", vbExclamation, "Status"

End Sub


It will find the criteria (in this case "Big", "Little", "Medium") in Column K of Sheet1 (which you could name "Master") and transfer the relevant row of data to each individual sheet. The criteria are in an array.

Following is the link to my test work book:-

https://www.dropbox.com/s/hzqz9opl8cl724i/Dani%28Master%20sht%20to%20multi%20shts%2Cautofilter%29.xlsm?dl=0

Click on the button to run the code.

Let us know if its what you were thinking of.

I hope that this helps.

Cheerio,
vcoolio.
1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Answer 2 / 2
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
 230
Hello Dani,

You have marked the thread as solved. Does it suit your needs?

For the sake of possible future visitors to this site/thread, please leave a reply of some sort confirming that it does suit your needs (or otherwise).

Thank you.

Cheerio,
vcoolio.
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!