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!