Solved: Copying & pasting multiple columns using VBA

[VJ1234]

Hi,
I want to copy alternate columns of an excel file & paste the contents to a new excel file using VBA code. The number of alternate columns will be an user input. Also are the starting row & number of rows.

I'm able to do for a single column. However, the looping is not working for me. My code is as follows:

Sheets(1).Range(Cells(row_start, col_start), Cells(row_end, col_start)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False

Can anybody help me out in moving forward?
Thanks for your time! --- VJ

[Bob Phillips]

For j = 1 To 25 STep 2 '<--- change to suit
Sheets(1).Cells(row_start, j).Resize(row_end - row_start + 1).Copy
Workbooks.Add
ActiveSheet.Paste
Next j
Application.CutCopyMode = False

[VJ1234]

Hi XLD,
Thanks for your time!
I tailored your code as follows:

Dim col_count As Integer
For col_count = 1 To num_cols
Sheets(1).Cells(row_start, col_count).Resize(row_end - row_start + 1).Copy
Workbooks.Add
ActiveSheet.Paste
Next col_count
Application.CutCopyMode = False

The code is working for the first column copy & paste. However, it copies the rest of the contents from the new excel file (where the pasted data is). It creates multiple excel files (qty is the length of the loop).

[Bob Phillips]

Sorry, I don't really get your meaning.

[VJ1234]

The code is copying the first column of data from the master spreadsheet & pastes that to a new excel sheet. Let's say the new file as "Book1.xls". For the next column in the loop, it copies the data from "Book1.xls" rather than the master spredsheet. Also, it creates multiple excel sheets for each loop, which I don't want. The requirement is to copy multiple columns of data (alternate columns) & paste all of them to a single spreadsheet. Hope, it adds clarity.

[Bob Phillips]

Ah, yes of course.

See if this is better



Dim col_count As Integer
With WorkSheets(1)

For col_count = 1 To num_cols

.Cells(row_start, col_count).Resize(row_end - row_start + 1).Copy
Workbooks.Add
ActiveSheet.Paste
Next col_count
End With

Application.CutCopyMode = False

[VJ1234]

Thanks, this is able to copy the data from the same source (master spreadsheet) but the pasting happens in multiple files. I understand the reason for this, as the "Workbooks.Add" is within the loop. I wish to paste all the data to a single xls rather than multiple. Kindly let me know how to achieve this? I can even think of hardcoding the xls name & path to paste the data & pass it for pasting.

[Bob Phillips]

Dim col_count As Integer
Dim this As Worksheet
Dim wb As Workbook
Dim ws As Worksheet

Set this = Activesheet
Set wb = Workbooks.Add
Set ws = wb.Worksheets(1)
With this

For col_count = 1 To num_cols

.Cells(row_start, col_count).Resize(row_end - row_start + 1).Copy ws.Cells(1, col_count)
Next col_count
End With

Application.CutCopyMode = False

[VJ1234]

Great! :clap2: . Works well. But found onething during the data copy. It's not copying alternate columns. It copies successive columns. I Overlooked that before. Sorry for that. Can you please help me out in nailing it? Thanks for your efforts!

[mbarron]

Change this line
For col_count = 1 To num_cols
to
For col_count = 1 To num_cols Step 2

[VJ1234]

Excellent!! Thanks for your help!