PDA

View Full Version : Solved: Copying Named Ranges



asystole0
06-14-2010, 09:10 AM
Hi Guys

I need your help...again!

I have some working code, it looks at a location for another excel document and pulls in all the worksheets.

The problem comes when it tries to copy across the named ranges, there can be upto 20 in each of the documents im bringing into the current document and excel requires me to press "YES" to each and everyone. Is there a way to turn this off and say yes to all?



If CheckBox1.Value = True Then
Dim obXL As Object
Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim Sht As Worksheet
Set wbTarget = ThisWorkbook
Set wbSource = Workbooks.Open(tbFilePath) ' Pulls file path from tbFilePath box (worked better this way)
For Each Sht In wbSource.Sheets
Sht.Copy before:=wbTarget.Sheets(1)
Next
wbSource.Close ' Close the source workbook
GoTo CarryOn
ElseIf CheckBox1.Value = False Then
GoTo CarryOn
End If


Message Received:

http://mattgooch.com/wp-content/uploads/2010/06/excel-error1.jpg

Bob Phillips
06-14-2010, 12:39 PM
Application.DisplayAlerts = False

If CheckBox1.Value = True Then
Dim obXL As Object
Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim Sht As Worksheet
Set wbTarget = ThisWorkbook
Set wbSource = Workbooks.Open(tbFilePath) ' Pulls file path from tbFilePath box (worked better this way)
For Each Sht In wbSource.Sheets
Sht.Copy before:=wbTarget.Sheets(1)
Next
wbSource.Close ' Close the source workbook
GoTo CarryOn
ElseIf CheckBox1.Value = False Then
GoTo CarryOn
End If

Application.DisplayAlerts = True

asystole0
06-14-2010, 01:21 PM
Application.DisplayAlerts = False

If CheckBox1.Value = True Then
Dim obXL As Object
Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim Sht As Worksheet
Set wbTarget = ThisWorkbook
Set wbSource = Workbooks.Open(tbFilePath) ' Pulls file path from tbFilePath box (worked better this way)
For Each Sht In wbSource.Sheets
Sht.Copy before:=wbTarget.Sheets(1)
Next
wbSource.Close ' Close the source workbook
GoTo CarryOn
ElseIf CheckBox1.Value = False Then
GoTo CarryOn
End If

Application.DisplayAlerts = True


Thanks xld, Solved!