Macro DtRNG as Range, Dt as Range, Multiple Criteria or dates

[Jack7774]

I used to modifiy my macro each day and change the date then run the macro. Basically CTRL-F find/replace AABBCC with the corresponding MMDDYY date for that day then run the macro. I found out that this code...

Dim DtRNG As Range, Dt As Range

Set DtRNG = Sheets("Sheet1").Range("A:A").SpecialCells(xlConstants)

For Each Dt In DtRNG

...would pull one date from the list in column A then run the macro and rinse and repeat for each Dt in DtRNG. The problem I am running into is I don't just need one date replaced. I need six different dates replaced. Is there a way I can replace the multiple dates without having to use CTRL-F to modify the macro each time. Below is pieces of the code that reference each date. Date1 and Date11 are the same date but different formats. I know I can use the above code to replace one of those dates but what about multiple dates?

Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE1-DATE6_DailyFile.xls"
Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE1_CIC_LocationSummaryReport.xls"
ActiveCell.FormulaR1C1 = "DATE11"
Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE2_CIC_LocationSummaryReport.xls"
ActiveCell.FormulaR1C1 = "DATE22"
Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE3_CIC_LocationSummaryReport.xls"
ActiveCell.FormulaR1C1 = "DATE33"
Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE4_CIC_LocationSummaryReport.xls"
ActiveCell.FormulaR1C1 = "DATE44"
Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE5_CIC_LocationSummaryReport.xls"
ActiveCell.FormulaR1C1 = "DATE55"
Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE6_CIC_LocationSummaryReport.xls"
ActiveCell.FormulaR1C1 = "DATE66"

Here is my thought process but its stops at one date

Dim DtRNG As Range, Dt As Range

Set DtRNG = Sheets("Sheet1").Range("A:A").SpecialCells(xlConstants)

For Each Dt In DtRNG

Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\" & Format(Dt, "YYYY") & "\" & Format(Dt, "YYYY") & "\Completed\" & Format(Dt, "MMDDYY") & "-DATE6_DailyFile.xls"

Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\" & Format(Dt, "YYYY") & "\" & Format(Dt, "YYYY") & "\Completed\" & Format(Dt, "MMDDYY") & "_CIC_LocationSummaryReport.xls"
ActiveCell.FormulaR1C1 = " & Format(Dt, "MM/DD/YY") & "

Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE2_CIC_LocationSummaryReport.xls"
ActiveCell.FormulaR1C1 = "DATE22"

Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE3_CIC_LocationSummaryReport.xls"
ActiveCell.FormulaR1C1 = "DATE33"

Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE4_CIC_LocationSummaryReport.xls"
ActiveCell.FormulaR1C1 = "DATE44"

Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE5_CIC_LocationSummaryReport.xls"
ActiveCell.FormulaR1C1 = "DATE55"

Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE6_CIC_LocationSummaryReport.xls"
ActiveCell.FormulaR1C1 = "DATE66"

[Norie]

What exactly are you trying to do?

Why are you using a loop if there's only one date?

[Jack7774]

I don't need the loop for this macro I just took it from another macro someone made for me. I am not sure how else to explain what I am trying to do other than tyring to replace DATE1 and DATE11 with one date (DATE1 and DATE11 being the two formats I need it in) {Im not concerned with the formats I know how to do that its dimming them down into a way I can use it from the personal.xls columns A and B and so on i guess, DATE2 and DATE22 with the second date, and so on until DATE6 and DATE66. I know .....

Dim DtRNG As Range, Dt As Range

Set DtRNG = Sheets("Sheet1").Range("A:A").SpecialCells(xlConstants)

For Each Dt In DtRNG

...will work for one date but what about the other 5? Can I do something like DtRNG and DT to be column A, then DtRNG2 and DT2 to be column B or something like that so that I can type the dates I need in personal.xls file in columns A and B and so on instead of using CTRL-F find/replace ?

[Norie]

Where are you trying to replace the date?

In the code you've posted it appears to be in the path to the workbook(s) you want to open and you also seem to be putting the date in a cell in the workbook(s) you are opening.

[Jack7774]

Here is the part of the code i could paste. We don't have to use the DtRNG and Dt if that is not going to work but maybe this might help with the thought of understanding what i am trying to accomplish i hope.

Sub WeeklyIntegrationSetupCIC()
'
' WeeklyIntegrationSetup MacroCIC
'
'===================================================================================================
'CTRL-F REPLACE "DATE1" W/1ST DATE OF WEEK, FORMAT AABBCC
'CTRL-F REPLACE "DATE11" W/1ST DATE OF WEE, FORMAT AA/BB/CC
'ETC...........................................................
'CTRL-F REPLACE "DATE6" W/1ST DATE OF WEEK, FORMAT AABBCC
'CTRL-F REPLACE "DATE66" W/1ST DATE OF WEE, FORMAT AA/BB/CC
'===================================================================================================

'
    Application.DisplayAlerts = False
    Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\Current Month\zIntegration template BLANK.xls"
    ChDir "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed"
    Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE1-DATE6_CIC_DailyFile.xls"
    Range("B7:W7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Windows("zIntegration template BLANK.xls").Activate
    Sheets("FeeSetup").Select
    Range("B7").Select
    ActiveSheet.Paste
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    
    Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE1-DATE6_GA_DailyFile.xls"
    Range("B7:W7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("zIntegration template BLANK.xls").Activate
    ActiveSheet.Paste
    Windows("DATE1-DATE6_CIC_DailyFile.xls").Activate
    ActiveWindow.Close
    Windows("DATE1-DATE6_GA_DailyFile.xls").Activate
    ActiveWindow.Close
    Sheets("CkbkSetup-single dist_site").Select
    '===================================================================================================

    Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE1_CIC_LocationSummaryReport.xls"
    Columns("A:A").Select
    Selection.NumberFormat = "mm/dd/yy;@"
    Selection.ColumnWidth = 10
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "date"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "DATE11"
    Range("A7").Select
    Selection.Copy
    ActiveCell.Offset(0, 4).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, -3).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A7:N7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("zIntegration template BLANK.xls").Activate
    ActiveSheet.Paste
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select

    Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE1_GA_LocationSummaryReport.xls"
        Columns("A:A").Select
    Selection.NumberFormat = "mm/dd/yy;@"
    Selection.ColumnWidth = 10
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "date"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "DATE11"
    Range("A7").Select
    Selection.Copy
    ActiveCell.Offset(0, 4).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, -3).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A7:N7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("zIntegration template BLANK.xls").Activate
    ActiveSheet.Paste
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Windows("DATE1_CIC_LocationSummaryReport.xls").Activate
    ActiveWindow.Close
    Windows("DATE1_GA_LocationSummaryReport.xls").Activate
    ActiveWindow.Close
    
    '====================================================================================================
    
    Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE2_CIC_LocationSummaryReport.xls"
    Columns("A:A").Select
    Selection.NumberFormat = "mm/dd/yy;@"
    Selection.ColumnWidth = 10
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "date"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "DATE22"
    Range("A7").Select
    Selection.Copy
    ActiveCell.Offset(0, 4).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, -3).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A7:N7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("zIntegration template BLANK.xls").Activate
    ActiveSheet.Paste
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select

    Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE2_GA_LocationSummaryReport.xls"
        Columns("A:A").Select
    Selection.NumberFormat = "mm/dd/yy;@"
    Selection.ColumnWidth = 10
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "date"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "DATE22"
    Range("A7").Select
    Selection.Copy
    ActiveCell.Offset(0, 4).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, -3).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A7:N7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("zIntegration template BLANK.xls").Activate
    ActiveSheet.Paste
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Windows("DATE2_CIC_LocationSummaryReport.xls").Activate
    ActiveWindow.Close
    Windows("DATE2_GA_LocationSummaryReport.xls").Activate
    ActiveWindow.Close
    '====================================================================================================
    
    Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE3_CIC_LocationSummaryReport.xls"
    Columns("A:A").Select
    Selection.NumberFormat = "mm/dd/yy;@"
    Selection.ColumnWidth = 10
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "date"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "DATE33"
    Range("A7").Select
    Selection.Copy
    ActiveCell.Offset(0, 4).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, -3).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A7:N7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("zIntegration template BLANK.xls").Activate
    ActiveSheet.Paste
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select

    Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE3_GA_LocationSummaryReport.xls"
        Columns("A:A").Select
    Selection.NumberFormat = "mm/dd/yy;@"
    Selection.ColumnWidth = 10
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "date"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "DATE33"
    Range("A7").Select
    Selection.Copy
    ActiveCell.Offset(0, 4).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, -3).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A7:N7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("zIntegration template BLANK.xls").Activate
    ActiveSheet.Paste
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Windows("DATE3_CIC_LocationSummaryReport.xls").Activate
    ActiveWindow.Close
    Windows("DATE3_GA_LocationSummaryReport.xls").Activate
    ActiveWindow.Close
    '    '===================================================================================
    MsgBox "Define the Names - Formulas - Name Managers - Select Name - Define Range - Check Mark - Okay."
    MsgBox "Save and Save as a backup copy as well"
End Sub

[Norie]

I'll look at the code, but an explanation in words might help too.

[Jack7774]

I have no clue how to explain it. I use CTRL-F to find and replace the word "DATE1" with example 020113 that is a date. I do the same thing for DATE11 and its the same date 02/01/13 but in this format.

'CTRL-F REPLACE "DATE1" W/1ST DATE OF WEEK, FORMAT MMDDYY
'CTRL-F REPLACE "DATE11" W/1ST DATE OF WEE, FORMAT MM/DD/YY
'ETC...........................................................
'CTRL-F REPLACE "DATE6" W/1ST DATE OF WEEK, FORMAT MMDDYY
'CTRL-F REPLACE "DATE66" W/1ST DATE OF WEE, FORMAT MM/DD/YY

I have another macro that basically does this very thing except it only does it for one date and I have multiple dates in this macro. Here is a small piece of it

Sub GLCompilation()
'
' GLCompilation Macro
'
'===============================================================================
Dim DtRNG As Range, Dt As Range

Set DtRNG = Sheets("Sheet1").Range("A:A").SpecialCells(xlConstants)

For Each Dt In DtRNG
'================================================================================
    ChDir "I:\ACCOUNTING\Jesse - Recon\Compiled GL Files"
    Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Jesse - Recon\Compiled GL Files\Oracle GL Compilation Template.xlsx"
    ChDir "I:\ACCOUNTING\GL Oracle\complete"
    Workbooks.OpenText Filename:= _
        "I:\ACCOUNTING\GL Oracle\complete\GeneralLedgerFile_" & Format(Dt, "MMDDYY") & ".txt", Origin:=437 _
        , StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
        TrailingMinusNumbers:=True

All I have to do is type the date in cell A1 in the personal.xls sheet1 tab. I want to do something simular except i have 6 dates total that need to be replaced in the weeklyintegrationsetup macro.

[Norie]

It was actually what the code is meant to do I was wondering about.

Obviously you have a bunch of files you are opening copying from.

Where are those files located and where are you copying to?

Why do you need to change the date?

Are the files being generated on a daily basis?

[Jack7774]

That directory will change based on the date as well.

[Jack7774]

The files are located in the same directory "I:\ACCOUNTING\Clear Payments\2013\2013-02\Completed\"

I need to change the dates because this macro is intended to run on a weekly basis

[Norie]

So the directory the files are in changes?

If all the files are in the same directory you could declare the path as a constant.

You would do this at the top of the code, and that's the only place it would need to be changed in the future.

By the way, why not close each workbook you are opening once you've finished with it rather than closing them all at the end?

[Jack7774]

The directory changes. Each month a folder is made for that month. I don't know how to declare a constant or even if that would work with the directory changing each month. Closing them once I'm finished with them do something I am not aware of rather than doing it at the end? Speed up the processs slightly or something?

[Norie]

This code has the path declared as a constant, strPath, at the top, you can use that throughout the rest of the code and you'll only need to change it in one place.

I've also added a few variables to reference the various worksheets.

This is only for the first part of the code, not really looked at the rest yet.

Actually the rest of the code seems quite different, it's manipulating the files being opened and doesn't appear to be copying anything from them to other worksheets/workbooks.

Sub WeeklyIntegrationSetupCIC()
' declare constant for folder path, change as required
Const strPath = "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\"

Dim wbDaily As Workbook
Dim wbTemplate As Workbook

    Application.DisplayAlerts = False
    Set wbTemplate = Workbooks.Open(Filename:= _
                                    "I:\ACCOUNTING\Clear Payments\Current Month\zIntegration template BLANK.xls")

    Set wbDaily = Workbooks.Open(Filename:=strPath & "DATE1-DATE6_CIC_DailyFile.xls")

    With wbDaily.ActiveSheet
        .Range("B7", Range("W" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy _
                wbTemplate.Sheets("FeeSetup").Range("B7")
    End With

    wbDaily.Close SaveChanges:=False

    Set wbDaily = Workbooks.Open(Filename:=strPath & "DATE1-DATE6_GA_DailyFile.xls")

    With wbDaily.ActiveSheet
        .Range("B7", Range("W" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy _
                wbTemplate.Sheets("FeeSetup").Range("B" & Rows.Count).End(xlUp).Offset(1)
    End With

    wbDaily.Close SaveChanges:=False

[Jack7774]

Thats all the macro does is open files copy data from them and paste them into template. I am not sure how you are missing that part.

I see you shortened the code, nice job. I didn't know the code language well enough to write it that way but i see now how each item works and what it does. The only problem I see so far is that

Set wbDaily = Workbooks.Open(Filename:=strPath & "DATE1-DATE6_CIC_DailyFile.xls")

and

Set wbDaily = Workbooks.Open(Filename:=strPath & "DATE1-DATE6_GA_DailyFile.xls")

Still have the DATE1 and DATE6 in the file name which is the part I want to replace with each week's weekdays dates. Otherwise its shorter and looks awesome so far.

[Norie]

I might have missed it because with all the Activating/Selecting it's hard to see what's going on.

As far as I can see, apart from the first two workbooks, you are manipulating the workbooks you are opening as well as copying.

For example, this opens a workbook, does a bunch of formatting in that workbook, copies from it and then moves onto the next workbook.

Workbooks.Open Filename:= _
        "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE1_CIC_LocationSummaryReport.xls"
    Columns("A:A").Select
    Selection.NumberFormat = "mm/dd/yy;@"
    Selection.ColumnWidth = 10
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "date"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "DATE11"
    Range("A7").Select
    Selection.Copy
    ActiveCell.Offset(0, 4).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, -3).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A7:N7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("zIntegration template BLANK.xls").Activate
    ActiveSheet.Paste
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select

As for DATE1 and DATE6, you could use constants or variables for them just as you are the path.

[Jack7774]

Yes i manipulate the file name because the file name has a date in it that changes for each days date for each file name. And as far as using constant or variables i don't know how but that sounds like a plausible solution. As for what the code does it formats certain data, it enters a date, and it copies. I'm not interested in shortening it unless you feel you want to but the part that i am trying to achieve is avoid having to open the macro hold button control while pressing f using the find replace the date 1 date 2 date 3 date 4 date 5 date 6 into the actual dates which is the actual file name. If i could type the dates in as a constant or variable at the top that would be great but how do you do that?

[Norie]

You would do it the same way as the for the path, declare the dates as constants at the top of the module.

Then that's the only place you'll need to change them.

As for shortening the code, that is one reason for tidying up the code but it's also to make the code run faster and to make sure it's actually doing what it's supposed to do.

I'll take a look at the code but I'll probably have to make a few guesses to work out what's going on.

[Jack7774]

So I would say what

Const "?" = Date1
Const "?" = Date2
Const "?" = Date3
Const "?" = Date4
Const "?" = Date5
Const "?" = Date6

What about format if i do it this way?

Also could i set the Const "?" = "A cell reference in the personal.xls sheet1 like B1 and put the date in the cell then manipulate the format from that point?

[Jack7774]

So I would say what

Const "?" = Date1
Const "?" = Date2
Const "?" = Date3
Const "?" = Date4
Const "?" = Date5
Const "?" = Date6

What about format if i do it this way?

Also could i set the Const "?" = "A cell reference in the personal.xls sheet1 like B1 and put the date in the cell then manipulate the format from that point?

I would still like an example of what you mean in order to setup those dates as constants as to what goes in the ? mark area I have no idea.

[Jack7774]

I can also tell you what each line is doing as well if its not written how you are used to reading it. LOOK FOR THE COMMENTS IN ALL CAPS AND WITH THE ' STARTING THE LINE AS WELL. MAYBE THIS WILL HELP.

    Sub WeeklyIntegrationSetupCIC()

 'YOU ALREADY HAVE THIS PART SO I WILL SKIP THIS AREA
        Application.DisplayAlerts = False
        Workbooks.Open Filename:= _
            "I:\ACCOUNTING\Clear Payments\Current Month\zIntegration template BLANK.xls"
        ChDir "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed"
        Workbooks.Open Filename:= _
            "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE1-DATE6_CIC_DailyFile.xls"
        Range("B7:W7").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.SpecialCells(xlCellTypeVisible).Select
        Selection.Copy
        Windows("zIntegration template BLANK.xls").Activate
        Sheets("FeeSetup").Select
        Range("B7").Select
        ActiveSheet.Paste
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
        
        Workbooks.Open Filename:= _
            "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE1-DATE6_GA_DailyFile.xls"
        Range("B7:W7").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.SpecialCells(xlCellTypeVisible).Select
        Application.CutCopyMode = False
        Selection.Copy
        Windows("zIntegration template BLANK.xls").Activate
        ActiveSheet.Paste
        Windows("DATE1-DATE6_CIC_DailyFile.xls").Activate
        ActiveWindow.Close
        Windows("DATE1-DATE6_GA_DailyFile.xls").Activate
        ActiveWindow.Close
        Sheets("CkbkSetup-single dist_site").Select
 '___________________________________________________________________________________________________

        Workbooks.Open Filename:= _
            "I:\ACCOUNTING\Clear Payments\2013\2013-01\Completed\DATE1_CIC_LocationSummaryReport.xls"
'FORMATTING COLUMN A TO DATE FORMAT AND 10 WIDTH
        Columns("A:A").Select
        Selection.NumberFormat = "mm/dd/yy;@"
        Selection.ColumnWidth = 10
'IN CELL A6 MAKING A HEADER FOR THE COLUMN
        Range("A6").Select
        ActiveCell.FormulaR1C1 = "date"
'IN CELL A7 PUTTING IN THE DATA THAT I CHOOSE DATE11 TO BE WHICH ESSENTIALLY IS THE FIRST DATE OF THE WEEK LIKE LAST WEEK WAS MONDAY FEB 25TH SO THE DATA I WOULD WANT DATE11 TO BE REPLACED WITH IS 02/25/13 IN THAT FORMAT
        Range("A7").Select
        ActiveCell.FormulaR1C1 = "DATE11"
'COPING THE CELL A7 SO THAT I CAN PASTE IT INTO THE DATA BELOW WHERE DATA EXISTS IN CELLS IN COLUMN B SO THAT EACH ROW HAS A DATE OF THAT DATE BECAUSE THE FILE I OPEN DOES NOT HAVE A DATE HEADER SHOWING WHAT DATE THE DATA GOES WITH I HAVE TO PUT ONE IN THE SPREADSHEET FOR EACH ROW BECAUSE THE TEMPLATE CALLS A FORMULA BASED ON THE DATE IN COLUMN A CELL REFERENCE IN THE TEMPLATE
        Range("A7").Select
        Selection.Copy
        ActiveCell.Offset(0, 4).Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(0, -3).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Selection.Copy
        Range(Selection, Selection.End(xlUp)).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
'THIS IS COPING THE DATA IN THE SPREADSHEET WITH THE DATE THAT WAS ADDED AND PASTING IT INTO THE TEMPLATE
        Range("A7:N7").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Windows("zIntegration template BLANK.xls").Activate
        ActiveSheet.Paste
'THIS IS SIMPLY GOING TO THE BOTTOM OF THE COLUMN A NEXT CELL DOWN OR FINDING THE NEXT EMPTY CELL IN COLUMN A SO THAT WHEN THE NEXT COPY AND PASTE IS DONE IT WILL NOT OVERLAP ANY OF THE DATA ALREADY PASTED
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select


'THE DATA FROM HERE DOWN REPEATS THE SAME INSTRUCTIONS OVER AND OVER AGAIN UNTIL EACH FILE FOR EACH DAY IS COPIED AND PASTED AS WELL.

[Norie]

This is the part of the code I wasn't following.

       Range("A7").Select
        Selection.Copy
        ActiveCell.Offset(0, 4).Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(0, -3).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Selection.Copy
        Range(Selection, Selection.End(xlUp)).Select
        ActiveSheet.Paste

First you select A7, copy it, move 4 columns to the right, then down to the last row of data, then 3 columns to the left, then you copy again and finally paste.

I think a sample workbook would probably clear up what's going on.

[Jack7774]

Attached is a sample file. Basically that part of the code copies the data in that cell and essentially find the bottom of the column of a row that still has data and pastes the data in that cell in column a in that row then copies the data in the rest of the column. I wrote it this way because each day these files change in size and I don't know which cell it will need to copy and paste down to so that each row of data has that following date. I suggested to a superior that we just contact the company who made these reports just for us and add a date column on the report however its not very high on their priority list.

[Norie]

Sorry, that doesn't really help as there's no data.

If I run the code on the worksheet it's just copying blanks.

I can understand that the data might change, but what is it you actually want to copy and where do you want to copy it to?

[Jack7774]

I can't give you the data unfortunately. If you would like to fill in the columns with data yourself you are more than welcome. I fail to see how you won't be able to see what the code is doing when the code itself tells you line by line what it does. I would suggest opening the sample file and running this code line by line intead of running the macro all at once to get a better understanding. I have put comments in the code line by line almost to help.

'OPENS FILE
Workbooks.Open Filename:= _
        "...............sample file.xls"
'WIDENING THE COLUMN SO IT CAN BE SEEN
    Columns("A:A").Select
    Selection.NumberFormat = "mm/dd/yy;@"
    Selection.ColumnWidth = 10
'INSERTING A HEADER
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "date"
'INSERTING THE ACTUAL DATE FOR THE FILE - CONTAINS TRANSACTIONS THAT I CAN NOT SHARE
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "DATE11"
'COPIES THE DATE
    Range("A7").Select
    Selection.Copy
'MOVING OVER A FEW COLUMNS TO A COLUMN THAT IS FILLED WITH DATA (I LEFT THAT DATA ON THE SPREADSHEET)
    ActiveCell.Offset(0, 4).Select
'GOING DOWN TO THE BOTTOM OF THAT COLUMN'S DATA
    Selection.End(xlDown).Select
'MOVING BACK OVER TO THE COLUMN THAT IS EMPTY TO PUT THE DATE IN THAT ROW THAT CONTAINS DATA
    ActiveCell.Offset(0, -3).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
'COPYING THE DATE SO THAT I CAN FILL THE REMAINING CELLS IN THAT COLUMN WITH THE DATE FOR THE REMAINING ROWS WITH NO DATE
    Selection.Copy
'GOING UP THE EMPTY CELLS IN THE COLUMN
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
'SELECTING THE RANGE OF DATA I WISH TO COPY DOWN TO THE LAST ROW WITH DATA (AGAIN THIS AMOUNT OF DATA CHANGES EVERY DAY THUS THE REASON FOR SELECTION.END(XLDOWN) INSTEAD OF INPUTTING A RANGE
    Range("A7:N7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
'NOW ITS READY TO GO BACK TO THE OTHER SPREADSHEET FOR PASTING

Does this help?

[Norie]

You've got it the wrong way round.

Date1, Date2, ... would be the names of the constants/variables.

They would be variables if you were to pull their values from a worksheet, which I think you might want to do.

[Jack7774]

Thanks. I read some more forum posts about how to use constants as well. SOLVED