Excel VBA Copy Paste After Search

Question

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?

h2so4 · Accepted Answer · 2017-04-16 21:35:25Z

1

try this

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

answered Apr 16, 2017 at 21:35

h2so4

1,5771 gold badge10 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shai Rado · Accepted Answer · 2017-04-17 06:51:42Z

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

Collectives™ on Stack Overflow

Excel VBA Copy Paste After Search

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related