Excel Macro VBA Tip 6 - IF Then Statement in Excel Macros and VBA

How can I Export and import Excel Macro from an Excel Template in Centralized location? Whenever user opens the Template this code gets imported at runtime.

All macro code in file1 kept in Shared drive.

Whenever any user opens Template it gets all the code for macro from this Shared Drive --> template.



For that I need to have some code within all the templates to get this code from shared drive.

i) Importing macros

ii) have all code in VBS format in the shared drive



But for First solution it not working. I will show you what I have worked on and could you please tell me what is wrong.

Please find the code which i have worked on however has issues with it when import line is execute. It doesn't perform any action though it executes that line.
Code:

Private Sub Workbook_Open()
'Call Sheet1.updatescreens
Call OpenSourceExcel
Call CopyMacroModule(Workbooks("Smoke_Test.xls"), "Test Script")
'Call CopyMacroModule(Workbooks("Smoke_Test.xls"), "Result")
'Call CopyMacroModule(Workbooks("Smoke_Test.xls"), "Datapool")
'Call CopyMacroModule(Workbooks("Smoke_Test.xls"), "Object Repository")
Call CloseSourceExcel
End Sub

Sub CopyMacroModule(SourceWB As Workbook, strModuleName As String)
strFolder = SourceWB.Path
arun = ThisWorkbook.Path
arun1 = ThisWorkbook.Name
Set Targetwb = ThisWorkbook
MsgBox arun1
temp = arun & "\" & arun1
MsgBox temp
If Len(strFolder) = 0 Then strFolder = CurDir
strFolder = strFolder & "\"
strTempFile = strFolder & "tmpexport.bas"
MsgBox strFolder
SourceWB.VBProject.VBComponents(1).Export (strTempFile)
MsgBox ActiveWorkbook.Name
Targetwb.VBProject.VBComponents.Import (strTempFile)
ThisWorkbook.AcceptAllChanges
ThisWorkbook.Save
Kill strTempFile
End Sub


Private Sub OpenSourceExcel()
strSourceSheet = "C:\Smoke_Test.xls"
Set objExcelSource = CreateObject("Excel.Application")
Set objSpreadSource = objExcelSource.Workbooks.Open(strSourceSheet)
End Sub
Private Sub CloseSourceExcel()
objExcelSource.Workbooks.Close
End Sub

I have a spreadsheet that has 600+ rows of data and numerous columns (10-15). Each of the 600 rows will become 24. With a combination of a few macros, and if statements, im sure this could be a breeze. I have a basic idea of what to do, but am lost. I have been up all nite from 12am till know, and am know where close.....

If I attached the file and explained what I need done you think you guys can help??

I need a little help on whether this should be done with a Macro or an IF statement.

On the first tab, there is a field that the user inputs, and then i need to go to the second tab and if the field is in between the range of the column C and D, then i need to return Column A and E back to the first tab. But if it is not in the range, it needs to go down to the next line, and the next line until the range is met. Any ideas? I need a little help.

THANKS!

Hii..

I'm looking for a macro or condition statement which would do following for me:

If cell A1 is "BUY" then run a macro automatically which will add current value in cell B1 to cell A3
so everything theres a "BUY" in cell A1..macro will keep addiing B1 values in cell A3

and

If cell A1 is "SELL" then run a macro automatically which will add current value in cell B1 to cell A4
so everything theres a "SELL" in cell A1..macro will keep addiing B1 values in cell A4

Hope it makes sense...also A1 will be updated dynamically through external source i

I had a macro in the file, and then subsequently deleted it from the Tools->Macro menu. I saved the file after doing this, and when I reopen the file Excel still gives me the Macro Warning and asks if I want to Enable/Disable Macros. Why does it do this after I have deleted the only Macro?

How can I prevent this from happening?

Thanks

Hi,

I have the following situation:

In Excel 2003, I have a working macro that opens another workbook - and then runs a macro sub within that other workbook. It works fine in Excel 2003 since it seems that macros are enabled automatically in the second opened workbook.

However, when I run this in Excel 2007 it does not work. When the first macro opens another workbook - the other workbook does NOT have macros enabled and I get an error when trying to run the macro within that second workbook.

Is there a way in Excel 2007 to have a macro open another Excel workbook with macros enabled? Or this perhaps a new security restriction in Excel 2007?

Best regards,
CC

Column A contains 4 digit code

Every 4 digit code has few 8 digit codes

Column B contains 8 digit Code.

For every 8-digit code there is information related to that code in columns C-N
For some codes information is stored on 2 rows and for some codes it is stored on more than 2 rows.

Is it possible to add 2 rows for every 8-digit code just before the next 8-digit code starts. These rows will contain "Import Policy Reference" from another Sheet "Sheet3"

The sample sheet can be downloaded from
http://tsi-pk.webs.com/chapter%201.xls

I have highlighted the required 2 rows i added manually in yellow

Help!
This is the similar problem as mentioned by Delbert on 12/12/2005 that
whenever they open a particular Excel file, it gives them a "Send Report
Error." Weird thing
about it is it doesnt happen in all other Excel files, only happens to this
file when saving as usual and trying to reopen it shortly after that. It
happens on my machine and my friend working on the same file also.
Details:
OS: Winxp
Office 2003 SP2
Files are saved in local drive
Files are around 12MB size (varies)
Files have Macros imbedded
Files have links
While opening, the dialogue box appears twice, asking for Enabling or
diabling macros, then crash (excel repair the error though, but all macros
and most formulas are gone, the log file is attach )

I have patched Office (SP2), and tried to use Excel safemode and other
methods mentioned in Delbert's thread, but excel still crashes, I doubt
wheather it is due to in this very workbook, there are several worksheets
containing a large amount of data, while in others, several macros. Will it
work if I seperate data and macros into two seperate workbooks?

Any solutions? Or those who have encountered this problem? Thank you!!


Microsoft Office Excel File Repair Log is shown as follows:
******************
Errors were detected in file
'C:\DOCUME~1\Owner\LOCALS~1\Temp\Rar$DI00.687\GSIM4x42005_1230.XLS'
The following is a list of repairs:
Damage to the file was so extensive that repairs were not possible. Excel
attempted to recover your formulas and values, but some data may have been
lost or corrupted.
******************

Because I'm lazy & want consistency in data input, I wrote the following macro to convert text to upper case in three cells on a worksheet:

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Value "" And Target = [b5] Then
Target.Value = UCase(Target.Value)
End If
If Target.Value "" And Target = [b6] Then
Target.Value = UCase(Target.Value)
End If
If Target.Value "" And Target = [b7] Then
Target.Value = UCase(Target.Value)
End If
End Sub

it might be a bit clumsy but it worked without any errors.

However, I've been migrated to Excel2010 and now the macro appears to get into a loop, asks me to "End" or "Debug" the macro & my only recourse is to save the spreadsheet, shut excel down through Task Manager and re-open Excel.

Does anyone have any clues/suggestions, please?

Working in Excel 2002/XP on an Excel worksheet which was created in Excel
2000, I get an error message "Subscript out of range". How do I update the
macro to work in Excel 2002?