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]

Try:

Private Sub CommandButton4_Click()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet
    Set srcWS = ThisWorkbook.Sheets("Positech")
    srcWS.Range("AL5:AQ24").AutoFilter Field:=6, Criteria1:="<>-"
    srcWS.Range("AL5:AQ24").AutoFilter Field:=5, Criteria1:="<>-"
    Workbooks.Open Filename:=("C:\Users\scott.baugh\Desktop\Blank Quote.xls")
    Set desWS = Sheets("sheet1")
    srcWS.Range("AQ6:AQ24").SpecialCells(xlCellTypeVisible).Copy
    desWS.Range("A21").PasteSpecial xlPasteValues
    srcWS.Range("AM6:AM24").SpecialCells(xlCellTypeVisible).Copy
    desWS.Range("B21").PasteSpecial xlPasteValues
    srcWS.Range("AN6:AN24").SpecialCells(xlCellTypeVisible).Copy
    desWS.Range("C21").PasteSpecial xlPasteValues
    srcWS.Range("AP6:AP24").SpecialCells(xlCellTypeVisible).Copy
    desWS.Range("D21").PasteSpecial xlPasteValues
    srcWS.Range("AL5").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[Evapar18]

That worked perfectly!!!! ray:

After playing around with the code I finally figured out the proper field number and ranges that worked in the other workbooks... thank you sooo very much for everything I truly appreciate all your help and all your patience!

I wish there was something I could to repay you the help!

 

[mumps]

You are very welcome. Your appreciation is repayment enough!!

 

[Evapar18]

Apologizes, but I do have a small question. Is there any way to skip a row when pasting the data into the quote workbook? I have another tab that I have to set this up on as well and there are more items and it drags over to the 2nd sheet of our quote workbook. I just need to skip a single row to continue pasting the rest of the data.

Or should I pose this as a new thread?

 

[mumps]

As written, the macro pastes the data in row 21. I'm not clear on what you mean by:

Is there any way to skip a row when pasting the data

Could you please describe in more detail? If not row 21, then which row?

 

[Evapar18]

See the attached image. https://drive.google.com/open?id=1_H31NgLXNYOCw6HU-iSXrQFsinIVp2S8

You should see that line 48 shows page 1 of 2. The question arose if the values could skip a row and be pasted on the second page once the first page is full. The other BOM takes up the entire first page and a few rows on the second. Basically, I have 27 line items in the BOM and there are only 25 line items on the first page of the quote. To me, this seems like a tall order, but I thought I would ask.

Thanks!

 

[mumps]

In the image, the first row to paste to is now row 24 whereas before it was row 21. Rather than a picture, please upload a copy of the file with all the headers rather than a picture.

 

[Evapar18]

https://drive.google.com/open?id=1Z8sQ4_UB89I0QNWj0qCiD6KrmkjvboT2

My apologies. This is a different quote sheet that was given to me yesterday to use instead of the one I sent out to you.

 

[mumps]

Your BOM sheet currently has room for only 19 items. The Blank Quote has room for 58 items (on 2 pages). Does this mean that the BOM sheet will also have room for 58 items? By the way, we could simplify the whole process if we could delete the "Page 1 of 2" in row 48 or move it to somewhere in the header where it wouldn't affect how the data is pasted. Do you think that would be possible?

 

[Evapar18]

The new BOM has a maximum of 27 rows and the new quote only has 25 rows.

Yeah, that was my first thought and I asked if I could move the Page 1 of 2, but they really wanted to keep it there.

Just to make it easier, I added to more rows to the quote and that seems to work for now. I was just curious if it was possible to skip a row when pasting copied data from one workbook to another.