Selecting multiple cells in an active row and copying to the next sheet

[onefastmx5]

Help!

Im using Excel 2010 and want to create a macro that allows me to cut the cells A:S and U in the current active row and paste in the same locations on the next page, at the bottom of a growing list. I would like formulas to be copied also.

Alternatively, cut and paste the entire row, but do not paste over the data already in column T on the destination page.

Ive got this so far:
ActiveSheet
Range(Cells(.Row, "A"), Cells(.Row, "S")).Select.Copy
Selection.Sheets("Work Commenced").Range("a", "S").End(xlUp).Offset(1, 0).Paste
ActiveCell.EntireRow.Delete

Application.CutCopyMode = False

and im sure its totally wrong.

Help please!

Adam.

 

[Jeffrey Smith]

Will this work?

Sub CopyFormulas()
Dim NewSht As Worksheet
Dim NewRow As Long
Dim ThisRow As Long
Set NewSht = Sheets("Work Commenced")
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
ThisRow = ActiveCell.Row
NewRow = NewSht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(ThisRow, "A").Copy NewSht.Cells(NewRow, "A")
Cells(ThisRow, "S").Copy NewSht.Cells(NewRow, "S")
'ActiveCell.EntireRow.Delete
Application.CutCopyMode = False
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[onefastmx5]

Will this work?

Sub CopyFormulas()
Dim NewSht As Worksheet
Dim NewRow As Long
Dim ThisRow As Long
Set NewSht = Sheets("Work Commenced")
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
ThisRow = ActiveCell.Row
NewRow = NewSht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(ThisRow, "A").Copy NewSht.Cells(NewRow, "A")
Cells(ThisRow, "S").Copy NewSht.Cells(NewRow, "S")
'ActiveCell.EntireRow.Delete
Application.CutCopyMode = False
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

No it didnt work. This only copied the active cell to the bottom of the existing list on sheet"Work Commenced".

I had originally found:
Sub Progress()
ActiveCell.EntireRow.Cut Sheets("Work Commenced").Range("A65536").End(xlUp).Offset(1, 0)
ActiveCell.EntireRow.Delete

End Sub

Which is great! But, how can I modify the range so it only copy and pastes A:S? (happy for whole row to be deleted at end)

Any other suggestions on Range?

 

[Jeffrey Smith]

Did you want to copy column "A" and column "S" then paste them onto "Work Commenced" into columns "A" and "S"? Or did you want to paste them into columns "A" and "B"?

The code I posted does copy only the values in Columns "A" and "S". It pastes them in columns "A" and "S". If you want to paste them into different columns then change the code accordingly.

'Change the letter in red to whatever column you need
Cells(ThisRow, "S").Copy NewSht.Cells(NewRow, "[COLOR=#ff0000]S[/COLOR]")
'
'Any row in the code that has apostrophe at the beginning turns it into a comment
'If you want to temporarily disable code, just add an apostrophe
'so, this next line has been commented out.  To enable it, just remove the apostrophe
'ActiveCell.EntireRow.Delete

 

[onefastmx5]

Did you want to copy column "A" and column "S" then paste them onto "Work Commenced" into columns "A" and "S"? Or did you want to paste them into columns "A" and "B"?

The code I posted does copy only the values in Columns "A" and "S". It pastes them in columns "A" and "S". If you want to paste them into different columns then change the code accordingly.

'Change the letter in red to whatever column you need
Cells(ThisRow, "S").Copy NewSht.Cells(NewRow, "[COLOR=#ff0000]S[/COLOR]")
'
'Any row in the code that has apostrophe at the beginning turns it into a comment
'If you want to temporarily disable code, just add an apostrophe
'so, this next line has been commented out.  To enable it, just remove the apostrophe
'ActiveCell.EntireRow.Delete

OK thanks, got it to work now.
Ive entered multiple rows to move col a, b, c etc...
eg:
Cells(ThisRow, "A").Copy NewSht.Cells(NewRow, "A")
Cells(ThisRow, "b").Copy NewSht.Cells(NewRow, "b")
Cells(ThisRow, "c").Copy NewSht.Cells(NewRow, "c")
Cells(ThisRow, "d").Copy NewSht.Cells(NewRow, "d")..... and so on, excluding col T.

Is there a way of consolidating these rows into a range statement or similar? (not important, but would be nice)

Overall, it works, so thanks very much!

Adam.

 

[Jeffrey Smith]

Well, when you consolidate ranges into one and paste them, it removes the blank cell. So, if you copied Column S and U and pasted them into Column S on the next sheet, Column U would end up in Column T. Now, if Column T is blank and you are not overwriting any data in Column T, then Yes, you could copy the entire range and paste it.

 

[Norie]

Adam

You can do this in 2 steps - first A:S and then U.

Intersect(ActiveCell.EntireRow, Range("A:S")).Copy Sheets("Work Commenced").Range("A" & ActiveCell.Row)

Range("U" & ActiveCell.Row).Copy Sheets("Work Commenced").Range("U" & ActiveCell.Row)