Newb question

[Montana34]

Okay I didnt know how to phrase my subject line so here is my question.

I have gotten some code from here to allow me to filter out unused data and only copy the rows that meet the criteria into a new book.

Here is the problem. After I print my new book and delete it, I want to go back to original document (that is still open) clear out my data on the sheet input new data and run my macro again.

I always get an error cause the second time that I use my macro without closing and opening the original workbook I get an error. It has created a new book2 and my macro looks for book1.

any suggestions?

 

[dave3009]

Hi Montana34

Welcome to the Board

Glad you got some results from here, can you provide the code you're using so the experts can advise more adequately on your situation.

Cheers


Dave

 

[Montana34]

Here is the code as I have it now

Sub Extract_Data()

'this macro assumes that your first row of data is a header row.
'will copy a row from one worksheet, to another blank workbook
'IF there is a 0 in column N
'Variables used by the macro
Application.ScreenUpdating = False
Dim FilterCriteria
Dim CurrentFileName As String
Dim NewFileName As String
Dim aShtLst As Variant

'Get the current file's name
CurrentFileName = ActiveWorkbook.Name
'Activate Material list
ActiveWorkbook.Sheets("Complete Material list").Activate
'Unprotect Sheet
ActiveSheet.Unprotect
'Select Range
'(note you can change this to meet your requirements)
Range("A1:g500").Select
'Apply Autofilter
Selection.AutoFilter
FilterCriteria = "yes"
'NOTE - this filter is on column N (field:=14), to change
'to a different column you need to change the field number
Selection.AutoFilter field:=7, Criteria1:=FilterCriteria
'Select the visible cells (the filtered data)
Selection.SpecialCells(xlCellTypeVisible).Select
'Copy the cells
Selection.Copy
'Open new workbook
Workbooks.Add
ActiveWorkbook.Name
'Open a new Sheet
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Print Only"
'Make sure you are in cell A1
Range("A1").Select
'Paste the copied cells
ActiveSheet.Paste
'Auto Fit Columns for data
Columns("A:F").Select
Selection.Columns.AutoFit
'Change the "Quantity" Cell so that is doesnt wrap
Range("C1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'Clear the clipboard contents
Application.CutCopyMode = False
'Activate Library to copy BOM
Windows("Metro_Library1.xls").Activate
'Select BOM sheet to copy to new book
Sheets("BOM Requests").Select
Sheets("BOM Requests").Copy Before:=Workbooks("Temp Print").Sheets("Print Only")
Sheets("BOM Requests").Select
'Print your needed materials on the print Only sheet
Sheets(Array("Print Only", "BOM Requests")).Select
ActiveWindow.SelectedSheets.PrintPreview
'Delete the print workbook
ActiveWorkbook.Close
'Go back to the original file
Windows("Metro_Library1.xls").Activate
ActiveWorkbook.Sheets("Complete Material list").Activate
'Clear the autofilter
Selection.AutoFilter field:=7
'Take the Autofilter off
Selection.AutoFilter
'Go to A1
Range("C2").Select
Application.ScreenUpdating = True
'Protect sheet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
'Reminder to delete out what you ordered before you close library
MsgBox "Do Not forget to delete out your quantities."
End Sub

 

[Montana34]

I am newb and want to appologize for wasting everones time. I went back and instead of adding a new book I just added a sheet. Then with that sheet active I renamed it, did everything wanted with it. Deleted it and then I was able to do this multiple times without closing the document by renaming the activesheet instead of renaming Sheet1.

Again sorry for wasting everyones time.