Selection AutoFill into other Sheet

Question

The following Code works fine in my Sheet 1. But I want to transfer data from one sheet into an other.

Sub Data_Transfer()
    Dim rowcount As Integer
    Sheets("Eingabe").Select ' Whatever your sheet is
    rowcount = 4
    rowcount = Application.CountA(Range("A:A")) + 1 'get end range

    ActiveSheet.Unprotect 123

    Cells(5, 2).Select 'select the start cell
    'autofill to rowcount
    Selection.AutoFill Destination:=Range("B5:B" & rowcount), Type:=xlFillDefault

    ActiveSheet.Protect 123
End Sub

How is the syntax for the Destination? I tried:

Set fillRange = Worksheets("MDB_to_pA_Dispodaten").Range("B5:B" & rowcount)
Selection.AutoFill Destination:=fillRange, Type:=xlFillDefault

But it doesn't work...

EDIT: --> The AutoFill method of the Range object cannot be executed. Error 1004

EDIT 2: What I need is this:

Sub Daten_Transfer()
    Range("A5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A5:X33").Select
    Selection.Copy
    Sheets("MDB_to_pA_Dispodaten").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("A2").Select
End Sub

But I need it dynamicly! Because on times the user have 33 rows in the first sheet, next day he has 10 rows. I want only transfer the filled rows from Sheet 1 into Sheet 2, because Sheet 2 is a link table to MS Access.

what are you actually trying to do? show some examples of actual data and "before" and "after" scenarios" — DisplayName, Mar 08 '18 at 07:30
I didn't mean write that literally. I meant that your destination range would always need an end row greater than or equal to the start. I am also not sure about autofill across sheets. I am just looking. I have never seen it done. — QHarr, Mar 08 '18 at 07:31
Some suggestions: Don't use `Integer` for row counting, Excel has more rows than `Integer` can handle. I suggest always to use `Long` there is no advantage in using `Integer` in VBA at all. Also avoid using `.Select`, instead use a direct reference: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). This avoids many issues. — Pᴇʜ, Mar 08 '18 at 07:31
In Sheet 1 is like an form. The user can write his data into the cell. One some cell are function. When user is finish, I want to transfer spefici date from sheet 1into formatated sheet 2. — Vegeta_77, Mar 08 '18 at 07:32
use copy or set the range in the other sheet equal to current data. Autofill fills down same page same start cell. — QHarr, Mar 08 '18 at 07:34
as QHarr said. You need to use Copy and Paste. Autofill is the wrong tool for that task. Then determine the last row to get the source range and copy that range. And probably get the last row in the destination sheet, so you can append the data here with a paste. — Pᴇʜ, Mar 08 '18 at 07:39
Ok. But can I Copy and Paste automaticly and dynamicly? I think it's just like my Code on my EDIT 2, but this is static. — Vegeta_77, Mar 08 '18 at 07:46

DisplayName · Accepted Answer · 2018-03-08T11:07:09.553

Edited to add pasting values features

I guess you need something like

Sub Daten_Transfer()
    Range("A5:X" & Cells(Rows.Count, "A").End(xlUp).Row).Copy Destination:=Sheets("MDB_to_pA_Dispodaten").Range("A2")
End Sub

should you want to paste values only, you have two ways:

1) use PasteSpecial method of Range object

Sub Daten_TransferValuesOnly1()    
    Range("A5:X" & Cells(Rows.Count, "A").End(xlUp).Row).Copy
    Sheets("MDB_to_pA_Dispodaten").Range("A2").PasteSpecial xlPasteValues

    Application.CutCopyMode = False 'clear clipboard
End Sub

2) use Value property

Sub Daten_TransferValuseOnly2()
    With Range("A5:X" & Cells(Rows.Count, "A").End(xlUp).Row) ' reference "source" range
        Sheets("MDB_to_pA_Dispodaten").Range("A2").Resize(.Rows.Count, .Columns.Count).value = .value ' write referenced range values in "target" sheet range of same size and starting from A2 
    End With
    Application.CutCopyMode = False
End Sub

I prefer this latter, faster and with no clipboard usage

Oh. I have one more question about your solution :-) How can I paste only values? With your solution I need something like this ".PasteSpecial xlPasteValues", else. — Vegeta_77, Mar 08 '18 at 10:25

Selection AutoFill into other Sheet

1 Answers1