Activate workbook

Roses8

Active Member
Joined
Aug 4, 2005
Messages
348
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. :)
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
my advise is to not use it.
Use Application.ScreenUpdating = False instead, to make sure to user won't see any flashing.
 

Roses8

Active Member
Joined
Aug 4, 2005
Messages
348
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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:
Code:
WB1.Sheets(1).Range("A1:D6").Copy Wb2.Sheets(1).Range("A1")
or
Code:
WB2.Sheets(1).Range("A1:D6") = Wb2.Sheets(1).Range("A1:D6")
typed off-hand typos possible

your code with some tweaks
Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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

Active Member
Joined
Aug 4, 2005
Messages
348
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

Well-known Member
Joined
Nov 18, 2004
Messages
953
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:

Code:
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

Active Member
Joined
Aug 4, 2005
Messages
348
Hey thank you, thats given me a much better understanding of how to do this...

:biggrin:
 

Forum statistics

Threads
1,136,510
Messages
5,676,281
Members
419,617
Latest member
Shane50GT

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
Top