copy data from on workbook to another

[tuytuy]

Hey guys,
i want to copy data from a sheet in a workbook, to another workbook in the same directory, but this directory may change.
Thx

 

[sergioMabres]

To copy and past both workbook have to be open
May be you would like some macro to open the second workbook from the same directory where the 1st workbook was opened?
Like dir = get the directory for current workbook
Open workbook dir & "\secondfile.xls"
Copy
Paste
Is this is what you need?

 

[tuytuy]

To copy and past both workbook have to be open
May be you would like some macro to open the second workbook from the same directory where the 1st workbook was opened?
Like dir = get the directory for current workbook
Open workbook dir & "\secondfile.xls"
Copy
Paste
Is this is what you need?

ho sorry forgot to mention i want to use vba

 

[sergioMabres]

yes I guessed you needed a macro now
Shall the macro ask for a directory in a pop up?
Or shall the macro read the directory of the first, that is open, file and open the second?
There is a couple of ways to do what you said
If both file are already open you need not to use a directory name

 

[tuytuy]

yes I guessed you needed a macro now
Shall the macro ask for a directory in a pop up?
Or shall the macro read the directory of the first, that is open, file and open the second?
There is a couple of ways to do what you said
If both file are already open you need not to use a directory name

if it could read the directory it would be great, and the second file where the data need to be copied wouldn't be open when the macro runs.
ho and i dont know if it matters but the names of the files are always the same.

 

[sergioMabres]

Here some vba code to do what I understand you want

Sub copyopenpasteclose()
    Dim d As String
    d = Application.ActiveWorkbook.Path
    Range("C4:D5").Copy
    Workbooks.Open Filename:=d & "\secondfile.xlsx"
    Range("F13").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub

"C4:D5" can be changed to a range name or your desired range address
"F13" can be changed to a range name or your desired range address
"secondfile.xlsx" can be changed to your desired name
I hope this helps

 

[tuytuy]

Here some vba code to do what I understand you want

Sub copyopenpasteclose()
    Dim d As String
    d = Application.ActiveWorkbook.Path
    Range("C4:D5").Copy
    Workbooks.Open Filename:=d & "\secondfile.xlsx"
    Range("F13").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub

"C4:D5" can be changed to a range name or your desired range address
"F13" can be changed to a range name or your desired range address
"secondfile.xlsx" can be changed to your desired name
I hope this helps

Great works perfectly !
i have another problem. The second file is basically a yearly synthesis, and the first file represent one month, so every month i need the macro to the data in the next column available so i can have in the second file something like this :

Jan-13

Feb-13

Mar-13

Apr-13

May-13

Jun-13

Jul-13

Aug-13

Sep-13

Oct-13

Nov-13

Dec-13

Flotte

Total number of users:

Total number of BIP:

Total number of BlackBerry:

Total number of Smartphones

Voice

<colgroup><col><col span="12"></colgroup><tbody>
</tbody>

 

[sergioMabres]

Well you need to decide how the macro is going to know the month to process
1. There is a control with a combo box to select the month you want to process
2. Paste data in next available col, you won't be able to reprocess, you will have to fill by hand January if you want to start in February.
3. The macro uses current date to determine where to paste data