I am new to VBA and am trying to write some code to select a range of cells dependent on the content and copy and paste it into another worksheet.
I have attached a sample file so that it is easier to see what i want to do. Basically, i want to select all the cells in column A that contain ADE, for example, and then for those copy all the relative cells in columns B, D, E and L.
The problem i have is that there will be a different number of ADE's each time i create the spreadsheet, and the first occurance of ADE will not always be in cell A4, so i need the code to be able to search the sheet for the first occurance and select the relevant number to copy.
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 14/05/2007 by Erin Redwood
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Dim i As Integer
Dim j As Integer
j = 1
'run thru lines 2 to 13
For i = 2 To 13
Sheets("Sheet1").Select
Cells(i, 1).Select
If Cells(i, 1).Value = "ADE" Then
'copy value in column A
Cells(i, 1).Select
Selection.Copy
Sheets("Sheet2").Select
Cells(j, 1).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
'copy value in column B
Cells(i, 2).Select
Selection.Copy
Sheets("Sheet2").Select
Cells(j, 2).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
'copy value in column D
Cells(i, 4).Select
Selection.Copy
Sheets("Sheet2").Select
Cells(j, 3).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
'copy value in column E
Cells(i, 5).Select
Selection.Copy
Sheets("Sheet2").Select
Cells(j, 4).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
'copy value in column L
Cells(i, 12).Select
Selection.Copy
Sheets("Sheet2").Select
Cells(j, 5).Select
ActiveSheet.Paste
j = j + 1
End If
Next i
End Sub
You're welcome to rate this post!
If your problem is solved, please use the Mark thread as resolved button
I'm not sure which bit of it isn't working, but it is not pasting anything at all into Sheet 2 and i am just getting an error box with 400 written in it...