Copy between Sheets

[hiteshkataria]

Hi,

I have data in Sheet1 of the Excel sheet. I want to copy the content of Column C & D of Sheet1 into Sheet2 using a macro.

I am able to do using below logic (But i think there is a simple logic):
Get the Last record count of Col C
For i=1 to Last
sheet2.columns(r,c)=sheet1.columns(i,3)
Next i

Since I am using for loop, it takes long time if the data is huge.
Could you please let me know whether my approach is good or there exists a simpler method.

 

[mosiki]

If its just those columns then

Sub CopyColumns()
 
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    Worksheets("Sheet1").Columns("C:D").Copy Destination:=Worksheets("Sheet2").Columns("C:D")
 
 
 
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
 
End Sub

 

[hiteshkataria]

Thanks, mosiki!

I forgot to tell, I need just the text (without copying the Formats).
Suppose, the source contains the data in Bold,Red Colour, ... the destination should copy text in default settings of color,type,font...

 

[mosiki]

Try

Sub CopyColumns()
 
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
Worksheets("Sheet1").Columns("C:D").Copy
Worksheets("Sheet2").Columns("C:D").PasteSpecial xlPasteValues
 
 
 
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
 
End Sub

 

[hiteshkataria]

It works! thanks.

Can you tell me use of these:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Thanks.

 

[mosiki]

Everytime you make a change on an excel sheet, Excel will re-draw the screen and recalculate every formula, etc on the sheet.

If you are making a lot of changes via a macro it is best to turn of the screenupdating (re-draw sheet) and calculation engine, make the changes, and then turn them back on at the end. This speeds up the macro considerably as it only has to do this once.

Eg. If you had a thousand rows of data that you were looping through it would mean excel would re-draw the screen and re-calcualte a thousand times if you didnt turn off autocalc and screenupdating, would take way too long.

 

[hiteshkataria]

Thanks a lot for info.

 

[hiteshkataria]

If you don't mind, can you tell me what would be the best way to copy the Unique sorted records from Sheet1 to sheet2 via macro.

I am planning to do:
Copy from Sheet1 to sheet2 and then use Advance Filter option in sheet2 and sort.
(For advanced filter and Sort, I am planning to record a macro and get the code)

Thanks.

 

[mosiki]

How is the data layout on sheet 2 after copied across ?

Headers in row 1 for example? Whick column header are you going to sort by?

Need more info.

 

[hiteshkataria]

Copy from Sheet1 Column C and D to Sheet2(column C&D)
Filter Unique using source Sheet2(C&D)
Sort Key1= C Key2=D, Column contains headers