Macro in excel 2007

[Geoff Walker]

Hi I am trying to write a macro in excel 2007 and am not having much luck.

I enter data into Sheet1 of a workbook. The data is not in adjoining cells or columns.

I want to print this sheet, then copy the data to sheet3 (in the same workbook) and then clear all the data in sheet1.

I can do a macro which covers the printing and deleting the data, but I am really struggling with the copying to the other worksheet.

I have attached the file and would be very very grateful if someone could tell me where I am going wrong.

Many thanks in advance,

Geoff

 

[Nebu]

Hi:

hope this is what you are looking for. I did not get to populate Column C "Type", rest all the fields are populated from the order form into Sheet 3. I have written the code in excel 2010, But you can copy it to 2007 excel it should work.

Sub Printing()
'
' Printing Macro
'
Application.ScreenUpdating = False

Dim copyRange As Range, cel As Range, pasteRange As Range, erow As Long, ecolumn As Long
Dim cnt As Integer, inc As Integer, fCell As Integer
Dim vArr

Set copyRange = Sheet1.Range("E7:E11,E13:E14,E16:E18,E26,E28,H3:H5,H7:H11,H13:H14,H16:H18,H27:H29,H31:H33")
erow = Sheet4.Cells(Rows.Count, "A").End(xlUp).Row + 1
For Each cel In copyRange
    cel.Copy
    vArr = Split(Cells(1, cel.Column - 1).Address(True, False), "$")
    Set pasteRange = Sheet4.Range("A1:BB1").Find(what:=Sheet1.Range(vArr(0) & cel.Row).Value, LookAt:=xlWhole)
    On Error Resume Next
    ecolumn = pasteRange.Column
    If Not pasteRange Is Nothing Then
        Sheet4.Cells(erow, ecolumn).PasteSpecial xlPasteValues
        Sheet1.Range("D31").Copy
        Sheet4.Range("BB" & erow).PasteSpecial xlPasteValues
        Sheet1.Range("G26:H26").Copy
        Sheet4.Range("AS" & erow).PasteSpecial xlPasteValues
    End If
Next
cnt = Application.WorksheetFunction.Count(Sheet1.Range("E21:E25"))
fCell = 24
For inc = 1 To cnt
Sheet1.Range("E" & 20 + inc & ":H" & 20 + inc).Copy
Sheet4.Cells(erow, fCell).PasteSpecial xlValues
fCell = fCell + 4
Next
    ActiveWorkbook.Save
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Thanks

 

[Geoff Walker]

Hi Nebu,

You are a star! Thank you so much for this. Can you think of how I can populate the Product Code, Q, Unit Price and Total fields?

Regards

Geoff

 

[Geoff Walker]

Hi Nebu,

I have now fixed this by adding more columns next to each of the columns and then adding the cell references to the list. I then hid the extra columns, protected the worksheet so no one could see them.

I have checked it several times and it works perfectly.

Thanks again for all of your help. I would not have been able to get it done without you.

Regards

Geoff

 

[David Evans]

Nice Solution, Nebu - I liked it and learned from it - thank you!

 

[Nebu]

Thanks Dave

Geoff:

Happy that you got it sorted.