I want to copy the contents of the cell A10 into c44:f44( and merge the cells c44:f44). this needs to be repeated. i.e. A11 would need to be copied to c45:f45(merged), A12 would be copied to c46:f46 and so on. I do not want to copy the whole column of A:A. I only want a range i.e. A10- empty cell.
Help in VBA copy
- Thread starter rexer
- Start date
NARAYANK991
Excel Ninja
Hi ,
Can you try this ?
[pre]
[/pre]
Narayan
Can you try this ?
[pre]
Code:
Public Sub Copy_Merge()
Dim rs As Range, rd As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet5") ' Change as required
ws.Activate
Set rs = Range("A10") ' Source : Change as required
Set rd = Range("C44:F44") ' Destination : Change as required
i = 0
Do
rs.Offset(i, 0).Copy
rd.Offset(i, 0).Resize(1, 1).PasteSpecial xlPasteAll
rd.Offset(i, 0).Resize(1, rd.Columns.Count).Merge
i = i + 1
Loop Until rs.Offset(i, 0) = ""
Set ws = Nothing
Set rs = Nothing
Set rd = Nothing
End SubNarayan
wow!! it works man!! thanks a ton!! I am very new to VBA and not really sure how this works. Would appreciate it if you could explain it a bit. I did make one modification to your code.. I added the below to center the cell.
rd.Offset(i, 0).HorizontalAlignment = xlCenter
need to understand what the offset does, also have no idea why the set ws = nothing does not make the initial values = null..I try to use the macro record functionality and then try re-engineering the code. Thanks again for your help
rd.Offset(i, 0).HorizontalAlignment = xlCenter
need to understand what the offset does, also have no idea why the set ws = nothing does not make the initial values = null..I try to use the macro record functionality and then try re-engineering the code. Thanks again for your help
NARAYANK991
Excel Ninja
Hi ,
I think the only things that need explanations are the OFFSET and RESIZE functions ; for an explanation , you can see the answers to this question :
http://chandoo.org/forums/topic/offsetresize-vba
The Set ws = Nothing statement is basically a kind of cleaning up statement ; when you use the Set statement at the beginning of the procedure , memory is assigned to the variable ; when you use the Set ... = Nothing , that allocated memory is de-allocated , so that it can now be reused for other variables.
Narayan
I think the only things that need explanations are the OFFSET and RESIZE functions ; for an explanation , you can see the answers to this question :
http://chandoo.org/forums/topic/offsetresize-vba
The Set ws = Nothing statement is basically a kind of cleaning up statement ; when you use the Set statement at the beginning of the procedure , memory is assigned to the variable ; when you use the Set ... = Nothing , that allocated memory is de-allocated , so that it can now be reused for other variables.
Narayan
sasi251904
New Member
Hi All, I wanted to copy data of required criteria from one excel sheet to another excel sheet. I have written the Following VBA code how ever there's an error persisting as "1004" Run time error. i am using Excel 2007(the code written is for a command button)can any one please help me on this.
x=2
do while (x,1)<> " "
if (x,4)>=20 then
worksheets("sheet1").rows(x).copy
worksheets("sheet2").activate
erow=activesheet.cells(rows.count,1).end(xlup).offset(1,0)
activesheet.paste destination:=woksheets("sheet2").rows(erow)
endif
worksheets("sheet1").activate
x=x+1
loop
end sub
x=2
do while (x,1)<> " "
if (x,4)>=20 then
worksheets("sheet1").rows(x).copy
worksheets("sheet2").activate
erow=activesheet.cells(rows.count,1).end(xlup).offset(1,0)
activesheet.paste destination:=woksheets("sheet2").rows(erow)
endif
worksheets("sheet1").activate
x=x+1
loop
end sub
SirJB7
Excel Rōnin
@sasi251904
Hi!
Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.
Perhaps you'd want to read the green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).
Regards!
Hi!
Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.
Perhaps you'd want to read the green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).
Regards!