Copy Columns to other Sheet - Xtreme Visual Basic Talk

08-07-2003, 08:11 AM

bcschouten bcschouten is offline

Newcomer

Join Date: Aug 2003

Posts: 15

Copy Columns to other Sheet

Hello,

I want to copy the columns of sheet1 to sheet3 in a different order.
I have made a table on sheet2 in wich order the columns have to be coppied. This is my code ( for 3 columns ):

Sub Button1()
Sheets("Sheet1").Select
Columns("B:B").Select <- I want B:B to be the variable placed in the table on sheet2
Selection.Copy
Sheets("Sheet3").Select
Columns("A:A").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Columns("A:A").Select
Selection.Copy
Sheets("Sheet").Select
Columns("B:B").Select
ActiveSheet.Paste

Sheets("Sheet1").Select
Columns("C:C").Select
Selection.Copy
Sheets("Sheet3").Select
Columns("C:C").Select
ActiveSheet.Paste
End Sub

Hope you guys can help me out.

Tnx

08-07-2003, 08:25 AM

herilane

Unashamed geek

Retired Moderator
* Expert *

Join Date: Jul 2003

Location: London, England

Posts: 8,988

What is your question / problem?

08-07-2003, 08:37 AM

bcschouten bcschouten is offline

Newcomer

Join Date: Aug 2003

Posts: 15

I have a table on sheet 2 with how I want to copy the colums from sheet 1 to sheet 3. Like this:

Column On sheet 1 : Column On sheet 3
B : A
A : B
C : C

Now column B of sheet 1 is coppied to sheet 3, etc.

But I want to make the destination column variable. But I don't know how to change this sentence: Columns("B:B").Select in something that works. I have tried this:
Columns("Sheet(2).Cells(2,2):Sheet(2).Cells(2,2)").Select

08-07-2003, 12:17 PM

Insomniac

Contributor

* Expert *

Join Date: Mar 2003

Location: Adelaide,Australia

Posts: 681

This should copy the specified columns of a table in Sheet2 assuming your table is in Columns A and B starting from A1.

Code:

Sub cpyColumns()
'assumes column references in Sheet2 format in cellA1 "B" & cellB1 "A"
'copies column combinations until a blank cell in sheet2 columnA is found
Dim cpy As String
Dim pst As String
Dim rw As Long

    For rw = 1 To Sheet2.Range("A1").End(xlDown).Row
        cpy = Sheet2.Cells(rw, 1)
        pst = Sheet2.Cells(rw, 2)
        Sheet1.Columns(cpy & ":" & cpy).Copy Sheet3.Columns(pst & ":" & pst)
    Next
    Application.CutCopyMode = False
End Sub

08-08-2003, 03:41 AM

bcschouten bcschouten is offline

Newcomer

Join Date: Aug 2003

Posts: 15

Thanks. It Works !!!!

« Previous Thread | Next Thread »

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is Off Forum Rules

Forum Jump

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
copy from recordset to excel sheet	alazarou	Excel	4	08-06-2003 04:19 AM
copy date from sheet and rename sheet	gumby	Word, PowerPoint, Outlook, and Other Office Products	2	02-19-2003 12:52 PM
MSHFlexGrid copy pasting rows, columns, etc	Ryo2	General	1	10-14-2002 06:54 PM
Hekp Importing Excel	JonM	Word, PowerPoint, Outlook, and Other Office Products	2	05-01-2002 09:45 AM
Copy excel Sheet	roger	General	3	02-23-2001 09:07 AM