PDA

View Full Version : Macro for Special Copy Paste Format



buhay
09-26-2010, 12:31 PM
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
09-27-2010, 06:35 AM
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 beSheets("Sheet1").Range("A1:A" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlup).Row).CopyTo paste to the last row of Column A the code would beSheets("Sheet2").Range("A" & Rows.Count).End(xlup).Offset(1,0) 'enter .PasteSpecial.....etc for the parameters you want at the end of this

buhay
09-27-2010, 11:17 AM
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:



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


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
09-27-2010, 11:55 AM
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
09-27-2010, 12:18 PM
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
09-27-2010, 12:50 PM
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
09-27-2010, 01:53 PM
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:)

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

Execute Sub Total()with the sample attached.

Thanks again for helping me out:)