Copy range of cells to other sheet

etaver87

New Member
Joined
Feb 15, 2016
Messages
16
Hi everyone,

I have a table of data in cells C8:AR1000 of Sheet 1.

I now want only to copy certain columns of that table to B2 of Sheet 2, i.e. leave out certain columns when copying over to sheet 2. Specifically, I want to leave out columns J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD and AE. How can I do that?

Many thanks for your help.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi everyone,

I have a table of data in cells C8:AR1000 of Sheet 1.

I now want only to copy certain columns of that table to B2 of Sheet 2, i.e. leave out certain columns when copying over to sheet 2. Specifically, I want to leave out columns J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD and AE. How can I do that?

Many thanks for your help.
Hi etaver87, welcome to the boards.

If I am understanding you correctly, try out the following in a COPY of your workbook:

Code:
Sub CopyTable()
Sheets("Sheet1").Range("C8:I1000").Copy Destination:=Sheets("Sheet2").Range("B2")
Sheets("Sheet1").Range("AF8:AR1000").Copy Destination:=Sheets("Sheet2").Range("I2")
Application.CutCopyMode = False
End Sub
 
Upvote 0
assume we have this setup in sheet1:


Excel 2010
ABCDEFGH
1copycopyavoidavoidavoidcopy
2Columns to avoidFruitsBasketValuecommentsOwnerDate
35M22E57akldfonda2016-01-5
46C70Z62adlally2016-01-7
57E78A56qdavalid2016-01-15
6I57K63adltheif2016-01-9
7A4B82adlally2016-01-12
8Y73G32adlally2016-01-3
9W24U3adjohn2016-01-19
10N87A98atheif2016-01-19
11L17P21adagfdsgally2016-01-23
12Y43Q91dgfonda2016-01-11
13H61T43dgjohn2016-01-19
14W62B38dgtheif2016-01-11
15S79L29dhfjgcrook2016-01-12
16Y4U53jhkally2016-01-25
17X5F52hcrook2016-01-26
18V11U3kdjohn2016-01-27
19R68P50vxvalid2016-01-1
20B43W56vxcrook2016-01-15
21J97L22vxjohn2016-01-19
22E60M52bchfuecrook2016-01-12
23I67Y63tstheif2016-01-22
24U36Y91gfsfonda2016-01-7
25T40U14gdtheif2016-01-24
26Z10J60sfjohn2016-01-6
27A44P67wwgcrook2016-01-7
Sheet1
Cell Formulas
RangeFormula
C1=CHOOSE(ISERROR(MATCH(COLUMN(),Columns2Avoid,0))+1,"avoid","copy")
D1=CHOOSE(ISERROR(MATCH(COLUMN(),Columns2Avoid,0))+1,"avoid","copy")
E1=CHOOSE(ISERROR(MATCH(COLUMN(),Columns2Avoid,0))+1,"avoid","copy")
F1=CHOOSE(ISERROR(MATCH(COLUMN(),Columns2Avoid,0))+1,"avoid","copy")
G1=CHOOSE(ISERROR(MATCH(COLUMN(),Columns2Avoid,0))+1,"avoid","copy")
H1=CHOOSE(ISERROR(MATCH(COLUMN(),Columns2Avoid,0))+1,"avoid","copy")
Named Ranges
NameRefers ToCells
Columns2Avoid=Sheet1!$A$3:OFFSET(Sheet1!$A$3,,,COUNT(Sheet1!$A:$A))


** column A has the number corresponding to the columns we would like not to copy

** the 1st row above each column in the table tags the columns with a string "copy" or "avoid" based on whether it column number is in the array of columns to avoid or not.

** I have a named range that sizes up dynamically and fit the array of the corresponding numbers of columns to avoid

the following VBA code when activated will copy the respective columns to sheet2; however the columns that are avoided in sheet1 will leave a blank spot at their parallel ranges in sheet2 .

Code:
Option Explicit

Public Sub testting()


Sheet2.UsedRange.Clear


Dim i As Integer


Dim firstC As Integer: firstC = 3
Dim lastC As Integer
lastC = Application.WorksheetFunction.CountA(Sheet1.Range("1:1")) + 2






For i = firstC To lastC


If Sheet1.Cells(1, i) = "copy" Then


    Sheet2.Range(Sheet2.Cells(2, i), Sheet2.Cells(Sheet1.Cells(Sheet1.Rows.Count, i).End(xlUp).Row, i)) = _
    Sheet1.Range(Sheet1.Cells(2, i), Sheet1.Cells(Sheet1.Cells(Sheet1.Rows.Count, i).End(xlUp).Row, i)).Value


End If


Next i
    
End Sub


A cheezy solution that combines VBA and some formulas on the sheet, not perfect but does the described job i guess.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top