VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows

[NoneTheWiser]

Good afternoon,

I'm trying to create a macro which will move data from one tab to another, but the data isn't sequential. The original tab has 36 columns and the second tab only has 20, meaning there are 16 columns which don't require transferring. I only want to transfer the 20 columns if there is a value in the last column. I can copy the data across if it is static and is in the same rows constantly, but this can't be guaranteed. Can someone advise how this can be applied to dynamic data where the data in column C may not start in C2, and end in M3 (and the same for the remaining columns).

ActiveSheet.Columns("A:AJ").AutoFilter Field:=36, Criteria1:="VALUE"

Range("C1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Range("C2:M3").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste


Sheets("Sheet1").Select
Range("R2:R3").Select
Selection.Copy
Sheets("Sheet2").Select
Range("L1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste


Sheets("Sheet1").Select
Range("X2:AC3").Select
Selection.Copy
Sheets("Sheet2").Select
Range("M1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Sheets("Sheet1").Select
Range("AH2:AI3").Select
Selection.Copy
Sheets("Sheet2").Select
Range("S1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

The next issue I have, is I want to delete the rows from sheet 1 after is has been copied to sheet 2 but the VBA code isn't working.

Sheets("Pipeline").Select
ActiveSheet.Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete

Can anyone shed some light on this issue as to why it's not working? When the macro gets to the last section I receive an error message "Run-time error '424' object required" on the below line.

Range(Selection, Selection.End(xlDown)).Select.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete

Again appreciate any thoughts and comments on this.

 

[DanteAmor]

Hi and welcome to MrExcel.

Delete "Select" word:

Range(Selection, Selection.End(xlDown)).Select.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete

Try:

Range(Selection, Selection.End(xlDown)).Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete