How to copy specific columns until end of data

[Matt G.]

I'm trying to import data from 2 workbooks into a summary (combination)
workbook. The trouble I'm having, is I want to import only certain
columns and have excel know to stop at the last row with data.

For example, from Workbook1 I want to import columns A, B, C, F, and G
(from Sheet2). I want it to copy up to the last row containing any
data--right now that's row 1046, but next month it could be row 1267.

In workbook2 I'm fine with it copying the CurrentRegion--all columns
are important. BUT, it doesn't include the title/category heads in the
first row (Salesperson, ID#, Product, etc.)...anyone know how to
specify whether or not to include the title header?



Here's what I'm using so far to just copy the CurrentRegion of each
worksheet...

Sub ImportData()

Set Rng1 = Workbooks("Workbook1.xls"). _
Sheets("Sheet2").Range("A1").CurrentRegion
Set Rng2 = Workbooks("Workbook2.xls"). _
Sheets("Production Totals").Range("A1").CurrentRegion
Set Rng3 = Workbooks("Combine.xls"). _
Sheets("Combination").Range("A1")

Rng1.Copy
Rng3.Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Rng2.Copy
Rng3.Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

End Sub

 

[Guest]

Start from the bottom and work your way up to find the last used row, then
define your range based on wherever you want to start from. Take a look at
this example syntax and have a try with that, but post back if you get stuck:-

'Define the last row using End Up
LastRw = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
LastRw = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

'Last cell plus one to bottom of sheet
Set rng = ActiveSheet.Range(Cells(LastRw + 1, "A"), Cells(Rows.Count,
"A"))

'First cell to last cell with data
Set rng = ActiveSheet.Range(Cells(1, "A"), Cells(LastRw, "A"))