CODEWITHSUNDEEP
vbaexcelcopy-paste
P.Schmid
P.Schmid

Reputation: 21

Excel VBA Copy Paste After Search

My macro should simply copy paste a block of data.

My problem is, that it only copies the data into one column, and I'm not sure how to fix that. 

Sheets("Auswertung").Select
Daten = Range("L4:R17")
Sheets("Übersicht").Select

With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

For S = 2 To Range("nz85").End(xlToLeft).Column
    If Cells(85, S) = Sheets("Auswertung").Range("L3") Then
        Range(Cells(86, S), Cells(98, S)) = Daten
        Exit For
    End If

Row 85 contains dates. I want to copy the data in a certain date. Daten contains the information of the date if "L3" and the next 6 days. So one week of data. I thought I can simply put the data of one week into Daten and the paste it on the first day of the week in the hope that it will also paste the next 6 days. The problem is that it only pastes in the date in one column.

How could i fix that?

Upvotes: 0

Views: 79

Answers (2)

Shai Rado
Shai Rado

Reputation: 33672

Important Note: you are trying to Paste to 13 rows Range(Cells(86, S), Cells(98, S)) is = 98 - 86 + 1 = 13 rows. While Daten = Range("L4:R17") is actually 14 rows. Therefore, you are getting your error, because the size of the Range and your Daten array don't match.

The same goes for the size of your Column, you can copy 6 columns into 1. (as mentioned also by @h2so4)

So either your Daten need to be modified, or maybe it's Range(Cells(86, S), Cells(99, S)) ?

Also, you can achieve what you are trying to without all the unnecessary Select of the different sheets. Just use fully qualified Range and Cells like the code below:

Daten = Sheets("Auswertung").Range("L4:R17").Value

With Worksheets("Übersicht")
    For s = 2 To .Range("NZ85").End(xlToLeft).Column
        If .Cells(85, s) = Sheets("Auswertung").Range("L3").Value Then
            ' ******* MODIFY ONE OF THE PARAMETERS AT THE LINE BELOW *******
            .Range(.Cells(86, s), .Cells(98, s)).Value = Daten
            Exit For
        End If
    Next s
End With

Upvotes: 0

h2so4
h2so4

Reputation: 1577

try this

Range(Cells(86, S), Cells(98, S+6)) = Daten

Upvotes: 1

Related Questions