Newb question

Montana34

New Member
Joined
Sep 13, 2007
Messages
22
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?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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

New Member
Joined
Sep 13, 2007
Messages
22
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

New Member
Joined
Sep 13, 2007
Messages
22
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,497
Messages
5,596,508
Members
414,073
Latest member
Contilly

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