PDA

View Full Version : VBA- copy/union certain cells



Johnatha
10-21-2014, 06:08 AM
HI!

I'm trying to copy the first 3 cells of a row AND 1 cell + the cell to its right. (Example, "A:C" and "D:E"... then "A:C" and "F:G, and so on) See the bolded part of my code so far! For Case 2, what do I change to copy "A:C" and "F:G"? I know it must be a union function, but can't figure it out. Once I have this, I can create case 3,4,5,etc.


Case 1
Set NewBook = Workbooks.Add
Workbooks.Open "M.....xlsm"
With Worksheets("2014")
.AutoFilterMode = False
.Cells(1).AutoFilter Field:=4, Criteria1:=23
If .AutoFilter.Range.Rows.Count > 1 Then
.UsedRange.Columns(1).Offset(1).Resize(, 5).SpecialCells(12).Copy
NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial
End If
.AutoFilterMode = False
End With

Case 2
Workbooks.Open "M.....xlsm"
With Worksheets("2014")
.AutoFilterMode = False
.Cells(1).AutoFilter Field:=6, Criteria1:=23
If .AutoFilter.Range.Rows.Count > 1 Then
.UsedRange.Columns(1).Offset(1).Resize(, 7).SpecialCells(12).Copy
NewBook.Worksheets("Sheet1").Range("A1").End(xlDown).Offset(1, 0).PasteSpecial
End If
.AutoFilterMode = False
Workbooks("....xlsm").Close False
End With

Thanks!

westconn1
10-21-2014, 01:29 PM
is worksheets"("2014") in workbook M.......xlsm?
you have not specified in your code
newbook is added in case 1, bu not in case 2, though it is used in both

try like

set rng1 = .UsedRange.Columns(1).Offset(1).Resize(, 3).SpecialCells(12)
set rng2 = rng1.offset(,5).resize(,2)
set rng = union(rng1, rng2)
rng.copyi did not test this copied correctly