Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 289

# copy dynamic range to a row on the next tab

I have a range of cells on tab "Sample Size Calc".  This is a dynamically generated list of the workingdays for the month.  I would like to copy them into a formula on another sheet so that the cell yields the id number and the date.

Start:
1/1/2010 (list starts in cell I9)
1/4/2010
1/5/2010
1/6/2010
1/7/2010
1/8/2010
1/11/2010
1/12/2010
1/13/2010
1/14/2010
1/15/2010
1/18/2010
1/19/2010
1/20/2010
1/21/2010
1/22/2010
1/25/2010
1/26/2010
1/27/2010
1/28/2010
1/29/2010

Finish: J1 = Cell J1 = id number (9) plus date
J1                    J2
9 1/1/2010      25 1/2/2010

etc.....to dynamically generated end of month date, so the number of rows above and the number of columns here are dynamic depending on the last workday of the month.

I have been playing with the following code, but it need more information to do what I described.

For Each cel In Range(Cells(1, 10), Cells(1, WDays))

cel.Formula = "='Sample Size Calc'!" & Range("I9").Offset(1+1, 0).Address(RowAbsolute:=False, ColumnAbsolute:=False)
Next

Next

Any assistance would be greatly appreciated.  Thanks.

0
ealbrec
• 6
• 5
1 Solution

Commented:

good morning!

can u please attach sample excel file for us to further analyze it..

game-master
0

Author Commented:
Here is a file containing the code I am trying to modify.  When you run the macro, it demonstrates what I am trying to do, but the date does not update correctly.  I know this is probably a pretty simple question, so hopefully I can learn something in the process.  Thanks for the help!
Code-Snipet2.xls
0

Sr. Project ManagerCommented:
Hi,

I used the following formula...

=IF('Sample Size Calc'!I9<>"", 'Cap Man'!\$A\$1& " " &TEXT('Sample Size Calc'!I9, "mm/dd/yyyy")," ")

and i got the following result as shown in the attached file.

Can you confirm if this is what u need?

- Ardhendu
Code-Snipet2.xls
0

Author Commented:
That would be one way to do it, but I was hoping to use the macro because Sample Size Calc.Rang(I9:I29) is dynamic.  The length of the range depends on the value in the Month (I2) and Year (I3) cells.  Otherwise, will have this formula hanging out there at the far right of the spreadsheet.  Thanks.
0

Sr. Project ManagerCommented:
what about the Id? would that be picked up from Cell A1?

and how many rows you have to apply this to?
0

Author Commented:
The Id needs to be picked up from Cell A1.  It really only needs to be done for the first frow and then that can be copied to fill down the spreadsheet for the rest of the rows.  There is a mcro started in the workbook, but I am a novice and could not figure out the code.  Ardhendu's solution will get me where I need to be, but I would like to learn where I went wrong.
0

Sr. Project ManagerCommented:
Hi Ealbrec,

I looked at your code and it seemed to me there was a lot of extra lines of code in it. Here's the cleaned up code which worked for me.

Can you try this once?

Thanks,
Ardhendu
``````Sub CreateInitialCalendar()

Dim rgref As Range
Dim i As Long
Dim r As Long
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ActiveWorkbook.Worksheets("Sample Size Calc")
Set ws2 = ActiveWorkbook.Worksheets("Cap Man")

ws1.Activate
WDays = ws1.Range("I7")
ws1.Range("I10:I39").ClearContents
ws1.Range("I10:I39").NumberFormat = "mm/dd/yyyy"
ws1.Range("I10") = "=IF(WEEKDAY(R[-1]C)=7,R[-1]C+2,IF(WEEKDAY(R[-1]C)=6,R[-1]C+3,R[-1]C+1))"
ws1.Range("I10").AutoFill Destination:=Range("I10 : I" & WDays + 8)

ws2.Activate
ws2.Range("J1:IV1").ClearContents
r = 10
For i = 8 To WDays + 7
ws2.Cells(1, r).Value = ws2.Range("A1") & " " & ws1.Cells(i + 1, 9)
'MsgBox (cel.Value)
r = r + 1
Next i
End Sub
``````
Code-Snipet2.xls
0

Author Commented:
This works very good!  One final thing though, the line of code below.....Is there a way to write the formula so that A1 is only a reference to the cell A1.  That way if the next thing I do is autofill cells J1:IV down the page, the formula will update to A2, A3, A4 etc?

ws2.Cells(1, r).Value = ws2.Range("A1") & " " & ws1.Cells(i + 1, 9)

Looks great though!  Thanks.
0

Sr. Project ManagerCommented:
Hi,
That is quite easy. Here you go!

- Ardhendu
``````Option Explicit
Sub CreateInitialCalendar()
Dim i As Long, r As Integer, n As Integer
Dim ws1 As Worksheet, ws2 As Worksheet
Dim WDays As Integer

Set ws1 = ActiveWorkbook.Worksheets("Sample Size Calc")
Set ws2 = ActiveWorkbook.Worksheets("Cap Man")

ws1.Activate
WDays = ws1.Range("I7")
ws1.Range("I10:I39").ClearContents
ws1.Range("I10:I39").NumberFormat = "mm/dd/yyyy"
ws1.Range("I10") = "=IF(WEEKDAY(R[-1]C)=7,R[-1]C+2,IF(WEEKDAY(R[-1]C)=6,R[-1]C+3,R[-1]C+1))"
ws1.Range("I10").AutoFill Destination:=Range("I10 : I" & WDays + 8)

ws2.Activate
ws2.Range("J1:IV10").ClearContents

For n = 1 To ws2.Cells(65536, "A").End(xlUp).Row
r = 10
For i = 8 To WDays + 7
ws2.Cells(n, r).Value = ws2.Range("A" & n) & " " & ws1.Cells(i + 1, 9)
'MsgBox (cel.Value)
r = r + 1
Next i
Next n
End Sub
``````
Code-Snipet2.xls
0

Author Commented:
The solution addressed the question exactly as it was asked.  Thanks for the assistance!
0

Author Commented:
Ardhendu Thanks, this works perfect!
0

Sr. Project ManagerCommented:
The solution addressed the question exactly as it was asked. Thanks for the assistance!

Hi,

Thanks a lot for the grade!