Can it be done??

[TroyYoung]

Hi Guys,

I currently have the following macro which works a treat however I wanted to add to it and Im not sure where to start.

Sub Consolidate()
'Open all Excel files in a specific folder and import data as separate sheets
'JBeaucaire (7/6/2009)     (2007 compatible)
Dim strFileName As String, strPath As String, sName As String
Dim wbkOld As Workbook, wbkNew As Workbook, ws As Worksheet
'Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Set wbkNew = ThisWorkbook
strPath = "\\ia-sbs2008\tggs\reports\phone reports\Daily Outbound Trace\"
strFileName = Dir(strPath & "*.xl*")
wbkNew.Activate
'Import first active sheet from found file
    Do While Len(strFileName) > 0
        Set wbkOld = Workbooks.Open(strPath & strFileName)
        sName = Left(strFileName, InStr(strFileName, ".") - 1)
        Sheets("Data Sheet").Name = sName
        Sheets(sName).Copy After:=wbkNew.Sheets(wbkNew.Sheets.Count)
        strFileName = Dir
        wbkOld.Close False
    Loop
    
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

As you can probably tell by reading the above that I am importing data from other sheets located on a network drive, It creates a new sheet within the workbook and imports that data in.

The next step that I would like to implement into this is that,

Once teh sheet is created, I would like to to Paste a set of data and formulas

as follows.

M7 = "Enow Office"
M8 = "Enow Director"
M9 = "EPS"
M10 = "HLT - Kristy"
M11 = "HLT - Heath(1)"
M12 = "HLT - Heath(2)"
M13 = "PSM"
M14 = "Switch"
M15 = "TwoWay Office"
M16 = "TwoWay Director"

N7 through N16 is a set of values to look up for.

O7 through O16 is a series of formulas

What would be the best way to approach this??

 

[Trebor76]

Hi TroyYoung,

To populate cells with text, simply run this while on the desired tab:

Range("M7").Value = "Enow Office"
    Range("M8").Value = "Enow Director"
    Range("M9").Value = "EPS"
    Range("M10").Value = "HLT - Kristy"
    Range("M11").Value = "HLT - Heath(1)"
    Range("M12").Value = "HLT - Heath(2)"
    Range("M13").Value = "PSM"
    Range("M14").Value = "Switch"
    Range("M15").Value = "TwoWay Office"
    Range("M16").Value = "TwoWay Director"

This is also the same mentality for the values you wish to appear in cells N7 to N16, just remove the quotes if dealing with numbers.

Formulas are slightly different - post back with what they are and I'll try to create them via code.

HTH

Robert

 

[TroyYoung]

=COUNTIF(Dialed_Number,N7)

=COUNTIF(Dialed_Number,N8)


etc

 

[Trebor76]

Range("O7").Formula = "=COUNTIF(Dialed_Number,N7)"
    Range("O8").Formula = "=COUNTIF(Dialed_Number,N8)"

etc

 

[TroyYoung]

Cheers,

I have actually had a think about it and although what you have proposed as worked and worked well, It does not allow for it to be easily updated, and we will continually be adding new data, and I dont want to have add 3 lines of code for every new supplier we put on.


SOOOO I have created a table and will just copy and paste the columns to each new sheet, allowing for a much easier way to update.


Thanks again for your help.

Regards,

Troy Young