By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,519 Members | 2,258 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

Problems with Creating/Executing Excel Macro

P: 1
I have to automatically create some Excel Spreadsheets based on automatically generated .CSV files that are produced overnight. Each .CSV has several columns that need to be deleted. The same columns (both in location and name) are deleted each time. The macro checks for Column Headers/Names and if the value isn't equal to one of 8 specifc values, I want to delete the column. I am attempting to create a Macro that executes automatically when a file is opened in Excel. I have found the Workbook_Open area in the VB Editor within Excel and have created a Macro that will execute when a file is opened. The macro will run successfully standalone outside of the Workbook_Open function. Whenever I add the macro to the Workbook_Open I receive a very generic error: Run-time error '1004' Method "Range" of Object'_Global Failed.
Here is the way I am executing the code:
ThisWorkbook has
Private Sub Workbook_Open()
FaxFormat

Here is the FaxFormat Macro
Sub FaxFormat()
Target1=OWNER_ID
Target2=FAXDIDNUM
Target3=REMOTEID
Target4=SNED_TIME
Target5=FAX_STATUS
Target6=NUMPAGES
Target7FAXDATE
Target8=FAXTIME
ActiveSheet.Range("A1").Select THIS IS THE ROW WITH THE ERROR
Do Until ActiveCell=""
If ActiveCell.Value<>Target1 and ActiveCell.Value<>Target2 and ActiveCell.Value<>Target3 and ActiveCell.Value<>Target4 and ActiveCell.Value<>Target5 and ActiveCell.Value<>Target6 and ActiveCell.Value<>Target7 and ActiveCell.Value<>Target8 Then
Active.Cell.EntireColumn.Delete
Else
ActiveCell.Offset(0,1).Activate
End If
Loop

The rest of the macro sorts and sizes columns, save the file and closes the file.

I am confused as the macro runs fine in standalone mode. It only errors when attempting to Auto Launch it upon opening of a file.

Thanks for any assistance.
Nov 14 '08 #1
Share this Question
Share on Google+
1 Reply


Dököll
Expert 100+
P: 2,364
Hey there good buddy!

Sorry, you probably needed this right away. Most of us are are spread out all over the world; time diffrence and all, hope you understand...

That said, I am not skilled at Macros, but I reckon the Access forum may help you get an idea.

How about giving them a try, see what pops up:-)

In a bit!
Jan 1 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.