Copy certain columns to other worksheet

Question

I've created a macro using the record function included in excel to copy information from one sheet to another.

Explanation:

Column A of worksheet A needs to be copied to column A of worksheet B Column B of worksheet A needs to be copied to column B of worksheet B Column I of worksheet A needs to be copied to column C of worksheet B Columns K & J of worksheet A need to be copied to column E of worksheet B

The script below only works in a specific range. Is it possible to let the range depend on the datarange of worksheet A (so pe if the values go from row 1 to row 589, just copy those rows to worksheet B?)

Script so far included below.

Thanks in advance :)

Sub Hulpwerkblad()
'
' Hulpwerkblad Macro
'
' Sneltoets: Ctrl+Shift+H
'
Sheets("Hulpwerkblad").Select
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "='samenvattende meetstaat'!RC"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "='samenvattende meetstaat'!RC"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "='samenvattende meetstaat'!RC[6]"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE('gedetailleerde meetstaat'!RC[6],'gedetailleerde meetstaat'!RC[5])"
    Range("E2").Select
    Columns("E:E").EntireColumn.AutoFit
    Range("A1").Select
    Selection.AutoFill Destination:=Range("A1:A33"), Type:=xlFillDefault
    Range("A1:A33").Select
    Range("B1").Select
    Selection.AutoFill Destination:=Range("B1:B33"), Type:=xlFillDefault
    Range("B1:B33").Select
    Range("C1").Select
    Selection.AutoFill Destination:=Range("C1:C33"), Type:=xlFillDefault
    Range("C1:C33").Select
    Range("E1").Select
    Selection.AutoFill Destination:=Range("E1:E33"), Type:=xlFillDefault
    Range("E1:E33").Select
End Sub

First I highly recommend to read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). This makes your code a lot shorter, faster and easier. • And to find the last used row in a column use eg `LastUsedRow = Worksheets("Hulpwerkblad").Cells(Rows.Count, "A").End(xlUp).Row` to find the last used row in column A. • Try to apply both to your code. If you get stuck or errors come back and update the code in your question. — Pᴇʜ, Oct 15 '18 at 11:55
I'll look into that. As I said, all the 'select' commands are simply there because it's included in the macro record function. — Stoepsteen, Oct 15 '18 at 12:55
Macro recorder can not produce good code. You should always refine the recorded code to make it reliable and stable. — Pᴇʜ, Oct 15 '18 at 12:56
my code skills are pretty much at zero, so using the excel macro record is pretty helpfull imo :) but I'll do my best copy pasting from other examples I can find :) — Stoepsteen, Oct 15 '18 at 13:41

score 0 · Answer 1 · answered Jun 11 '20 at 15:43

is it possible to let the range depend on the datarange of worksheet A (so pe if the values go from row 1 to row 589, just copy those rows to worksheet B?)

Script so far included below.

Thanks in advance :)

Dear Stoepsteen

Try this

     Sub cop()
Dim r1 As Worksheet
Dim r2 As Worksheet
Set r1 = ThisWorkbook.Worksheets(1)
Set r2 = ThisWorkbook.Worksheets(2)

r1.[a1].CurrentRegion.Copy r2.[a1]     

End Sub

Copy certain columns to other worksheet

1 Answers1