CODEWITHSUNDEEP
excelvbamergerange
Gilad Livnat
Gilad Livnat

Reputation: 47

can't merge cells in VBA 2007 worksheet notactive

i'm trying to create a button that makes an automatic work arrangement according to a date and year i give him. it creates a new sheet with the name that in the string - year

Sub Create_Arran_Click()

Dim year As String
Dim sdate As Date
Dim row As Integer

year = Worksheets("Main").Cells(17, 4)
sdate = Worksheets("Main").Cells(18, 4)
Sheets.Add.Name = year

For i = 1 To 56
row = ((i - 1) * 12) + 2
Worksheets(year).Cells(row, 1) = "week " & i
Worksheets(year).Cells(row + 1, 1) = "day"
Worksheets(year).Cells(row, 1).Font.Bold = True
For j = 2 To 15

    (!)Range(Cells(row, j), Cells(row, (j + 1))).Merge 
    Worksheets(year).Cells(row, j) = sdate + (j - 2)
    (!)Range(Cells(row + 1, j), Cells(row + 1, j + 1)).Merge 
    Worksheets(year).Cells(row + 1, j) = Weekday(sdate + (j - 2), vbSunday)

j = j + 2
Next
sdate = sdate + 7
Next'

the problem is that the rows that marked with '!' are not doing the merge in the 'year' sheet. i tried several things like:

          'Worksheets(year).Range(Cells(row, j), Cells(row, (j + 1))).Merge

or sheets(year).Range(Cells(row, j), Cells(row, (j + 1))).Merge or Worksheets(year).activate

but nothing helps anyone?

Upvotes: 1

Views: 3027

Answers (3)

Sooraj N
Sooraj N

Reputation: 11

you need to refer to the sheet when you refer cells also in this format

sheets("year").Range(sheets("year").Cells(i, j), sheets("year").Cells(i, j+3)).merge

Sooraj

Upvotes: 1

Potter Rafed
Potter Rafed

Reputation: 481

when you reference a range from another sheet you need to say where it is when calling Range AND Cells

So rather than writing 

Worksheets(year).Range(Cells(row, j), Cells(row, (j + 1))).Merge

Do this

with Worksheets(year)
.Range(.Cells(row, j), .Cells(row, (j + 1))).Merge
end with

Notice how I reference the sheet both when I call Range and Cells

Upvotes: 1

Daniel Möller
Daniel Möller

Reputation: 86600

When you do Range(Cells(row, j), Cells(row, (j + 1))).Merge you are taking the sheet where the code is writen, and it may not be "Year" worksheet.

Try doing Worksheets(Year).Range(Cells(row, j), Cells(row, (j + 1))).Merge instead.

If necessary, activate before:

Worksheets(Year).Activate
Worksheets(Year).Range(Cells(row, j), Cells(row, (j + 1))).Merge

Upvotes: 0

Related Questions