Clear Macro, and a Import Macro, How do I merge them

[santeria]

I have a Recorded Macro that clears some data pages, and I have an import macro that needs to have the Clear Page Macro edited into it.

I am not clear how to both edit the Clear Sheet Macro, nor how to edit the two together.

This is the recorded Clear Sheet Macro:

Sub CLEARRAWDATASHEETSTEST()
'
' CLEARRAWDATASHEETSTEST Macro
' Macro recorded 1/22/2004 '

'
    Range("A1:Z100").Select
    ActiveWindow.LargeScroll ToRight:=-1
    ActiveWindow.LargeScroll Down:=-2
    Selection.ClearContents
    Selection.QueryTable.Delete
    Range("I21").Select
    Sheets("RAW SKILL 17").Select
    Range("A1:Z100").Select
    ActiveWindow.LargeScroll ToRight:=-1
    ActiveWindow.LargeScroll Down:=-2
    Selection.ClearContents
    Selection.QueryTable.Delete
    Range("C7").Select
    Sheets("SKILL 77 SERV LEVEL").Select
    Range("A1:Z100").Select
    ActiveWindow.LargeScroll ToRight:=-1
    ActiveWindow.LargeScroll Down:=-2
    Selection.ClearContents
    Selection.QueryTable.Delete
    Sheets("SKILL 17 SERV LEVEL").Select
    Range("A1:Z100").Select
    ActiveWindow.LargeScroll ToRight:=-1
    ActiveWindow.LargeScroll Down:=-2
    Selection.ClearContents
    Selection.QueryTable.Delete
    Range("B23").Select
End Sub

And here is the import Macro:

Sub doimPrt(ByVal fName As String, ByVal shtNme As String, ByVal destName As String)
    Dim myDir As String
myDir = "C:\EXPORTS\" & Format$(Date, "mm_mmmm") & "\CMS\DAILY\"

With Sheets(shtNme)
    
        With .QueryTables.Add(Connection:="TEXT;" & myDir & fName, Destination:=.Range(destName))
            .Name = TableName
            .FieldNames = True
            .PreserveFormatting = True
            .TextFileParseType = xlDelimited 'not sure if you need this line
            .TextFileTextQualifier = xlTextQualifierDoubleQuote 'not sure if you need this line
            .RefreshStyle = xlOverwriteCells
            .AdjustColumnWidth = False
            .TextFileTabDelimiter = True
            .TextFileColumnDataTypes = Array(1, 1)
            .Refresh BackgroundQuery:=False
        End With
    
    End With
    
End Sub

Sub doimPrtall()
     Dim fNames As Variant, shtNmes As Variant, DestNames As Variant, i As Long
    
    fNames = Array("1.txt", "2.txt", "3.txt", "4.txt")
    shtNmes = Array("RAW SKILL 77", "RAW SKILL 17", "SKILL 77 SERV LEVEL", "SKILL 17 SERV LEVEL")
    DestNames = "A1"
            
    For i = LBound(fNames) To UBound(fNames)
        doimPrt fNames(i), shtNmes(i), DestNames
    Next
    
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("SUMMARY").Select

End Sub

Could someone maybe advise on how to edit the clear sheet macro, and how the Import macro can be edited to bring in the clear sheet macro, so that the pages are cleared before the data is imported.

Ta Muchly.

 

[santeria]

Absolutely Excellent !!!!!!!!!!!!!!

Ta Muchly !!!!!!!!

ray: ray:

 

[santeria]

I know this is horribly thick of me, but do you mean the selects at the *end* of the lines?

Or the Selection bits?

So am I supposed to edit out the ".Select" elements ??

Ta

 

[Smitty]

Generally speaking, wherever you have .Select, followed by Selection, but words can be elimintated:

Sheets("RAW SKILL 77").Select 
    Range("A1:Z100").Select 
    ActiveWindow.LargeScroll ToRight:=-1 
    ActiveWindow.LargeScroll Down:=-2 
    Selection.ClearContents

Can be shortened to:

Sheets("RAW SKILL 77").Range("A1:Z100").ClearContents

And as your code has two functions per range explains why I added the With statements in the fisrt post. So instead of:

Sheets("RAW SKILL 77").Range("A1:Z100").ClearContents
Sheets("RAW SKILL 77").Range("A1:Z100").QueryTable.Delete

You have

With Sheets("RAW SKILL 77").Range("A1:Z100")
   .ClearContents
   .QueryTable.Delete
End With

Smitty