Sub varoiustasks()

folder1 = "C:\files for me"
folder2 = "C:\files for me\new template"

ChDir folder1

Set Newbk = Workbooks.Add
Newbk.Sheets("Sheet1").Name = "Summary1"
Set NewbkS1 = Newbk.Sheets("Summary1")
Newbk.Sheets("Sheet2").Name = "Summary2"
Set NewbkS2 = Newbk.Sheets("Summary2")

FiletoOpen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")

Workbooks.Open Filename:=FiletoOpen
Set oldbk = ActiveWorkbook

With oldbk.Sheets("YY")
.Columns("A:E").Copy Destination:= _
NewbkSh1.Columns("A")
.Columns("G:H").Copy Destination:= _
NewbkSh1.Columns("F")
End With

With NewbkSh1
Set C = .Columns("E").Find(what:="info req", _
LookIn:=xlvlaues, lookat:=xlWhole)
If Not C Is Nothing Then
NewRow = C.Row
Rows(NewRow).Insert
Range("E" & NewRow).Font.Bold = True
End If
Set C = .Columns("E").Find(what:="outstanding", _
LookIn:=xlvlaues, lookat:=xlWhole)
If Not C Is Nothing Then
NewRow = C.Row
Rows(NewRow).Insert
Range("E" & NewRow).Font.Bold = True
End If
End With


oldbk.Close

ChDir folder2
FiletoOpen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")

Workbooks.Open Filename:=FiletoOpen
Set oldbk = ActiveWorkbook

With oldbk.Sheets("ZZ")
.Columns("F:O").Copy Destination:= _
NewbkSh2.Columns("A")
.Columns("Y:Z").Copy Destination:= _
NewbkSh2.Columns("K")
End With

With NewbkSh1
.Columns("A:A").AutoFilter
End With

oldbk.Close


fileSaveName = Application.GetSaveAsFilename( _
"New Data.xls", _
fileFilter:="Excel Files (*.xls), *.xls")

Newbk.SaveAs Filename:=fileSaveName

End Sub

"Gemz" wrote:

Hi,

I posted a similar post but cannot remember the subject so couldnt re-post
there, please discard other one and see below as this is amended query:

I need a macro to do several tasks for me, i wonder if its possible..

-select columns A-E, G,H from file A, TAB 'YY' in location C:\files for
me\summary 14.2.08 (date changes all the time meaning the file name will not
remain constant)
-paste these into new workbook and call this sheet summary1 and call file
new data.
-select columns F-O, Y, Z from file B, TAB 'ZZ' in location C:\files for
me\new template\summary 14.2.08 (again date changes all the time meaning the
file name will not remain constant)
-paste these into same workbook as above but in a seperate sheet and call it
summary2
-then in sheet summary 1 when first cell in column E = 'info req' insert a
line above this and put a bold heading there called 'info req'
-again in the same sheet when first cell in column E = 'outstanding' then
insert a line above this and put a bold heading there and call it
'outstanding'.
I am doing this because the file is quite big so would like to insert
headers all the way down to divide info out -unelss there is a better way of
doing this.
-finally in summary 2 sheet, i would just like to put a filter on coulumn A
(user can pick criteria manually later) and then just colour column D blue
and all column headings Red.

Is there a way i can write all this in a macro? if steps 1 and 2 arent
possible because the filename will change and it is tab specific can i have
the other steps please?

really appreciate all your help.