Copy data from on sheet to another

queysoft

New Member
Joined
Aug 3, 2021
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
Hello again. I'm finding this forum so useful when I can't solve my own issues using Google search!

So - this time I have the following.

Sheet 1 = Lots of column, different data in each column. Row's being added all the time (and populating each row with new data).

What I want in sheet 2 is.......

Copy over only a select couple of columns BASED on criteria in a certain column. So, for example - I want to copy to sheet 2 all the information in column B / D / F from sheet 1 where column A has the word APPLE in it. This will automatically populate as more info is added to sheet 1.

Basically, sheet 2 is a sub selection (or a filter if you will) of the information in sheet 1.

Any idea?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,202
Office Version
  1. 365
Platform
  1. Windows
Hello Queysoft,

Perhaps the following can be a starting point for you:-
VBA Code:
Sub Test()

Application.ScreenUpdating = False
        
        With Sheet1.[A1].CurrentRegion
                .AutoFilter 1, "Apple"
                Union(.Columns("B"), .Columns("D"), .Columns("F")).Offset(1).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
                .Offset(1).EntireRow.Delete
                .AutoFilter
        End With

Application.ScreenUpdating = True

End Sub

The code assumes that, in Sheet1, the data starts in row2 with headings in row1.
It also deletes the relevant rows of data from Sheet1 once the data has been transferred to Sheet2.

I hope that this helps.

Cheerio,
vcoolio.
 

queysoft

New Member
Joined
Aug 3, 2021
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
Hi - Sorry, which part of this code refers to the row and the headers? My data starts in row 3 and headers in row 2. I can make the changes if i know which part of the code this refers to - I think i can guess as there is not much to it - but just in case?
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,202
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hello Queysoft,

In that case, the code will look like this:-

VBA Code:
Sub Test()

Application.ScreenUpdating = False
        
        With Sheet1.Range("A2", Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp))
                .AutoFilter 1, "Apple"
                Union(.Columns("B"), .Columns("D"), .Columns("F")).Offset(1).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
                .Offset(1).EntireRow.Delete
                .AutoFilter
        End With

Application.ScreenUpdating = True

End Sub

Cheerio,
vcoolio.
 

queysoft

New Member
Joined
Aug 3, 2021
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
This seems to do the job very well thank you! If i don't want to delete the relevant rows from sheet one can I just remove the line .Offset(1).EntireRow.Delete or does that need to read something else?
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,202
Office Version
  1. 365
Platform
  1. Windows
That's correct but if you remove that line, you'll end up with duplication in Sheet2. Hence, if you don't want to delete the relevant rows of data from Sheet1, to prevent this duplication in Sheet2, this slight modification should do the task for you:-

VBA Code:
Sub Test()

Application.ScreenUpdating = False
       
        Sheet2.UsedRange.Offset(1).Clear
        
        With Sheet1.Range("A2", Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp))
                .AutoFilter 1, "Apple"
                Union(.Columns("B"), .Columns("D"), .Columns("F")).Offset(1).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
                .AutoFilter
        End With

Application.ScreenUpdating = True

End Sub

It will basically refresh the data in Sheet2 with old and new data each time the code is run.

I'm assuming that the headings in Sheet2 are in row1.

Cheerio,
vcoolio.
 

queysoft

New Member
Joined
Aug 3, 2021
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
Correct yes they are for sheet 2......Ok - meeting time then will give this a go later today or tomorrow AM. Thank you again!!
 

Forum statistics

Threads
1,147,518
Messages
5,741,636
Members
423,674
Latest member
Charles2dodo

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