Macro: Copy entire column into new sheets based on criteria listed in range in another sheet AND also first 4 columns copied onto new sheet

RyndaRaw

New Member
Joined
Jan 10, 2021
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Original Post here: Macro: Copy entire column into new sheets based on criteria listed in range in another sheet (excel 2010) | MrExcel Message Board

Macro should execute on command.

I realized I also need all rows for the description columns (Desc 1 to 4 are Columns A to D) to get returned with the columns (that matched the names from the approved tab).

1st Sheet - Input Sheet
- Input Sheet looks like below and rows go to ~ line 200,000
- it also has ~ 300 names in the columns after the Description

Desc 1Desc 2Desc 3Desc 4Name 1Name 2
RedWideTallPolka

2nd Sheet - Approved Names
This sheet will list names that should be copied to the 3rd sheet

Approved Name
Name 1
Name 4

3rd sheet (where all changes need to be copied to):

Should look like the below.
- Entire column for all Description attributes (Col A to D) should be copied over, starting at cell A1, including the header
- The entire columns with names in row 1, that match those on the approved sheet will be copied over to this sheet, should start after the last description column (in this case starts in column E)


Desc 1Desc 2Desc 3Desc 4Name 1Name 4
RedWideTallPolka
RedWideTallPolkaXX
RedWideTallPolka
RedWideTallPolka
RedWideTallPolka
RedWideTallPolka
RedWideTallPolka
RedWideTallPolkaX
RedWideTallPolka
RedWideTallPolka
RedWideTallPolkaX
RedWideTallPolka
RedWideTallPolka
RedWideTallPolka
RedWideTallPolka

JWhiz Provided this macro but it's not copying the entire description columns to the new sheet. It is copying the approved columns starting in cell B1.

Sub at()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, c As Range
Set sh1 = Sheets("Input")
Set sh2 = Sheets("Approved Name")
Set sh3 = Sheets("Return Approved Names")
With sh1
For Each c In .Range("E1", .Cells(1, Columns.Count).End(xlToLeft))
If Application.CountIf(sh2.Range("A:A"), c.Value) > 0 Then
Intersect(c.EntireColumn, .UsedRange).Copy sh3.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
End If
Next
End With
End Sub
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Watch MrExcel Video

Forum statistics

Threads
1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

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