Solved: VBA code to copy certain cells from active row?

[Panda]

Hi All,

I have a workbook with 2 sheets. Sheet 1 contains Unit Type, Part Number Part Description, W/O Number, QTY, Process, Sent on date, Received Date. I only want to copy the Unit Type, Part number, Part Descirption, QTY, Date sent and process to another sheet from the active row IE if the information is in row 3 copy cells A3, B3, C3 etc .

How do I go about doing this? I write a macro to do each line by line but cant seem to get it to only pick out the data required from the active row.

Can anybody help me please? Attached is a copy of the spreadsheet

Thanks

Phil

[CharlesH]

Panda,

Here's a simple code that may help you.

[Panda]

Panda,

Here's a simple code that may help you.

Hey Charles H thank you so much for getting back to me spent the day in the office trying to get something to work but gave up by lunchtime. I will try it when i get back in tomorrow and keep u posted.

Thanks again =

[Panda]

The code works but is there a way that i can paste special > values?

Ive tried modifying the code but to no avail.

Thanks in advance

Phil

[Kenneth Hobs]

[VBA]sh1ws.Cells(ActiveCell.Row, 1).Copy
sh2ws.Range("B3").PasteSpecial xlPasteValuesAndNumberFormats[/VBA]

[Panda]

[VBA]sh1ws.Cells(ActiveCell.Row, 1).Copy
sh2ws.Range("B3").PasteSpecial xlPasteValuesAndNumberFormats[/VBA]

Thank you for your quick responce i will give it a go when i get back into the office

Thanks again =

[Panda]

[VBA]sh1ws.Cells(ActiveCell.Row, 1).Copy
sh2ws.Range("B3").PasteSpecial xlPasteValuesAndNumberFormats[/VBA]

I tried this string but it returns run time error 1004 pastespecial method of range class failed.

[Kenneth Hobs]

Obviously you would need the other codes before that. I leave it to you to modify the other lines of code. I just guessed at the format to paste. Of course you can add another paste special if needed.

Note how I commented the line to replace.

[VBA]Sub Panda_Code()
'' you should be active in sheet1
Application.ScreenUpdating = False
Dim sh1ws As Worksheet
Dim sh2ws As Worksheet
Set sh1ws = Worksheets("Sheet1")
Set sh2ws = Worksheets("Sheet2")
sh1ws.Activate '' make sure sheet1 is active.

'sh1ws.Cells(ActiveCell.Row, 1).Copy Destination:=sh2ws.Range("B3")
sh1ws.Cells(ActiveCell.Row, 1).Copy
sh2ws.Range("B3").PasteSpecial xlPasteValuesAndNumberFormats

sh1ws.Cells(ActiveCell.Row, 2).Copy Destination:=sh2ws.Range("B5")
sh1ws.Cells(ActiveCell.Row, 3).Copy Destination:=sh2ws.Range("B7")
sh1ws.Cells(ActiveCell.Row, 5).Copy Destination:=sh2ws.Range("B9")
sh1ws.Cells(ActiveCell.Row, 7).Copy Destination:=sh2ws.Range("B11")
sh1ws.Cells(ActiveCell.Row, 6).Copy Destination:=sh2ws.Range("B13")

Application.CutCopyMode = False
End Sub[/VBA]

[Panda]

Thank you so much it works a treat (I had a mental lapse earlier must ber time to leave the office and go home for the weekend)

Thanks again for the help guys =