# Making a macro switch between workbooks



## mishasingh (May 28, 2006)

Hi ..

I'm trying to get a macro to Open a particular directory and let me select a csv file to open. Once it does this, I want to copy the entire sheet in the csv file to the other open workbook (the first one I mean) ..
After this I need to switch back to the csv file, but I can't figure out how to do that (I can't hardcode the name of the csv file as it will be different everyday) .. below is the code I am currently using:

Sub Import_UPS_file()

'
' Import_UPS Macro

Set wkbkFile1 = ActiveWorkbook
filename2 = ActiveWorkbook.Name

MyPath = CurDir("G")
Path_text = Application.Text(Range("today").Value, "yyyymmdd")
Path_text = "G:\IA_SHARE\UPS\" & Path_text & "\"
ChDrive "G"
ChDir Path_text
FilterIndex = 1
Title = "Select the Price Sheet for Trust file"

filename1 = Application.GetOpenFilename

If filename1 = "" Or filename1 = False Then
MsgBox "No File was selected."
End
End If

Workbooks.Open Filename:=filename1
Range("A:AA").Copy
Windows(filename2).Activate
Sheets("UPS_Sheet").Select
Range("A:AA").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Windows(filename1).Activate <----- *this is where the macro stops saying the value of filename1 is blank (probably because I switched to filename2*

End Sub

I hope someone can help me .. 

Thanks.


----------



## mishasingh (May 28, 2006)

Oops .. I meant *this is where the macro SAYS the value of filename1 is blank (probably because I switched to filename2*


----------



## Rollin_Again (Sep 4, 2003)

Are you getting a "Subscript out of range" error?

I suspect you are getting an error because you are trying to activate the workbook using the full path instead of the workbook name only. After opening *filename1* set a new variable equal to the name of newly opened workbook which should be the active workbook at that time. You can then activate the workbook using the workbook name only instead of the full file path.


```
Workbooks.Open Filename:=filename1
wbName = ActiveWorkbook.Name
Range("A:AA").Copy
Windows(filename2).Activate
Sheets("UPS_Sheet").Select
Range("A:AA").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Windows(wbName).Activate
```
Another way of pulling out the workbook name is to use the "Split" function to seperate the file name from the full file path. It's probably easiest to just use the code I provided above but here is an example of the split function.


```
Workbooks.Open Filename:=filename1
x = Split(filename1, Application.PathSeparator)
wbName = x(UBound(x))
Range("A:AA").Copy
Windows(filename2).Activate
Sheets("UPS_Sheet").Select
Range("A:AA").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Windows(wbName).Activate
```
Regards,
Rollin


----------



## mishasingh (May 28, 2006)

Hi Rollin .. Thanks for that .. it works fine now .. 

Is there any way I can 'Deselect' the range I've copied though?


----------



## Rollin_Again (Sep 4, 2003)

The only way I know of to deselect a particular range is to select another cell or another range. Is there a particular reason use are using the *PasteSpecial method* in your code? What exactly is being copied from the range? If you don't need to use *PasteSpecial* you can always use the *CopyTo* method which will allow you to copy directly from one range to another without needing to select the source range first. Is there anyway you can upload a sample workbook?

Regards,
Rollin


----------



## Rollin_Again (Sep 4, 2003)

Here is another way of transferring data between workbooks without having to select and copy the range.


```
Workbooks("CopyTo.xls").Sheets("Sheet1").Range("B1:B19").Value = _
Workbooks("CopyFrom.xls").Sheets("Sheet1").Range("A1:A19").Value
```
Regards,
Rollin


----------

