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:)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.