Macro copying data from worksheet from certain columns within same workbook

[jleone]

I'm trying to copy data from a "DATA" worksheet within my main workbook. The data I'm trying to get is from only certain columns by name (i.e, "OH" column, "Trigger" column, "Max Inv" Column, etc.) On the "DATA" sheet I want to be able to find the column and select all of the data below (from row 2 until the end of the data) and copy this information.

Since this data is updated daily, the length of the rows for the data varies. I want to be able to grab the data from that day for each of the columns and paste them into a corresponding seperate sheet, where each sheet holds daily information for one column only (where this is a "OH" sheet, "Trigger" sheet, etc., where within these sheets the dates go from left (most recent) to right in the Columns for that sheet, AKA I would need to insert a column before the most recent data column)

I know this may confuse you, as you are not fully aware of the project I'm working on, but any help will be greatly appreciated.

 

[venkat1926]

try this macro (modify if necessary)

Sub test()
Dim x(1 To 3) As String, cfind As Range, j As Integer
x(1) = "OH"
x(2) = "trigger"
x(3) = "max inv"
For j = 1 To 3
With Worksheets("main")
Set cfind = .Cells.Find(what:=x(j), lookat:=xlWhole)
If Not cfind Is Nothing Then
Range(cfind.Offset(1, 0), cfind.End(xlDown)).Copy
Worksheets.Add
ActiveSheet.Name = x(j)
ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End If
End With
Next j
End Sub