Formula does not populate the correct column

[Brutium]

Hello all,

Thanks to the help of someone else in this forum I was able to partially solve my dilemma.

I have a formula which creates sheets based on a template, and then populate a summary sheet. Unfortunately the columns that the macro fills up are not he correct ones. The macro fills up starting from a column to the right of where it should start from. I cannot make my macro start correctly.

Any help would be GREATLY appreciated.

Thank you
Brutium

 

[PaddyD]

it might help to provide the macro code

 

[Brutium]

Hello PaddyD

This is the macro I am using:

Sub Addsheets()
Dim LR As Long, i As Long, MasterSheet As Long
Dim rngTopOfList As Range
Dim wsName As String
Dim n As Integer

n = 1
With Sheets("tools")

Set rngTopOfList = .Range("B3")

LR = .Cells(.Rows.Count, rngTopOfList.Column).End(xlUp).Row

MasterSheet = ActiveWorkbook.Sheets.Count

If LR > rngTopOfList.Row Then

For i = 1 To LR - rngTopOfList.Row
ActiveWorkbook.Sheets(MasterSheet).Copy _
after:=Worksheets(MasterSheet + i - 1)
ActiveWorkbook.Sheets(MasterSheet + i).Name = _
rngTopOfList.Offset(i).Value '''Can be replaced with wsName if the following line is moved before.

''' Here begins changes
wsName = rngTopOfList.Offset(i).Value

ActiveWorkbook.Sheets("Class").Range("C9").Offset(0, n).Value = _
"=" & wsName & "!M9"
n = n + 1
'''
Next i

End If

ActiveWorkbook.Sheets(MasterSheet).Name = rngTopOfList.Value
End With
Set rngTopOfList = Nothing
End Sub

As I said it starts populating in the wrong column. It should start from C9.

Any idea on how I can solve this?

Brutium

 

[PaddyD]

vba's not really my thng but try:

ActiveWorkbook.Sheets("Class").Range("B9").Offset(0, n).Value = _

...instead of:

ActiveWorkbook.Sheets("Class").Range("C9").Offset(0, n).Value = _

 

[Brutium]

No PaddyD,

It did not work. All it did was shift my inputs to the right. What I need to do is have the same formula that I created for the row 9 to replicate down. I copied the formula down, but it did not work since the code does not know the sheets that are created by the macro (I think).

Brutium