copy data between worksheets based on list selection

[chuck.bailey]

So I’m new here, and not nearly as knowledgeable about this as I’m sure many of you are. But I’m trying to learn. I need help with something that I imagine is actually pretty easy, I just don’t even know where to begin with it.

I have a Calendar that can be updated for the date and time you want using a Dropdown box for year and month. What I want to do, is be able to import the data from a different worksheet into different weeks on the calendar. I have a physical training 16 week program that I made with some formulas in the data and I want to be able to put each training week on the accurate day of the calendar.

Basically what I want to do (but excel won’t let me do it this way because of the size) is set several cells to copy and paste from one worksheet to another based on the INDIRECT function of 1 cell. So every week will have a drop down for the training week, and whichever training week I select will auto fill the entire weeks schedule onto my calendar. Any way anyone can help would be greatly appreciated.

[chuck.bailey]

Anyone have any ideas? I think that an INDEX function can accomplish what I want, but do not seem to be able to get the INDEX function to work the way I would like it to.

[chuck.bailey]

So I still haven’t figured this out is anyone can help. I was thinking I could do something in VBA with this, something like

IF.worksheet1.cells(B4) = “week1” THEN select.worksheet2.(area needing copied).copy.selection

paste.selection.(area wanting it copied to).




Or something like that, but this is my limited understanding of how to work within VBA. Any help would be greatly appreciated.

[chuck.bailey]

Ok, I have got this kind of working. The only thing I cannot figure out, is getting it to copy the array from one worksheet and print n another worksheet. But the IF, THEN function works, and the saving array to memory and printing the array somewhere else works.

I need help moving this array between worksheets though.

This, by the way, took me more than 10 hours on youtube learning this. hahaha




Sub Week1()

Dim Plans(5, 4) As Variant, xrow As Integer, xcol As Integer, _
rowIndex As Long, colIndex As Long, i As Long, j As Long
rowIndex = 0
colIndex = 0
xrow = 3
xcol = 1

If Cells(xrow, 1).Value = "Week 1" Then


Do Until Cells(xrow, xcol).Value = ""
Do Until Cells(xrow, xcol).Value = ""
Plans(rowIndex, colIndex) = Cells(xrow, xcol).Value
colIndex = colIndex + 1
xcol = xcol + 1
Loop
xcol = 1
colIndex = 0

xrow = xrow + 1
rowIndex = rowIndex + 1
Loop

xrow = 12
xcol = 1

For i = 0 To UBound(Plans, 1)
For j = 0 To UBound(Plans, 2)
Cells(xrow, xcol).Value = Plans(i, j)
xcol = xcol + 1
Next
xcol = 1
xrow = xrow + 1
Next i

End If
End Sub

[chuck.bailey]

Alright, well I solved it. Of course if you have a better way of doing it, send it to me and I'll try it. but here's what I've come up with

Option Explicit
Sub WorkSheetActivate()
Worksheets("Data").Activate
Worksheets("ACFT Calendar").Activate
End Sub
Sub Week1()
Dim Plans(5, 4) As Variant, xrow As Integer, xcol As Integer, _
rowIndex As Long, colIndex As Long, i As Long, j As Long
rowIndex = 0
colIndex = 0
xrow = 4
xcol = 1
If Cells(xrow, 1).Value = "Week1" Then

Do Until Worksheets("Data").Cells(xrow, xcol).Value = ""
Do Until Worksheets("Data").Cells(xrow, xcol).Value = ""
Plans(rowIndex, colIndex) = Worksheets("Data").Cells(xrow, xcol).Value
colIndex = colIndex + 1
xcol = xcol + 1
Loop
xcol = 1
colIndex = 0
xrow = xrow + 1
rowIndex = rowIndex + 1
Loop
xrow = 12
xcol = 1
For i = 0 To UBound(Plans, 1)
For j = 0 To UBound(Plans, 2)
Worksheets("ACFT Calendar").Cells(xrow, xcol).Value = Plans(i, j)
xcol = xcol + 1
Next
xcol = 1
xrow = xrow + 1
Next i
End If
End Sub