how to replace ThisWorkbook with the book opened?

jamiguel77

Active Member
Joined
Feb 14, 2006
Messages
330
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Web
Dim my_FileName As Variant
my_FileName = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*")
If my_FileName <> False Then
Workbooks.Open Filename:=my_FileName
Dim xActiveSheet As Worksheet
Set xActiveSheet = ThisWorkbook.ActiveSheet
Dim NewSheet As Worksheet
Dim wavanza As Boolean
Dim wrowIni As Integer
ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = "Resumen"

i test this code, open the file, but in this line:

ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = "Resumen"

i get this error:

Error '1004' this name is ocuppied try with another...


but the file havent a sheet named "Resumen"


any advice how to assign the workbook opened to thisWorkbook?


Thanks.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,935
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Thisworkbook will always be the workbook that holds the code that is running. Is there already a sheet named "Resumen" in the workbook the code is in?
 

jamiguel77

Active Member
Joined
Feb 14, 2006
Messages
330
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Web
not exist the sheet name "RESUMEN"

Not sheet hidden.

the problem is this 2 lines:

Workbooks.Open Filename:=my_FileName

and:

ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = "Resumen"

how to assign Workbook opened to this variable: ThisWorkbook


Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
75,871
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Dim my_FileName As Variant
Dim Wbk As Workbook
my_FileName = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*")
If my_FileName <> False Then
Set Wbk = Workbooks.Open(Filename:=my_FileName)
Dim xActiveSheet As Worksheet
Set xActiveSheet = Wbk.ActiveSheet
Dim NewSheet As Worksheet
Dim wavanza As Boolean
Dim wrowIni As Integer
Wbk.Sheets.Add(After:=Wbk.Sheets(Wbk.Sheets.count)).Name = "Resumen"
 

jamiguel77

Active Member
Joined
Feb 14, 2006
Messages
330
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Web
Worked Fluff thanks thanks.....
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
75,871
Office Version
  1. 365
Platform
  1. Windows
You're welcome
 

jamiguel77

Active Member
Joined
Feb 14, 2006
Messages
330
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Web
<Fluff> escuse me ask here but this line not freeze pane

NewSheet.Cells(wRowIniProd, 1).Select
ActiveWindow.FreezePanes = True

where:

Wbk.Sheets.Add(After:=Wbk.Sheets(Wbk.Sheets.Count)).Name = "Resumen"
Set NewSheet = Wbk.ActiveSheet

i think my problem is:

ActiveWindow

havent activated the window.....
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
75,871
Office Version
  1. 365
Platform
  1. Windows
As this is a completely different question, you need to start a new thread.
 

Forum statistics

Threads
1,170,953
Messages
5,872,928
Members
432,952
Latest member
Il Prete

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