converting and copying columns in excel

I have a problem with my code, I am trying first to copy columns from worksheet1 to worksheet2 inside the same workbook and second i want to convert column 2 in worksheet1 which contains string into an integer column
by connecting to a database and retrieving the ID corresponding to each cell and copy the ID in the same cell but in worksheet2.Everything's ok but my problem is that I obtain the following message:
"A file named "index30052006.xls" already exists in this location.Do you want to replace it?".Can anyone help me to find what's wrong?

VB Code:

Private Sub cmdmerge_Click()
'Open the Workbook
Set wkbSource = Workbooks.Open("C:\index30052006.xls")
 
 
'Set wksSource = wkbSource.Worksheets(1)
'Set wksTarget = wkbSource.Worksheets(2)
 
'LastRow = wksSource.UsedRange.End(xlDown).Row
 
Worksheets(1).Columns(1).Copy Worksheets(2).Columns(10)
Worksheets(1).Columns(4).Copy Worksheets(2).Columns(6)
Worksheets(1).Columns(5).Copy Worksheets(2).Columns(8)
Worksheets(1).Columns(8).Copy Worksheets(2).Columns(7)
Worksheets(1).Columns(9).Copy Worksheets(2).Columns(12)
Worksheets(1).Columns(11).Copy Worksheets(2).Columns(9)
 
nextstep
End Sub
 
 
Private Sub Matching()
Rs1.Open "Select * from Category where DescriptionCategory like '" & scat & "'", cnn, adOpenStatic, adLockOptimistic
If Rs1.RecordCount = 0 Then
Worksheets(2).Cells(i, 2).Value = "No Value"
Else
Worksheets(2).Cells(i, 2).Value = Rs1!CategoryID
End If
Rs1.Close
End Sub
 
Private Sub nextstep()
Dim lastRow As Integer
 
lastRow = Worksheets(1).UsedRange.End(xlDown).Row
For i = 2 To lastRow
scat = Worksheets(1).Cells(i, 2)
Matching
Next i
wkbSource.Save
wkbSource.Close False
  Set wkbSource = Nothing
End Sub

well u are saving the worksheet

wkbSource.Save

but u arent using SaveAs so im not sure why its prompting u??? odd....

but do this

VB Code:

Application.DisplayAlerts False
    wkbSource.Close True
Application.DisplayAlerts True

I took out the SAVE line since u are closing it right after u can just call SAVE on the close by using Close True

engnouna ...

You may find a lot of your problems if you put "Option Explicit" as the very top line of your module. It will show you a lot of your errors. What is happening is that you are treating "wkbSource" like a Global variable, but it is only defined in "Sub cmdmerge_Click" and is ONLY local to that subroutine where it is also opened. You need to learn about the "Scope" of variables (DKenny said the same thing in response to your Thursday post). You are using the same variable NAME "wkbSource" in "Sub nextstep", but I'm pretty sure VBA assumes that this is the ActiveWorkbook and not necessarily the same workbook it opened in "Sub cmdmerge_Click". Even though the name is the same in both subroutines, VBA is treating them like entirely different Workbooks. "Option Explicit" prevents VBA from making this type of assumption ... you have to explictly and properly define all variables yourself, so it will complain that you have not properly defined "wkbSource" in "Sub nextstep", and you'll have to add a DIM line to fix it. That will still result in two different variables.

To make "wkbSource" a "Global" handle for the Workbook, put it above the start of the first Subroutine/Function in the module. I generally prefix my Globals with a "g" or a "G", so it would look like "G_wkbSource". Now, the same book is referenced whenever you use that name.

The other option is to pass the handle for the open workbook as a parameter to the "Sub nextstep", making it something like "Sub nextstep(byref aBook as Workbook)". Now, the call to "nextstep" becomes "nextstep(wkbSource)". If you really want to, you can use "wkbSource" instead of "aBook" in "Sub nextstep" ... pick whatever name is the easiest for you to follow.