StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Depending on how <em>different</em> your workbooks are formatted. Here is two way to handle this:</p> <p><strong>Adapt your macro</strong></p> <p>Instead of copying the whole column, you can copy paste, only the values you want to.<br> Here is an example:</p> <pre><code>Sub CopyTest() Dim rSource As Range, rTarget As Range Dim lEnd As Long lEnd = Range("A65536").End(xlUp).Row Set rSource = Workbooks("Local Workbook.xlsm").Worksheets("Sheet3").Range("A1:A" & lEnd) Set rTarget = Workbooks("Nonlocal Workbook.xlsm").Worksheets("Sheet1").Range("A9") rSource.Copy Destination:=rTarget End Sub </code></pre> <p><strong>Use a formula</strong></p> <p>If your data are <strong>not in the same order</strong>, you'd better use a <code>VLOOKUP</code> formula.</p> <p>See <a href="http://www.techonthenet.com/excel/formulas/vlookup.php" rel="nofollow">how it works</a>.</p> <p>Don't hesitate to post another question with what you've built for some help. Please give as much details as possible so we could help you the best way.</p> <p><strong>[EDIT] Another try following the comments</strong></p> <pre><code>Option Explicit Dim wTarget As Workbook Sub mainCopy() Dim bGo As Boolean bGo = True 'Add a new workbook to copy the data - do you want the user to select one? Set wTarget = Application.Workbooks.Add() Do While bGo CopyTest bGo = MsgBox("Do you want to import data from another workbook?", vbYesNo, "Continue?") Loop End Sub Sub CopyTest() Dim rSource As Range, rTarget As Range Dim lEnd As Long, lCol As Long Dim ws As Worksheet Dim vFile As Variant vFile = Application.GetOpenFilename("Excel-files,*.xls", _ 1, "Select One File To Open", , False) 'if the user didn't select a file, exit sub If TypeName(vFile) = "Boolean" Then Exit Sub Workbooks.Open vFile For Each ws In ActiveWorkbook.Worksheets 'do you need to copy the columns separately? ' For lCol = 1 To 10 'find the last cell of the 10th column lEnd = ws.Cells(65536, 10).End(xlUp).Row Set rSource = ws.Range("A1:J" & lEnd) 'How can we define the target worksheet? Set rTarget = wTarget.Worksheets("Sheet1").Range("A9") rSource.Copy Destination:=rTarget ' Next lCol Next ws End Sub </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload