Activate workbook

[Roses8]

Hi,

Hi,
I am trying to activate a window once I have opened the workbook with a macro.

The main aim is to open the workbook 2 and then go back to workbook 1, look up the sheet iI want it to copy as the sheet name always changes, go to workbook 2 and copy and bring the worksheet back into workbook 1.


Sheets("Front Page").Select
Range("A3").Select
ActiveCell.CurrentRegion.Select

For Each i In Selection
myvalue = i.Value
myworkbook = "\\x:\data\workbooksfolder\" & myvalue & ".xls"
Workbooks.Open Filename:=myworkbook
Next

not sure how to do this part -

Windows("UT MI.xls").Activate
If Worksheets("Front Page").Range("C3").Value Then
Window.Activate = "\\x:\data\workbooksfolder\" & myvalue & ".xls"
Worksheets("Front Page").Range("C3").activate
Else
End If

Many thanks.

 

[Harvey]

my advise is to not use it.
Use Application.ScreenUpdating = False instead, to make sure to user won't see any flashing.

 

[Roses8]

Hi,

But i cant even get it to work, i can use the code once it works, but how do i copy a sheet over from one workbook to another, but the name defined in another workbook.

 

[erik.van.geit]

Hi,

I think, you don't need "selections" and "activations", but you could need putting the workbooks in a variable so you can refer to them like:

WB1.Sheets(1).Range("A1:D6").Copy Wb2.Sheets(1).Range("A1")

or

WB2.Sheets(1).Range("A1:D6") = Wb2.Sheets(1).Range("A1:D6")

typed off-hand typos possible

your code with some tweaks

Sub test()
'you can use more descriptive variable names
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim cell As Range

Set WB1 = ActiveWorkbook
'or
'Set WB1 = Workbooks("UT MI.xls")

'I don't think, you need these selections
Sheets("Front Page").Select
Range("A3").Select
ActiveCell.CurrentRegion.Select

For Each cell In Selection
Workbooks.Open Filename:="\\x:\data\workbooksfolder\" & cell.Value & ".xls"
Set WB2 = ActiveWorkbook
Next

If WB1.Worksheets("Front Page").Range("C3").Value Then
WB2.Activate
Worksheets("Front Page").Range("C3").Activate
Else
End If
End Sub

it was not clear to me what exactly you want to do, but this information might get you further ...

kind regards,
Erik

 

[erik.van.geit]

Hi,

But i cant even get it to work, i can use the code once it works, but how do i copy a sheet over from one workbook to another, but the name defined in another workbook.

Didn't see this reply before posting mine.
You are talking about copying a sheet, but it looks to me as you want to copy a range (part of a sheet)

Please record a macro while doing your operations and post the code.

 

[Roses8]

Hi, thank you thats helped great deal,

however i cannot get it to activate the sheet in wb2 from the name of the worksheet in WB1.

WB1.Worksheets(Worksheets("Front Page").Range("A1").Value).WB2.worksheetvalue.Activate

 

[Harvey]

WB1.Worksheets(Worksheets("Front Page").Range("A1").Value).WB2.worksheetvalue.Activate

What are you trying to do here?

If you want to copy things, do something like this:

set WB1=Workbooks("name workbook 1")
set WB2=Thisworkbook

SH1=WB2.Worksheets("Front Page").Range("A1").Value 'is this the name of the sheet?

WB1.Sheets(SH1).Range("your range").Copy
WB2.Sheets("whatever sheet you are using").Range("destination range").Paste

Now Erik's and my code should give you an idea of how it works. Good luck!

 

[Roses8]

Hey thank you, thats given me a much better understanding of how to do this...