Macros Help

[tjg]

Hi,

I am pretty new to VBA and am struggling in writing a simple code.

I'm trying to write a code that locates columns with the headings 'Platform' , 'Complex' , 'Field' and 'Area' and then copy the entire column (Columns may be in different cells and different lengths each time), into a newly created worksheet. And then add two new columns titles called 'TPL' and 'OPAL'.

This is what I have so far, it copies one column but i cannot get it to copy the others:

Sub Copy_Column()
    Dim rng As Range
    Dim cl As Object
    Dim strMatch As String
 
    strMatch = "Platform"
    Set rng = Range("A1:ZZ1")
    For Each cl In rng
        If cl.Value = strMatch Then
            cl.EntireColumn.Copy
 
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
       Exit For
       End If
    Next cl
End Sub

Any help would be much appreciated.

 

[Deepak]

Hi,

Welcome to the board! On behalf of forum thanks for the query.


Do you want to create new sheet for each word in Platform' , 'Complex' , 'Field' and 'Area' or just copy these matching columns in a new sheet?

 

[Marc L]

Hi !

Using an advanced filter (Excel basics) does not need to locate headers
and avoid a gas factory code with few codelines like this demonstration :​

Sub Demo()
With Worksheets.Add(, Worksheets.Count)
    .[A1:F1].Value = Split("Platform Complex Field Area TPL OPAL")
    Sheet1.UsedRange.AdvancedFilter xlFilterCopy, , .[A1:D1]
End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[tjg]

Hi Marc

When I run your code I get a run-time error 1004.

The four columns I need to located and copy to a new sheet, are not located in cells A1:D1. The worksheet has many column titles and in each workbook, the four columns i need to copy are generally located in different places.

 

[Marc L]

A1:D1 is the range in destination worksheet ‼

Read Excel help of using an advanced filter
as well AdvancedFilter VBA help …
Excel basics at beginner level : a classroom of 10/11 years kids
have succeeded to use it as well a maybe 70 years old grandma !
Excel : from 7 to 77 ‼

Replace Sheet1 CodeName ¤ by a reference to source worksheet …
(whatever Worksheets(1) or using its name !)

¤ :

Sheet1 is the CodeName of worksheet named "abc" …
​