I have a macro that I want to run from one workbook (Summary.xls) that opens
another workbook (Source.xls), adds a worksheet (Summary_Sheet) and then runs
a series of commands on the second workbook (Source.xls) before moving the
new sheet (Summary_Sheet) to the original workbook (Summary.xls). The
(Source.xls) filename changes every month, so I don't want to hardcode it.

My problem is that the macro opens the Source file, but shifts back to the
original file to create the worksheet and run the commands. How do I keep
the focus on the file I just opened without hardcoding the filename?

Thanks for any help I can get.

The only way that I know to get the focus back to the original workbook is to
have code something like this...
Windows("Summary.xls").Activate
Otherwise the focus stays with the Workbook you just opened.

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"gary_wyman" wrote:

I have a macro that I want to run from one workbook (Summary.xls) that opens
another workbook (Source.xls), adds a worksheet (Summary_Sheet) and then runs
a series of commands on the second workbook (Source.xls) before moving the
new sheet (Summary_Sheet) to the original workbook (Summary.xls). The
(Source.xls) filename changes every month, so I don't want to hardcode it.

My problem is that the macro opens the Source file, but shifts back to the
original file to create the worksheet and run the commands. How do I keep
the focus on the file I just opened without hardcoding the filename?

Thanks for any help I can get.

Gary,

Thanks, but it still doesn't keep the focus on the file I open via the
macro. Here's the whole macro. I think the problem is the line that says
Set Sourcebook = ThisWorkbook. If I run this from the Source.xls file (and
'comment out the Application.GetOpenFile line) it works perfectly. However,
I can't put this code in the source file since it is created monthly by a
different person.

Sub Summary_All_Worksheets_With_Formulas_Copy()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook
Dim Sourcebook As Workbook
Dim rng As Range
Dim LastRow As Long
Dim FillRow As Long


With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Application.GetOpenFilename
Set Sourcebook = ThisWorkbook

'Add a worksheet with the name "Summary-Sheet"
Set Newsh = Worksheets.Add
Newsh.Name = "Summary-Sheet"
Newsh.Range("A1:D1").Value = Array("Sheet Name", "Prod_Channel_Offer",
"Accounts", "CMV")

'The links to the first sheet will start in row 2
RwNum = 1

For Each Sh In Sourcebook.Worksheets
If Sh.Name < Newsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1
'Copy the sheet name in the A column
Newsh.Cells(RwNum, 1).Value = Sh.Name

For Each myCell In Sh.Range("A2,E36,e27")
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell

End If
Next Sh

Newsh.UsedRange.Columns.AutoFit

With Application
Set rng = Cells(1, 1).End(xlDown).Offset(1, 0)
rng.Range("C1").FormulaR1C1 = "=Sum(R2C:R[-1]C)"
End With

With Worksheets("Summary-Sheet")
LastRow = .Cells(Rows.Count, "c").End(xlUp).Row
.Range("e2").Formula = "=c2/$c$" & LastRow & "*d2"
FillRow = .Cells(Rows.Count, "d").End(xlUp).Row
.Range("E2").AutoFill Destination:=.Range("e2:e" & FillRow) _
, Type:=xlFillDefault
rng.Range("e1").FormulaR1C1 = "=Sum(R2C:R[-1]C)"
End With

Sheets("Summary-Sheet").Select
Sheets("Summary-Sheet").Move Befo=Workbooks("Summary").Sheets(1)

Windows("Summary").Activate

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With


End Sub


"Gary Brown" wrote:

The only way that I know to get the focus back to the original workbook is to
have code something like this...
Windows("Summary.xls").Activate
Otherwise the focus stays with the Workbook you just opened.

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"gary_wyman" wrote:

I have a macro that I want to run from one workbook (Summary.xls) that opens
another workbook (Source.xls), adds a worksheet (Summary_Sheet) and then runs
a series of commands on the second workbook (Source.xls) before moving the
new sheet (Summary_Sheet) to the original workbook (Summary.xls). The
(Source.xls) filename changes every month, so I don't want to hardcode it.

My problem is that the macro opens the Source file, but shifts back to the
original file to create the worksheet and run the commands. How do I keep
the focus on the file I just opened without hardcoding the filename?

Thanks for any help I can get.

Gary,
The GetOpenFilename Method displays the standard Open dialog box and gets
a file name from the user without actually opening any files.

Put this with the declarations...
Dim strFileName as String

Put this in places of Application.GetOpenFilename...
strFileName = Application.GetOpenFilename
Application.Workbooks.Open varFileName

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"gary_wyman" wrote:

Gary,

Thanks, but it still doesn't keep the focus on the file I open via the
macro. Here's the whole macro. I think the problem is the line that says
Set Sourcebook = ThisWorkbook. If I run this from the Source.xls file (and
'comment out the Application.GetOpenFile line) it works perfectly. However,
I can't put this code in the source file since it is created monthly by a
different person.

Sub Summary_All_Worksheets_With_Formulas_Copy()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook
Dim Sourcebook As Workbook
Dim rng As Range
Dim LastRow As Long
Dim FillRow As Long


With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Application.GetOpenFilename
Set Sourcebook = ThisWorkbook

'Add a worksheet with the name "Summary-Sheet"
Set Newsh = Worksheets.Add
Newsh.Name = "Summary-Sheet"
Newsh.Range("A1:D1").Value = Array("Sheet Name", "Prod_Channel_Offer",
"Accounts", "CMV")

'The links to the first sheet will start in row 2
RwNum = 1

For Each Sh In Sourcebook.Worksheets
If Sh.Name < Newsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1
'Copy the sheet name in the A column
Newsh.Cells(RwNum, 1).Value = Sh.Name

For Each myCell In Sh.Range("A2,E36,e27")
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell

End If
Next Sh

Newsh.UsedRange.Columns.AutoFit

With Application
Set rng = Cells(1, 1).End(xlDown).Offset(1, 0)
rng.Range("C1").FormulaR1C1 = "=Sum(R2C:R[-1]C)"
End With

With Worksheets("Summary-Sheet")
LastRow = .Cells(Rows.Count, "c").End(xlUp).Row
.Range("e2").Formula = "=c2/$c$" & LastRow & "*d2"
FillRow = .Cells(Rows.Count, "d").End(xlUp).Row
.Range("E2").AutoFill Destination:=.Range("e2:e" & FillRow) _
, Type:=xlFillDefault
rng.Range("e1").FormulaR1C1 = "=Sum(R2C:R[-1]C)"
End With

Sheets("Summary-Sheet").Select
Sheets("Summary-Sheet").Move Befo=Workbooks("Summary").Sheets(1)

Windows("Summary").Activate

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With


End Sub


"Gary Brown" wrote:

The only way that I know to get the focus back to the original workbook is to
have code something like this...
Windows("Summary.xls").Activate
Otherwise the focus stays with the Workbook you just opened.

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"gary_wyman" wrote:

I have a macro that I want to run from one workbook (Summary.xls) that opens
another workbook (Source.xls), adds a worksheet (Summary_Sheet) and then runs
a series of commands on the second workbook (Source.xls) before moving the
new sheet (Summary_Sheet) to the original workbook (Summary.xls). The
(Source.xls) filename changes every month, so I don't want to hardcode it.

My problem is that the macro opens the Source file, but shifts back to the
original file to create the worksheet and run the commands. How do I keep
the focus on the file I just opened without hardcoding the filename?

Thanks for any help I can get.

How about something like this

Dim aWB as workbook
Dim oWB as workbook

Set aWB = activeworkbook

''When you open the new workbook, do this

Set oWB = WOrkbooks.open(...)

When you want to add sheets to the oWB, ensure you are referencing oWB.

HTH,
Barb Reinhardt



"gary_wyman" wrote:

I have a macro that I want to run from one workbook (Summary.xls) that opens
another workbook (Source.xls), adds a worksheet (Summary_Sheet) and then runs
a series of commands on the second workbook (Source.xls) before moving the
new sheet (Summary_Sheet) to the original workbook (Summary.xls). The
(Source.xls) filename changes every month, so I don't want to hardcode it.

My problem is that the macro opens the Source file, but shifts back to the
original file to create the worksheet and run the commands. How do I keep
the focus on the file I just opened without hardcoding the filename?

Thanks for any help I can get.

Thanks, but that still doesn't work. In fact when I select the file to Open,
it must close right away because nothing happens with it. The focus never is
on the file I just opened. I can't set the Workbook name to something
hardcoded because the file name changes every month.

Please somebody help.


"Barb Reinhardt" wrote:

How about something like this

Dim aWB as workbook
Dim oWB as workbook

Set aWB = activeworkbook

''When you open the new workbook, do this

Set oWB = WOrkbooks.open(...)

When you want to add sheets to the oWB, ensure you are referencing oWB.

HTH,
Barb Reinhardt



"gary_wyman" wrote:

I have a macro that I want to run from one workbook (Summary.xls) that opens
another workbook (Source.xls), adds a worksheet (Summary_Sheet) and then runs
a series of commands on the second workbook (Source.xls) before moving the
new sheet (Summary_Sheet) to the original workbook (Summary.xls). The
(Source.xls) filename changes every month, so I don't want to hardcode it.

My problem is that the macro opens the Source file, but shifts back to the
original file to create the worksheet and run the commands. How do I keep
the focus on the file I just opened without hardcoding the filename?

Thanks for any help I can get.

I stumbled onto the answer. The GetOpenFilename method only gives you the
filename, but doesn't actually open the file. Using the FindFile method
allowed me to open the file. Thanks Barb for your tip, I was able to
incorporate it following the latest discovery.


"gary_wyman" wrote:

Thanks, but that still doesn't work. In fact when I select the file to Open,
it must close right away because nothing happens with it. The focus never is
on the file I just opened. I can't set the Workbook name to something
hardcoded because the file name changes every month.

Please somebody help.


"Barb Reinhardt" wrote:

How about something like this

Dim aWB as workbook
Dim oWB as workbook

Set aWB = activeworkbook

''When you open the new workbook, do this

Set oWB = WOrkbooks.open(...)

When you want to add sheets to the oWB, ensure you are referencing oWB.

HTH,
Barb Reinhardt



"gary_wyman" wrote:

I have a macro that I want to run from one workbook (Summary.xls) that opens
another workbook (Source.xls), adds a worksheet (Summary_Sheet) and then runs
a series of commands on the second workbook (Source.xls) before moving the
new sheet (Summary_Sheet) to the original workbook (Summary.xls). The
(Source.xls) filename changes every month, so I don't want to hardcode it.

My problem is that the macro opens the Source file, but shifts back to the
original file to create the worksheet and run the commands. How do I keep
the focus on the file I just opened without hardcoding the filename?

Thanks for any help I can get.