dim ws as workbook etc.

RET79

Well-known Member
Joined
Mar 19, 2002
Messages
526
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
 
You can copy and paste from one workbook to another with the following without selecting or activating the workbook, worksheets or range.

workbooks("book1").workSheets("sheet1").range("a1").copy destination:=workbooks("book2").workSheets("sheet1").range("a1")

This copies the from book1 sheet1 cell a1 to book2 sheet1 cell a1
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
davers

its fixed width, and makes no sense its like


0234abc2434h20122003wfwj243242tokgq etc.

BUT, we are told by the IT dept where to put the columns in, for instance the above becomes

02,34,abc,2,43,4,h,20/12/2003,wfw,j24 etc.

if they had given us delimited, but they didnt. so therefore we have to open fixed width in excel using the wizard to put columns in position 0,2,4,7,8,10,11,19,22 etc. BUT my macro is designed to avoid going through that wizard crap all the time so we jsut cut and paste the 0,2,4,7,8, etc. from their emails onto column B of my sheet and the macro 'reads' where to put the columns every time.

I sincerely apologise for making such a meal out of this issue and no doubt I have put a lot of people off this topic. It would be great if you had a better way for me to do stuff with the text file but the issue I am after is still how to define workbooks etc. properly, without hard coding and cut and paste between sheets without activating and selecting.

RET79
 
Upvote 0
Ricky,

That is what I am after

BUT

I dont want the file names and sheets to be hard coded like that as I am planning on using this macro again in many different workbooks therefore I need a general way of naming these sheets.

See one of my earlier postings about this. I don't want any "book1" stuff there. I want to declare them like this

Dim Wb as workbook
Set Wb = ThisWorkbook

where ThisWorkbook would be "book1" say.
 
Upvote 0
You should be able to set that as an object and hardcode that in there. What error are you getting?
 
Upvote 0
zacemmel,

i cant remember exactly as i tried so many different things i got so many different errors, but one that came up was saying that object doesnt support this property etc.

i am really disturbed about this whole thing as i thought i had a good grasp about defining and using workbook objects but i cant believe i am having all this trouble to do this task.

RET79
 
Upvote 0
Set wb = Workbooks("book2")
wb.Sheets("sheet1").Range("a1:a10").ClearContents

I just did that and it worked fine for me...
 
Upvote 0
zacammel,

OK, that's cool, works for me too.

BUT, 2 things

(1) I dont want

Set wb = Workbooks("book2") .


I want

Set wb = ThisWorkbook

since wb will always be referring to the original workbook and I don't want hard coding here as I want to use this code in other workbooks and have it still work.

2) I don't want to specify Range like

Range("A1:A10")

I want the current region becuase the range will vary.

Does

wb.Sheets("sheet1").Range("A1").CurrentRegion.ClearContents

work for you? This is what I need effectively.

Many thanks,

RET79
 
Upvote 0
OK guys....

this is my best attempt but this doesnt work, if you could tell me why it would be so happy:-

Sub openAndPutOnNetPrem()


Dim original As Workbook
Dim nametextfile As Workbook
Dim sheetname As Worksheet

Set original = ThisWorkbook

OpenTextFileMacro

Set nametextfile = ActiveWorkbook
Set sheetname = ActiveSheet

original.Sheets("NetPrem").Range("A1").CurrentRegion.ClearContents

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

Application.CutCopyMode = False

End Sub
 
Upvote 0
Play around with this. It should get you somewhere. Select a bunch of cells in a workbook first and then step through this (Press F8).

Sub WorkbookObjectLesson()
Dim wb As Object
Dim rngRange As Object

Set rngRange = Selection
Set wb = ActiveWorkbook
MsgBox wb.Name
rngRange.Value = wb.Name


End Sub
 
Upvote 0
davers,

ok that was cool. do you think that the current region thing may be the problem with my code? Surely I should be able to select, on any open workbook, the current region and then clear contents without using the select or activate thing?

RET79
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top