macro code error please help

Microsoft / P-4
May 27, 2009 at 23:17:55
Specs: Windows XP
Hi i have record one macro which ask user to open file and after user open the file another macro copy
some column as per requirements i m getting error if user select different file name other than
the file name he selected at the time of recoding macro.

can you help to change the code below so that it becomes complitible to use with different files.

code is as fllows.

Dim myfile As String

myfile = Application.GetOpenFilename
Workbooks.Open myfile

windows("Barcodes.csv").Activate
Columns("A:A").Select
Selection.Copy
Windows("TBBT Tool.xls").Activate
Columns("A:A").Select
ActiveSheet.Paste
Windows("Barcodes.csv").Activate
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Copy
Windows("TBBT Tool.xls").Activate
Columns("B:B").Select
ActiveSheet.Paste
Windows("Barcodes.csv").Activate
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Copy
Windows("TBBT Tool.xls").Activate
Columns("C:C").Select
ActiveSheet.Paste
Range("E2").Select

when user select the file which has different then "barcodes.csv" file i am getting the error "Subscripit out of the range" for Windows() fuction.
can any one help to correct this code please?


See More: macro code error please help

Report •


#1
May 28, 2009 at 05:56:51
You use the variable myfile to request the filename from the user. Therefore, you need to use that variable any time you want to refer to that file.

Try replacing barcodes.csv with myfile and see what happens.

Note: I have not tested this suggestion.

P.S. The Recorder creates macros with a lot of extraneaous code. In many cases much of this extra code can be eliminated.

For example, in most cases you don't need to Select an item to perform an action on it. In many cases, things like

Columns("C:C").Select
Application.CutCopyMode = False
Selection.Copy

can be reduced to

Columns("C:C").Copy

Eliminating the extraneous code not only makes the macro more efficient, it will help you learn what syntaxes work and which don't. You cannot always record what you want a macro to do, so eventually you are going to need to learn to write the code from scratch. Cleaning up recorded code is a great way to learn what works and what doesn't.

One last note:

Take a look at the ScreenUpdating property in VBA Help. When your code is making changes to spreadsheets, like copying and pasteing, you might want to turn off ScreenUpdating so everything happens in the background without a lot visual stuff happening. Just make sure you turn it back on at the end of your code.


Report •

#2
May 29, 2009 at 01:09:15
Hi,

Thanks for your quick reply
but when i treid to replace myfile varibale at that time only for wirndows() i am getting the error "Subscript out of range"

see the code below

Dim myfile As String

myfile = Application.GetOpenFilename
Workbooks.Open myfile


Windows(myfile).Activate
Columns("A:A").Select
Selection.Copy
Windows("TBBT Tool.xls").Activate
Columns("A:A").Select
ActiveSheet.Paste

please can you tell me how i can chagne the code so what ever the file name code will not give this error.

Regards,
dharmesh


Report •

#3
May 29, 2009 at 20:35:56
Sorry...like I said I didn't test my last suggestion.

This one I did and I see why my last suggestion didn't work.

When you use myfile = Application.GetOpenFilename, myfile contains not only the filename, but the entire path to the file, therefore Excel doesn't know how to process Windows(myfile).Activate.

To solve this issue, you need to strip off the path and reduce the string to just the filename. One way to do this is as follows:

When the code opens the file, it becomes the Active file. so you can use:

myfile = Application.GetOpenFilename
Workbooks.Open myfile
myfileName = ActiveWorkbook.Name

and then use myfileName whenever you want to refer to that workbook.


Report •

Related Solutions

#4
June 2, 2009 at 23:49:34
thanks it really helps a lot

Report •


Ask Question