Copy two ranges of cells from active sheet and paste in another

[Dazzawm]

I need a code please that will copy 2 ranges of cells from the active sheet and create another sheet and paste in columns A and B. The ranges always start in row 17 but the amount of rows will vary each time. The 2 columns that need copying are column E to column A on created sheet and column M to column B on created sheet. Thanks.

E								M
Test								1​
Test								2​
Test								3​
Test								4​
Test								5​
Test								6​
Test								7​
Test								8​
Test								9​

Result on created sheet

A	B
Test	1​
Test	2​
Test	3​
Test	4​
Test	5​
Test	6​
Test	7​
Test	8​
Test	9​

 

[Dazzawm]

I found this code which sort of does what I need but this is a specific range where the amount of rows will change each file I use it on and obviously it doesnt copy column M. I think I need this sort of thing added but dont know how.

.Range(.Range("E17"), .Range("E" & Rows.Count).End(xlUp))

Sub CopyAndPasteNewSheet

ActiveSheet.Range("E17:E25").Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste

End Sub

 

[Zot]

Try this

Sub CopyAndPasteNewSheet()

Dim nRow As Long
Dim rngData As Range
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set ws2 = ActiveWorkbook.Sheets("Sheet2")

nRow = ws1.Cells(Rows.Count, "E").End(xlUp).Row
Set rngData = Union(ws1.Range("E17", "E" & nRow), ws1.Range("M17", "M" & nRow))
rngData.Copy ws2.Range("A1")

End Sub

 

[Dazzawm]

Try this

Sub CopyAndPasteNewSheet()

Dim nRow As Long
Dim rngData As Range
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set ws2 = ActiveWorkbook.Sheets("Sheet2")

nRow = ws1.Cells(Rows.Count, "E").End(xlUp).Row
Set rngData = Union(ws1.Range("E17", "E" & nRow), ws1.Range("M17", "M" & nRow))
rngData.Copy ws2.Range("A1")

End Sub

Thanks. Does this create the second sheet? And also I need the sheets to be active rather than named sheets as they may be called different on each file.

 

[Zot]

Thanks. Does this create the second sheet? And also I need the sheets to be active rather than named sheets as they may be called different on each file.

No it will not. It is not clear how the workflow in this case. You can create as many sheet but how do you intend to get data from. If it is the same sheet, then no need to new sheet. Are you planning the keep copying new data on Sheet1, for example?

 

[Zot]

This will keep adding new sheets

Sub CopyAndPasteNewSheet()

Dim nRow As Long
Dim rngData As Range
Dim ws1 As Worksheet, ws As Worksheet

Set ws1 = ActiveWorkbook.Sheets("Sheet1")

nRow = ws1.Cells(Rows.Count, "E").End(xlUp).Row
Set rngData = Union(ws1.Range("E17", "E" & nRow), ws1.Range("M17", "M" & nRow))
Set ws = Sheets.Add(After:=Sheets(Sheets.Count))   
rngData.Copy ws.Range("A1")

End Sub

 

[Dazzawm]

This will keep adding new sheets

Sub CopyAndPasteNewSheet()

Dim nRow As Long
Dim rngData As Range
Dim ws1 As Worksheet, ws As Worksheet

Set ws1 = ActiveWorkbook.Sheets("Sheet1")

nRow = ws1.Cells(Rows.Count, "E").End(xlUp).Row
Set rngData = Union(ws1.Range("E17", "E" & nRow), ws1.Range("M17", "M" & nRow))
Set ws = Sheets.Add(After:=Sheets(Sheets.Count)) 
rngData.Copy ws.Range("A1")

End Sub

I get a VB error 400 with a big red cross?

i think I also have the problem of the files it will be used on having merged cells etc..

 

[Zot]

I get a VB error 400 with a big red cross?

i think I also have the problem of the files it will be used on having merged cells etc..

The merged cell probably the problem. I tested it fine with mock up sample.

 

[Dazzawm]

Yes I ran it again and it said 'we can't do that with a merged cell'.

A lot of the cells in the sheet are merged but the data does start in E17 and M17.

 

[Dazzawm]

Thanks for your help, I think I have sorted it.