CODEWITHSUNDEEP
vba
nstzy
nstzy

Reputation: 33

For Next loop with two statements - Copy & Paste

I have a workbook with 2 worksheets, the 1st having a large range of data and formulas and the 2nd containing two empty tables to be populated with specific date from sheet1. Sheet1 has a column of dates Range("DK7:DK39") that range from 5/1/1986 to 5/1/2018. Any time the first date is changed in this column ("DK7"), all of the other dates in the following years will auto-update to the same day in the given year. With these changes, two separate columns will be repopulated with new data that is determined based on date-specific data.

I am trying to run a code that will loop through a range of days for which I need data (5/1 through 8/28), changing the value of cell("DK7") to each date in that range. However, I need the loop to also copy and paste data from the two data columns ("DS7:DS38") & ("DT7:DT38") into the two tables on sheet2 which have the years 1986-2017 as the y-axis column and 5/1 through 8/28 as the x-axis row. I have figured out how to loop the date through the Cell("DK7") but can't figure out the copy & pasting into the 2nd worksheet in respective year / date columns. Any help greatly appreciated, thanks.

Private Sub CommandButton1_Click()

Dim i As Date
For i = ("5/1/1986") To ("8/28/1986")
Range("DK7").Value = i

Worksheets(1).Range("DS7:DS38").Copy
Worksheets(2).Range("D4:E36").PasteSpecial

Next i

End Sub

Upvotes: 2

Views: 39

Answers (1)

Vityata
Vityata

Reputation: 43595

This is a good way to loop through the dates:

Public Sub TestMe()

    Dim i As Date
    For i = DateSerial(1986, 5, 1) To DateSerial(1986, 8, 28)
        Debug.Print i
    Next i
End Sub

Make sure you are using DateSerial() function as there is a big difference between date formats around the world.

Upvotes: 0

Related Questions