[RESOLVED] Simple but stuck

[bennett4455]

writing in excel 2003
i am trying to copy and rename an excel workbook but i cant seem so sort it out.
the dates need to start on 01-01-10 and finish 31-12-10
the code i have been working on is below. but i cant find out what the problem is???? please help


Dim nyear As Date

nyear = Format(Now(), "dd-mm-yy")
Do Until nyear = "31-12-10"


FileCopy Source:="M:\settings\Desktop\monthmaster.xls", Destination:="M:\settings\Desktop\" & nyear & ".xls"
nyear = nyear + 1
Loop
End Sub

[akhileshbc]

Try something like this...

Dim nyear As Date

nyear = DateSerial(2010, 1, 1)
While nyear <= DateSerial(2010, 12, 31)
    Debug.Print nyear
    FileCopy Source:="M:\settings\Desktop\monthmaster.xls", Destination:="M:\settings\Desktop\" & Format(nyear, "dd-mm-yy") & ".xls"
    nyear = DateAdd("m", 1, nyear) '~~~> adds 1 month (for more info: http://www.vb6.us/tutorials/understanding-vb6s-dateadd-function)
Wend

[Hack]

Moved To Office Development

[bennett4455]

spot on thaky you very much.

and merry xmas

[akhileshbc]

spot on thaky you very much.

Glad to hear that...

Happy new year...!

[fichelr]

I believe I have 2 similar problems

1. How do I paste my copied cells into workbook "Y"?

2. The data is pulled from SAP and has a fair amount of information about the data pull at the top of the spreadsheet. The amount of data may vary, making the row I sort on, 56, inappropriate. Is there a way to sort under a specifically named cell(s)?

This is a hodgepodge of code thrown into one macro, if there is a way to re-tool, please feel free.

Thank you for the help.

 Sub Macro2()
'
' Macro2 Macro
'

'
    Dim wbbook As Workbook
    Dim thiswbbook As ThisWorkbook
    
    strSecondFile = "Workbook x"
    
    Set wbk = Workbooks.Open(strSecondFile)
    With wbk.Sheets("Sheet1")
    
    Rows("56:56").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$56:$AC$30015").AutoFilter Field:=3, Criteria1:="XXXX"
    Range("G57:J57").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    *GO BACK TO WORKBOOK Y*
    
    Range("FILLDOWN_START_ROWCOUNT").Select
    ActiveSheet.Paste
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    End With
    
End Sub