#1
|
|||
|
|||
Macro to copy specific columns in Excel from another spreadsheet
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. |
#2
|
|||
|
|||
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 Cheers, Catalin |
Thread Tools | |
Display Modes | |
|
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 |