dim ws as workbook etc.

[RET79]

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

 

[davers5]

The problem is that you're saying Range("a1") and then saying current region. Range("a1").currentregion is cell a1. Try selection.currentregion or define a range object and just select the range object.

Let me know if either of these suggestions work.

 

[RET79]

Just for information...

It is this line which the VBE doesn't like in my above code:

nametextfile.sheetname.Range("A1").CurrentRegion.Copy original.Sheets("NetPrem").Range("A1")

and it comes up with the error message

object doesnt support this property or method.

even when i break it up using Select like this:

nametextfile.sheetnamed.Range("A1").CurrentRegion.Select


that doesnt work either.

 

[RET79]

in fact, even this doesnt work

nametextfile.sheetnamed.Range("A1").Select

 

[RET79]

so doesnt it like

Set nametextfile = ActiveWorkbook
Set sheetname = ActiveSheet

for some reason? Even though I put

Dim sheetname as worksheet
Dim nametextfile as workbook

at the beginning ?

 

[Ricky Morris]

Your code:
nametextfile.sheetnamed.Range("A1").Select

has a "d" at the end of sheetname, which is differernt from your Dim and Set statements.

 

[RET79]

Ricky,

I hate to say this, and trust me I am not taking the ****, but, that was a typo.

RET79

 

[Ricky Morris]

Try this:

nametextfile.Sheets(sheetname.Name).Range("A1").Select

 

[RET79]

PERFECTION!!!!

Thanks to everyone so much for your help. Ricky, your killer tip finally pushed it, thanks ever for your help!!!!!

Sub openAndPutOnNetPrem()


Dim original As Workbook
Dim nametextfile As Workbook
Dim sheetnamed As Worksheet
Set original = ThisWorkbook

OpenTextFileMacro

Set nametextfile = ActiveWorkbook
Set sheetnamed = ActiveSheet
original.Sheets("NetPrem").Range("A1").CurrentRegion.ClearContents
nametextfile.Sheets(sheetnamed.Name).Range("A1").CurrentRegion.Copy
original.Sheets("NetPrem").Range("A1").PasteSpecial
Application.CutCopyMode = False

nametextfile.Close False



End Sub

 

[RET79]

or maybe like this neglecting the paste special, this is now lightning quick, so much faster than when messing about with windows.activate and select, WOWOWOWOWOWOW


Sub openAndPutOnNetPrem()


Dim original As Workbook
Dim nametextfile As Workbook
Dim sheetnamed As Worksheet
Set original = ThisWorkbook

OpenTextFileMacro

Set nametextfile = ActiveWorkbook
Set sheetnamed = ActiveSheet
original.Sheets("NetPrem").Range("A1").CurrentRegion.ClearContents
nametextfile.Sheets(sheetnamed.Name).Range("A1").CurrentRegion.Copy _
original.Sheets("NetPrem").Range("A1")
Application.CutCopyMode = False

nametextfile.Close False



End Sub