Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a little help coping a sheet from one workbook to another.
Below is the code I have. It works in another macro, but not in a new one I
am working on. Not sure where I went wrong. I get the error at this line wbA.Sheets("C").Copy wbNew.Sheets("PW_CRAD_C") The error is run time error 9, subscript out of range. Sub ExportCA_Only() ' ' ExportCA_Only Macro ' Macro recorded 9/9/2002 by rgrisber Application.DisplayAlerts = False Application.ScreenUpdating = False Range("B1").Select Selection.AutoFilter Field:=2, Criteria1:="=CA", Operator:=xlOr, _ Criteria2:="=WP" Columns("C:F").Select Selection.Copy Worksheets.Add.Name = "C" Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False 'need to add a sheet to master and then copy the sheet Dim wbA As Workbook Dim wbNew As Workbook With Application SheetsInWb = .SheetsInNewWorkbook .SheetsInNewWorkbook = 1 .DisplayAlerts = False .ScreenUpdating = False End With Set wbA = ThisWorkbook Set wbNew = Workbooks.Add With wbNew .SaveAs Filename:="V:\CSPR\PW_CRAD_C.csv" End With wbA.Sheets("C").Copy wbNew.Sheets("PW_CRAD_C") 'wbNew.Sheets("Sheet1").Delete Dim myName As Name Dim WorkbookLinks As Variant Dim i As Long Dim myWB As Excel.Workbook Set myWB = ActiveWorkbook .... end sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a little help coping a sheet from one workbook to another.
Hi Brian,
I am assuming that you want the copied worksheet name in the new workbook the same as the new workbook name. If this is correct then try the following. Note that a space and underscore at the end of a line is a line break in an otherwise single line of code. Dim wbA As Workbook Dim wbNew As Workbook 'Dim SheetsInWb With Application 'Following line appears superfluous 'SheetsInWb = .SheetsInNewWorkbook .SheetsInNewWorkbook = 1 .DisplayAlerts = False .ScreenUpdating = False End With Set wbA = ThisWorkbook Set wbNew = Workbooks.Add With wbNew .SaveAs Filename:="V:\CSPR\PW_CRAD_C.csv" End With wbA.Sheets("C").Copy _ Befo=wbNew.Sheets(1) 'Rename sheet in new workbook wbNew.ActiveSheet.Name = "PW_CRAD_C" 'Delete superfluous sheet in new workbook wbNew.Sheets("Sheet1").Delete -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need a little help coping a sheet from one workbook to another.
I think you're doing too much work in your extraction:
Option Explicit Sub ExportCA_Only() 'I think using ThisWorkbook is better (documentation-wise) 'than creating a workbook. But your code looks like it's 'just working with the activesheet 'Dim wbA As Workbook Dim NewWks As Worksheet Dim ActWks As Worksheet Set ActWks = ActiveSheet Application.ScreenUpdating = False With ActWks 'remove any existing filter .AutoFilterMode = False .Range("B1").EntireColumn.AutoFilter _ Field:=1, _ Criteria1:="CA", _ Operator:=xlOr, _ Criteria2:="WP" 'check to see if there's any data If .AutoFilter.Range.SpecialCells(xlCellTypeVisible) _ .Cells.Count = 1 Then 'only the header is visible 'don't do the copy??? MsgBox "No details found!" Else 'create a new workbook with a single sheet 'and set the newwks to that worksheet Set NewWks = Workbooks.Add(1).Worksheets(1) .Range("C:F").Cells.SpecialCells(xlCellTypeVisible ).Copy NewWks.Range("A1").PasteSpecial Paste:=xlPasteValues 'remove the header row? 'NewWks.Rows(1).Delete With NewWks.Parent 'the workbook with the new sheet On Error Resume Next Application.DisplayAlerts = False .SaveAs Filename:="V:\CSPR\PW_CRAD_C.csv" If Err.Number < 0 Then Err.Clear MsgBox "CSV Save Failed!" _ & vbLf & "Please save it manually" Else MsgBox "Saved!" .Close savechanges:=False 'done with it? End If Application.DisplayAlerts = True On Error Goto 0 End With End If 'remove the filter .AutoFilterMode = False End With End Sub ps. you may want to look at how Ron de Bruin and Debra Dalgleish approached the similar problem of creating new sheets for each unique value in a column. It's not quite what you're doing today, but it could be what you're doing tomorrow <vbg. Ron de Bruin's EasyFilter addin: http://www.rondebruin.nl/easyfilter.htm Or: Code from Debra Dalgleish's site: http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb Brian S wrote: Below is the code I have. It works in another macro, but not in a new one I am working on. Not sure where I went wrong. I get the error at this line wbA.Sheets("C").Copy wbNew.Sheets("PW_CRAD_C") The error is run time error 9, subscript out of range. Sub ExportCA_Only() ' ' ExportCA_Only Macro ' Macro recorded 9/9/2002 by rgrisber Application.DisplayAlerts = False Application.ScreenUpdating = False Range("B1").Select Selection.AutoFilter Field:=2, Criteria1:="=CA", Operator:=xlOr, _ Criteria2:="=WP" Columns("C:F").Select Selection.Copy Worksheets.Add.Name = "C" Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False 'need to add a sheet to master and then copy the sheet Dim wbA As Workbook Dim wbNew As Workbook With Application SheetsInWb = .SheetsInNewWorkbook .SheetsInNewWorkbook = 1 .DisplayAlerts = False .ScreenUpdating = False End With Set wbA = ThisWorkbook Set wbNew = Workbooks.Add With wbNew .SaveAs Filename:="V:\CSPR\PW_CRAD_C.csv" End With wbA.Sheets("C").Copy wbNew.Sheets("PW_CRAD_C") 'wbNew.Sheets("Sheet1").Delete Dim myName As Name Dim WorkbookLinks As Variant Dim i As Long Dim myWB As Excel.Workbook Set myWB = ActiveWorkbook ... end sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem coping data from 1 sheet to new sheet | Excel Programming | |||
Coping a whole row to another sheet | Excel Programming | |||
WHY DOES COPING FROM SHEET TO SHEET INCREASE SIZE DRAMATICALLY? | Excel Discussion (Misc queries) | |||
Coping Contents of one Sheet into Another Workbook | Excel Programming | |||
Coping a sheet | Excel Programming |