Tags Tags

How to copy a range of cells and paste into a new workbook in different columns

August 16th, 2011 - 05:45 am ET by Mas | Report spam
Help Create a new topic
Hi all,

I have created a macro that copies and a number of columns and pastes
them in a new workbook in a variety of columns but the macro look
unprofessional as it flickers between copying column A and paste in
column A of the new workbook, copy column C and paste in column B, etc.

I would like to copy a range i.e. column A, C, D, F, G in the open
workbook and paste in a new workbook in columns A, B, F, G, H. without
all the flicker between workbooks etc.

Appreciate any help I can get.
email Follow the discussionReplies 3 repliesReplies Make a reply

Similar topics

Replies

#1 Pete_UK
August 16th, 2011 - 07:51 am ET | Report spam
Put this line near the beginning of your macro:

Application.ScreenUpdating = False

and then after you have done all the copy/paste operations insert this
line near the end:

Application.ScreenUpdating = True

This will prevent the flicker as you switch between different sheets,
by not updating the screen display until you have finished. It will
also speed up your macro, though you might not notice this.

Hope this helps.

Pete

On Aug 16, 10:45 am, Mas wrote:
Hi all,

I have created a macro that copies and a number of columns and pastes
them in a new workbook in a variety of columns but the macro look
unprofessional as it flickers between copying column A and paste in
column A of the new workbook, copy column C and paste in column B, etc.

I would like to copy a range i.e. column A, C, D, F, G in the open
workbook and paste in a new workbook in columns A, B, F, G, H. without
all the flicker between workbooks etc.

Appreciate any help I can get.
Replies Reply to this message
#2 Gord
August 16th, 2011 - 09:49 am ET | Report spam
Not using "select" will speed things up also.

A recorded macro will give you this

Columns("B:B").Select
Selection.Copy
Sheets("Sheet6").Select
Range("C1").Select
ActiveSheet.Paste

The equivalent is this with no screen flicker..

Columns("B:B").Copy Destination:=Sheets("Sheet6").Range("C1")


Gord Dibben Microsoft Excel MVP

On Tue, 16 Aug 2011 19:45:18 +1000, Mas wrote:

Hi all,

I have created a macro that copies and a number of columns and pastes
them in a new workbook in a variety of columns but the macro look
unprofessional as it flickers between copying column A and paste in
column A of the new workbook, copy column C and paste in column B, etc.

I would like to copy a range i.e. column A, C, D, F, G in the open
workbook and paste in a new workbook in columns A, B, F, G, H. without
all the flicker between workbooks etc.

Appreciate any help I can get.
Replies Reply to this message
#3 Gord
August 16th, 2011 - 09:57 am ET | Report spam
Or this if all you need is values.

Sheets("Sheet6").Range("C:C").Value = _
Worksheets("Sheet1").Range("B:B").Value


Gord

On Tue, 16 Aug 2011 06:49:57 -0700, Gord wrote:

Not using "select" will speed things up also.

A recorded macro will give you this

Columns("B:B").Select
Selection.Copy
Sheets("Sheet6").Select
Range("C1").Select
ActiveSheet.Paste

The equivalent is this with no screen flicker..

Columns("B:B").Copy Destination:=Sheets("Sheet6").Range("C1")


Gord Dibben Microsoft Excel MVP

On Tue, 16 Aug 2011 19:45:18 +1000, Mas wrote:

Hi all,

I have created a macro that copies and a number of columns and pastes
them in a new workbook in a variety of columns but the macro look
unprofessional as it flickers between copying column A and paste in
column A of the new workbook, copy column C and paste in column B, etc.

I would like to copy a range i.e. column A, C, D, F, G in the open
workbook and paste in a new workbook in columns A, B, F, G, H. without
all the flicker between workbooks etc.

Appreciate any help I can get.
email Follow the discussion Replies Reply to this message
Help Create a new topicReplies Make a reply
Search Make your own search