Macro to copy specific columns in Excel from another spreadsheet

KD999 · #1 06-29-2012, 10:45 AM

I need a excel macro which will open another spreadsheet which has 21 columns and for each row select the data in Column C, I-N and then paste that information into another spreadsheet in particular columns:
*
Spreadsheet 1********* Spreadsheet 2
C************************** A
I*************************** N
J*************************** O
K************************** P
L************************** Q
M************************** R
N************************** S*******
*

On my second spreadsheet I’d like to default ‘/’ in the other columns if the cells in Column A are empty.

Catalin.B · #2 07-20-2012, 08:58 AM

You can try this code. The code is checking the column C ; if there are no more data in this column rows, the code stops, so i couldn't find a reason to put that "/" in destination column A.

Code:

Sub OpenCopy()
Dim OpenFile, fileName As String, LastRow As Long, i As Integer
OpenFile = Application.GetOpenFilename("Excel files,*.xl*", _
                           1, "Select destination file", , False)
       Workbooks.Open fileName:=OpenFile
       fileName = ActiveWorkbook.Name
       Application.ScreenUpdating = False
       Application.DisplayAlerts = False
     LastRow = ThisWorkbook.Sheets("Sheet1").Cells(ThisWorkbook.Sheets("Sheet1").Rows.Count, "C").End(xlUp).Row
                           
      For i = 2 To LastRow
      ThisWorkbook.Sheets("Sheet1").Cells(i, "C").Copy
      ActiveWorkbook.Sheets("Sheet1").Cells(i, "A").Select
      ActiveWorkbook.Sheets("Sheet1").Paste
      
      ThisWorkbook.Sheets("Sheet1").Range("I" & i & ":N" & i).Copy
      ActiveWorkbook.Sheets("Sheet1").Cells(i, "N").Select
      ActiveWorkbook.Sheets("Sheet1").Paste
      Next i
      Application.CutCopyMode = False
      Workbooks(fileName).Close savechanges:=True
      
      Application.DisplayAlerts = True
      Application.ScreenUpdating = True
       
End Sub

This topic should be in Excel programming forum, i guess...
Cheers, Catalin

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to separate specific columns from the data	kumar	Word	1	06-04-2012 01:46 PM
Macro: Exporting Data to a LEGIBLE Excel Spreadsheet	jeffcoleky	Word VBA	6	05-08-2012 08:24 AM
Adding columns in specific rows only	mhays	Excel	5	01-17-2012 09:13 AM
CAUTION!! Sorting a spreadsheet with hidden columns will trash your data.	psmaster@earthlink.net	Excel	0	11-24-2009 11:54 AM
Looking for a specific spreadsheet	DivideByZer0	Excel	3	11-10-2009 05:58 PM