Copy Paste between WorkBook

[imfarhan]

Hi All,
I would like to open!copy (xl workbook) from selected columns values and paste(UPDATE) into another excel workbook.
Update(The secondfile can be clear all)
I have following function using in my Ms Access application.

Problem:
1) How can I select the whole column from FirstFile(Source) in the given
VBA code ?
2) If the SecondFile is blank its paste ok but If the value how can I clear whole sheet and paste the value from sourcee file?
3) How can I select the other columns (E,F, and W) in the given functions
Thanks for your help
Farhan

[INDENT]Option Compare Database
Option Explicit
Sub CopyXL()
 
Dim wbk As Workbook
Dim strFirstFile  As String  ' This file changes every week with new datestamp(Suffix)
Dim strSecondFile  As String ' This will be constant file
strFirstFile = "T:\Info_Team\Farhan\Reports\Readmission audit report\PBR ReAdmission Audit Report - 20110920 1406.xls"
'strSecondFile = "T:\Info_Team\Farhan\Reports\Readmission audit report\Readmission audit JT access upload do not touch.xls"
strSecondFile = "T:\Info_Team\Farhan\Reports\Readmission audit report\book2.xls" 'Readmission audit JT access upload do not touch.xls"
 
'-----Copy-----
Set wbk = Workbooks.Open(strFirstFile)
'With wbk.Sheets("sheet1")
With wbk.Sheets("Audit_Sheet_for_Input")
    If Range("A6").Value = "AuditFlag" Then
    Range("A6:A1000").Copy ' How can I copy the whole column value?
     Cells.Select
     'Selection.
    End If
 
End With
[/INDENT]'--------Paste---
Set wbk = Workbooks.Open(strSecondFile)
'With wbk.Sheets("sheet2")
With wbk.Sheets("JT access upload do not touch")
    Range("A1:A1000").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
      False, Transpose:=False
      wb.Saved
      wbk.Close
 
  End With
End Sub

 

[imfarhan]

I think I have used the following code and its working sofar but the source file name get change every week. how can I capture teh source file
the name is save but date/time (suffix) get change every week when the file update or Is there any way I can capture the last file create using VBA dos command:-

Sub Farhancopy()
Dim wbk1 As Workbook
Dim wbk2 As Workbook
Dim destinationFile As String
Const sourceFile As String = "T:\Info_Team\Farhan\Reports\Readmission audit report\PBR ReAdmission Audit Report - 20110920 1406.xls"
destinationFile = "T:\Info_Team\Farhan\Reports\Readmission audit report\book2.xls"
Set wbk1 = Workbooks.Open(sourceFile)
Set wbk2 = Workbooks.Open(destinationFile)
wbk1.Sheets("Audit_Sheet_for_Input").Range("E:E").Copy Destination:=wbk2.Sheets("JT access upload do not touch").Range("A1")
wbk1.Sheets("Audit_Sheet_for_Input").Range("F:F").Copy Destination:=wbk2.Sheets("JT access upload do not touch").Range("B1")
wbk1.Sheets("Audit_Sheet_for_Input").Range("W:W").Copy Destination:=wbk2.Sheets("JT access upload do not touch").Range("C1")
wbk2.Sheets("JT access upload do not touch").Rows("1:5").Select
Selection.Delete Shift:=xlUp
wbk1.Close
wbk2.Save
wbk2.Close
End Sub