0

I want to make a macro that copy's the first and last column to another sheet. I have made a recorded macro that selects column A and L in one sheet called Saldobalanse RHB input and copies them to the sheet: Saldobalanse RHB. The problem with this is that the numbers I need do not ALWAYS appear in the column L, sometimes it is in K or J. That's why I would like to copy the first and the last column - wherever the placement is. Could someone help me edit my recorded macro to fit this? Please and thank you so much in advance.

Sub Makro6()
    Makro6 Makro
    Range("A:A,L:L").Select
    Range("L1").Activate
    Selection.Copy
    Sheets("Saldobalanse RHB").Select
    Range("A1").Select
    ActiveSheet.Paste
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Ellen Sophie
  • 19
  • 1
  • 1
    [Avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) would help clean up this recorder code for one... – Rdster Apr 06 '22 at 12:53
  • `usedrange.columns(1)` and `usedrange.columns(usedrange.columnscount)` ? – Nathan_Sav Apr 06 '22 at 12:56
  • Thank you, I will try to study this link on how to avoid select. When you write usedrange.columns(usedrange.columnscount), do you mean that I should just write that or do I put something else in the parantese? I would like to use CTRL+SHIFT+R for this action to start. Is that possible? – Ellen Sophie Apr 07 '22 at 09:06

1 Answers1

0

One of these examples should work for you:

Range.CurrentRegion

Dim Target As Range
Set Target = Range("A1").CurrentRegion

Target.Columns(1).Copy Sheets("Saldobalanse RHB").Range("A1")
Target.Columns(Target.Columns.Count).Copy Sheets("Saldobalanse RHB").Range("B1")

ActiveSheet.UsedRange

Dim Target As Range
Set Target = ActiveSheet.UsedRange

Target.Columns(1).Copy Sheets("Saldobalanse RHB").Range("A1")
Target.Columns(Target.Columns.Count).Copy Sheets("Saldobalanse RHB").Range("B1")
TinMan
  • 6,624
  • 2
  • 10
  • 20
  • Thank you for your reply. I Tried your code, but what it did was to put a filter on column A and B. Actually what I want to do is select the first column and last columns and copy them to A1 in another Sheet. I tried watching the video someone sent to avoid select. This was a lot of information for me, I realize I will have to study to understand truly how to edit a recorded macro. – Ellen Sophie Apr 07 '22 at 09:03
  • @EllenSophie This code does not add a filter. Download this workbook and try it out: [Copy First and Last Columns](https://docs.google.com/spreadsheets/d/1T0ckTb2RZPBEWKtmfAzk8YVZl9u-2bjm/edit?usp=sharing&ouid=114888086960932662321&rtpof=true&sd=true). The only difference in the download is that I do not use the ActiveSheet. Let me know how it works. You may want to read: [Range.Copy method (Excel)](https://docs.microsoft.com/en-us/office/vba/api/excel.range.copy?f1url=%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vbaxl10.chm144104);k(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue) – TinMan Apr 07 '22 at 16:11
  • @EllenSophie Note: the code will overwrite the data on **Saldobalanse RHB**. – TinMan Apr 07 '22 at 16:16