Copy several columns to a new workbook in a different order\location - VBA

[Evapar18]

I have been searching most of this morning looking for a solution. I have not seen anything like what I am wanting to do.

I have a Bill Of Materials with a part number, Description, Cost, and QTY. Some of those rows do not list an item, so I don't want those copied. Just the cells that have a value in the QTY column.

Once copied I need to open another quoting spreadsheet and place the copied items. Needless to say, the items in the quote spreadsheet are not in the order as my BOM.

I did find some information to copy a column to a new workbook. So I tried to start out with the basics but while trying out these tips for coping columns to an existing spreadsheet I have had nothing but bad luck. Instead of just opening the existing workbook, it opens a new workbook and stops with a message "method range of object _global failed". So I know the code I have been trying to use is seriously flawed.

Private Sub CommandButton4_Click()Dim partNum As Double
Dim Descrp As String
Dim cost As Currency
Dim qty As Single
Dim qdata As Workbook


Worksheets("Positech").Select

Worksheets("Positech").Copy
partNum = Range("AM7:AM24")
Descrp = Range("AN7:AN24")
cost = Range("AP7:AP24")
qty = Range("AQ7:AQ24")


Workbooks.Open Filename:=("C:\Users\scott.baugh\Desktop\Blank Quote.xls")
Worksheets("sheet1").Select


Worksheets("sheet1").Range("A24") = qty
Worksheets("sheet1").Range("B24") = partNum
Worksheets("sheet1").Range("E24") = Descrp
Worksheets("sheet1").Range("N24") = cost




End Sub

I know this would probably select the whole column, but I am just trying to get it to work before I start adding further complication to not copy the values where there is no "-" QTY.

I hope this all makes sense.

EP

 

[mumps]

It is possible with a little more work. You would basically have to count the number of rows being copied and split them between the 2 pages. It would make it lot easier if the Page 1 of 2 could be moved.