Without seeing your sheet or knowing what you're trying to accomplish, it's difficult to tell what methods would be better suited to reach your goal. Your procedure looks very hard-coded.
Instead of worrying about how you can make your code
shorter, you should worry about structuring your data to make your code work without having to hard code.
Can you set up your data in such a way that you don't have to put data into specific cells?
Do you need to select sheets?
Here's what I came up with:
Code:
Sub CopyToDebiteuren()
Application.ScreenUpdating = False
Dim destinationRange As Excel.Range
Dim sourceWksht As Excel.Worksheet
Dim sourceArray As Variant
Dim i As Long, j As Long
Set destinationRange = Sheets("Debiteuren").Range("B" & Rows.Count).End(xlUp)
Set sourceWksht = Sheets("Factuur")
With sourceWksht
sourceArray = Array(.Range("N14").Value, .Range("N13").Value, .Range("N16").Value, .Range("D13").Value, .Range("N41").Value)
End With
With destinationRange
Range(.Offset(1, 0), .Offset(1, 4)).Value = sourceArray
End With
' why are you selecting?
' Sheets("Factuur").Select
' Range("D13").Select
MsgBox "Gegevens gekopieerd. "
Factuur_bewaren
NieuweFaktuur
End Sub
Quote:
Originally Posted by Pinokkio
A question,
This macro can be written shorter ?
Code:
Sub CopyToDebiteuren()
Application.ScreenUpdating = False
With Sheets("Debiteuren").Range("B65536").End(xlUp)
.Offset(1, 0).Value = Sheets("Factuur").Range("N14").Value
.Offset(1, 1).Value = Sheets("Factuur").Range("N13").Value
.Offset(1, 2).Value = Sheets("Factuur").Range("N16").Value
.Offset(1, 3).Value = Sheets("Factuur").Range("D13").Value
.Offset(1, 4).Value = Sheets("Factuur").Range("N41").Value
End With
Sheets("Factuur").Select
Range("D13").Select
MsgBox "Gegevens gekopieerd. "
Factuur_bewaren
NieuweFaktuur
End Sub
|
macro shorter
