Hi.
I have a very annoying problem to sort out.
Please, do not reply with coding that includes select and activate, I can do this task with the macro recorder no problem, I am asking how to improve my code without the need for selecting or activating worksheets.
I have a file called NetPrem.xls. It contains two sheet - "NetPrem" and "InputSheet". There is a macro button on "InputSheet" which runs the "OpenText" macro I have which opens my text files in my own customized format.
Quite simply, I want to click that button, to activate the macro which opens the NP.txt file say in the excel format I want. Then, I want to delete the data currently on sheet "NetPrem" and copy the data on the active "NP.txt" in its place. Then close "NP.txt" .
OK, so this is the code I currently have which works , but is ugly. I am convinced this can be done without selecting and activating between sheets. But, I am having problems when trying to define worksheets and workbooks. Take a look
Sub openAndPutOnNetPrem()
original = ActiveWorkbook.Name
'this macro is always activated from the same button on the same workbook & sheet.
OpenTextFileMacro 'this is calling the macro
nametextfile = ActiveWorkbook.Name
'the activeworkbook at the conclusion of the OpenTextFileMacro is always the text file which has been opened in excel format.
Windows(original).Activate
Sheets("NetPrem").Select
Range("A1").CurrentRegion.ClearContents
Windows(nametextfile).Activate
Range("A1").CurrentRegion.Copy
Windows(original).Activate
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, Font _
:=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
End Sub
Very ugly. Please help me do this without all the amateurish window selecting and activating. I want to do this using just workbook,worksheet and range objects - I can't stand Window selecting and activating.
Also I have tried to assign the range which I am attempting to copy, and put it in an array then putting the array on NetPrem. But no luck there either.
I hope this makes sense,
RET79
This message was edited by RET79 on 2002-05-01 11:26
I have a very annoying problem to sort out.
Please, do not reply with coding that includes select and activate, I can do this task with the macro recorder no problem, I am asking how to improve my code without the need for selecting or activating worksheets.
I have a file called NetPrem.xls. It contains two sheet - "NetPrem" and "InputSheet". There is a macro button on "InputSheet" which runs the "OpenText" macro I have which opens my text files in my own customized format.
Quite simply, I want to click that button, to activate the macro which opens the NP.txt file say in the excel format I want. Then, I want to delete the data currently on sheet "NetPrem" and copy the data on the active "NP.txt" in its place. Then close "NP.txt" .
OK, so this is the code I currently have which works , but is ugly. I am convinced this can be done without selecting and activating between sheets. But, I am having problems when trying to define worksheets and workbooks. Take a look
Sub openAndPutOnNetPrem()
original = ActiveWorkbook.Name
'this macro is always activated from the same button on the same workbook & sheet.
OpenTextFileMacro 'this is calling the macro
nametextfile = ActiveWorkbook.Name
'the activeworkbook at the conclusion of the OpenTextFileMacro is always the text file which has been opened in excel format.
Windows(original).Activate
Sheets("NetPrem").Select
Range("A1").CurrentRegion.ClearContents
Windows(nametextfile).Activate
Range("A1").CurrentRegion.Copy
Windows(original).Activate
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, Font _
:=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
End Sub
Very ugly. Please help me do this without all the amateurish window selecting and activating. I want to do this using just workbook,worksheet and range objects - I can't stand Window selecting and activating.
Also I have tried to assign the range which I am attempting to copy, and put it in an array then putting the array on NetPrem. But no luck there either.
I hope this makes sense,
RET79
This message was edited by RET79 on 2002-05-01 11:26