Help in VBA copy

[rexer]

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.

 

[NARAYANK991]

Hi ,


Can you try this ?

[pre]

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 Sub

[/pre]
Narayan

 

[rexer]

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

 

[NARAYANK991]

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

 

[sasi251904]

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

 

[SirJB7]

@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!