Hi. I need a macro to copy certain columns from one sheet to another. I already have code that sort of works that I have
taken from elsewhere but I need little bit help to correctly finish it off. I have sheets called "Hyperlink" (the
source sheet) and ME22 (the destination sheet).
The code that I have so far does the following:
It takes info from column 4 from source sheet and places in column 1 on destination sheet
It takes info from column 5 from source sheet and places in column 2 on destination sheet
It places letter "X" in column 3 on destination sheet
It takes info from column 14 from source sheet and places in column 4 on destination sheet
It places the following to col 5 on destination sheet - concatenation of text "Supp Recoll" + column 21 and column
14 from source sheet
Now the code that I so far have is the following:
Dim a, i As Long, maxval As Double: Application.ScreenUpdating = False
With Sheets("Hyperlink"): a = .Range(.[a2], .Cells(Rows.Count, "b").End(xlUp).Offset(, 22)): End With
With Sheets("ME22"): maxval = Application.Max(.Range(.[a2], .Cells(Rows.Count, "a").End(xlUp)))
For i = 1 To UBound(a)
a(i, 1) = a(i, 4): a(i, 2) = a(i, 5): a(i, 3) = "X": a(i, 4) = a(i, 14): a(i, 5) = "Supp Recoll "
& a(i, 21) & a(i, 14)
Next: .Cells(Rows.Count, "b").End(xlUp).Offset(1, -1).Resize(UBound(a), UBound(a, 2)) = a: End With
Application.ScreenUpdating = True: End Sub
1) Now the problem with this current code is that it carries on copying information from source sheet to destination sheet as
far as it column 24. I want it to stop at column 5 (column E) as the last column being filled in. Rest of the columns should
stay clear. How can I adjust my code to make this happen?
2) Line : With Sheets("ME22"): maxval = Application.Max(.Range(.[a2], .Cells(Rows.Count,
"a").End(xlUp))). How could I change this line as the "maxval" was defined in the original code that I
used to learn and to get this far. This feature is not required in my macro but I have no idea how to remove it without
rendering the rest of the code useless.
All ideas are ideas are welcome. I have attached a test spreadsheet.