Macro for Special Copy Paste Format

[buhay]

Does anyone know a macro that copy the columns A and B from sheet 1 (which are not fixed) and paste it to sheet 2so that the result looks like below.

The macro is supposed to copy and paste ABC from sheet 1 to sheet 2. ABC should be paste on sheet 2 colA but below the last filled row(number of rows is not fixed and change from time to

time). EFG on col B should be pasted to sheet 2 colB and be located below on the left next to the last filled of colC. The number of CHF on sheet2 colD should be same as the final total

number of filled rows.




Many thanks in advance


Sheet 1

------colA-------colB

row1---ABC-------EFG

row2---ABC-------EFG

row3---ABC-------EFG








Sheet 2

------colA------colB------colC------colD

row1---EFD----------------XYZ-------CHF

row2---EFD----------------XYZ-------CHF

row3---EFD----------------XYZ-------CHF



Sheet 2 result

------colA------colB------colC------colD

row1---EFD----------------XYZ-------CHF

row2---EFD----------------XYZ-------CHF

row3---EFD----------------XYZ-------CHF

row4---ABC-------EFG----------------CHF

row4---ABC-------EFG----------------CHF

row6---ABC-------EFG----------------CHF

[Simon Lloyd]

I've not looked at your example and because of your explanation above i'm not inclined to, however, do some of the work yourself, record yourself on the macro recorder performing the actions.

If you want to copy all cells used in Column A the code would be[vba]Sheets("Sheet1").Range("A1:A" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlup).Row).Copy[/vba]To paste to the last row of Column A the code would be[vba]Sheets("Sheet2").Range("A" & Rows.Count).End(xlup).Offset(1,0) 'enter .PasteSpecial.....etc for the parameters you want at the end of this[/vba]

[buhay]

Thank you very much, after doing some macro recording I got stucked to the following problem:

I need a macro that adds "BTC" on row20 colC right below CTB and copy paste "BTC" and drag it down to the end of the rows which is row40 see also sample file



------colA------colB------colC

row1-Date----------------CTB

row2-Date----------------CTB

row...-Date----------------CTB
.
.
.

row17-Date----------------CTB

row18-Date----------------CTB

row19-Date----------------CTB

row20-Date----------------

row21-Date----------------
.
.
.

row...-Date----------------

row38-Date----------------

row39-Date----------------

row40-Date----------------


it should look like this after using the macro

------colA------colB------colC

row1-Date----------------CTB

row2-Date----------------CTB

row...-Date----------------CTB
.
.
.

row17-Date----------------CTB

row18-Date----------------CTB

row19-Date----------------CTB

row20-Date----------------BTC

row21-Date----------------BTC
.
.
.

row...-Date----------------BTC

row38-Date----------------BTC

row39-Date----------------BTC

row40-Date----------------BTC


I am using the following procdure but for reason it doesn't work:

[vba]

Sub FormatRest2()

Xls = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row



With ActiveSheet

Set LastCell6 = .Cells(.Rows.Count, "I").End(xlUp)

If IsEmpty(LastCell6) Then

'do nothing

Else

Set LastCell6 = LastCell6.Offset(1, 0)

End If

End With

LastCell6.Select

ActiveCell.FormulaR1C1 = "BTC"
Selection.Copy

With ActiveSheet

Set LastCell7 = .Cells(.Rows.Count, "I").End(xlUp)

If IsEmpty(LastCell6) Then

'do nothing

Else

Set LastCell7 = LastCell7.Offset


End If

End With

Range("I & LastCell7.Offset: I" & Xls).Select
ActiveSheet.Paste

ActiveSheet.Paste

End Sub

[/vba]
I've been trying for hours to figure it out but without any success

" Range("I & LastCell7.Offset: I" & Xls).Select" gives me an error message: method range of object global failed

Sombody help please

[Simon Lloyd]

That doesn't appear to be recorded code!

Please supply the link(s) to the other threads in other forums that you have posted this question in!

[buhay]

I haven't posted this question in any other forum. I googled around and found some procedure and tried it out to use it to format my sheets but without any success

[Simon Lloyd]

In that case why did you not do as i suggested and actually do some of the work yourself and record the task and try adapting it with the code i gave you?

[buhay]

In that case why did you not do as i suggested and actually do some of the work yourself and record the task and try adapting it with the code i gave you?

I've been busy with cooking. I have figured it out finally

[VBA]Sub FormatRest2()

Xls = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row


With ActiveSheet

Set LastCell7 = .Cells(.Rows.Count, "I").End(xlUp)

If IsEmpty(LastCell7) Then

'do nothing

Else

Set LastCell7 = LastCell7.Offset(1, 0)

End If

End With

LastCell7.Select
ActiveCell.FormulaR1C1 = "BTC"
End Sub
Sub FormatRest3()

Xls = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row


With ActiveSheet

Set LastCell = .Cells(.Rows.Count, "G").End(xlUp)

If IsEmpty(LastCell) Then

'do nothing

Else

Set LastCell = LastCell.Offset(0, 2)

End If

End With
LastCell.Select
ActiveCell.FormulaR1C1 = "BTC"
Selection.Copy

Range(Selection, Selection.End(xlUp)).Select

ActiveSheet.Paste
Application.CutCopyMode = False






End Sub

Sub Total()

FormatRest2
FormatRest3

End Sub[/VBA]

Execute Sub Total()with the sample attached.

Thanks again for helping me out