Defining destination Range

J

Juan Correa

Hello...

I'm working on a small macro for my boss.

Here is what I have so far:

Sub MonthAndPivot()
' Activate the Data Sheet before anything else
Sheets("Data").Activate

' Declarations
Dim LastRow As Long, LastCol As Long

LastRow = Range("A65536").End(xlUp).Row
LastCol = Range("IV1").End(xlToLeft).Column



' Create the "Booked Month" Column
Range("IV1").End(xlToLeft).Select
Selection.Copy
Range("IV1").End(xlToLeft).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
ActiveCell.FormulaR1C1 = "Booked Month"
ActiveCell.EntireColumn.AutoFit


' Populate the Month Column with new Monts
Range("IV1").End(xlToLeft).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = _

"=CHOOSE(MONTH(RC[-25]),""Jan"",""Feb"",""Mar"",""Apr"",""May"",""Jun"",""Jul"",""Aug"",""Sep"",""Oct"",""Nov"",""Dec"")"
Selection.AutoFill Destination:=Range("AL2:AL" & LastRow),
Type:=xlFillDefault
Calculate

End Sub

The part that is giving me headaches is the following:
Selection.AutoFill Destination:=Range("AL2:AL" & LastRow),
Type:=xlFillDefault

It works as it is, but I don't want to have the absolute references in there
because I'm not sure that the raw data will always have the same number of
columns.
Is there a way that I can set the Destination range in this particular case
without having the absolute references there?

thanks
Juan Correa
 
D

Dave Peterson

You can drop the .select's and .activate's and your code may be easier to
understand and update:

Option Explicit
Sub MonthAndPivot()

Dim DataWks As Worksheet
Dim LastRow As Long
Dim LastCol As Long

Set DataWks = Worksheets("Data")

With DataWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

.Columns(LastCol).Copy
.Columns(LastCol + 1).PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

.Cells(1, LastCol + 1).Value = "Booked Month"
.Columns(LastCol + 1).AutoFit

.Range(.Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1)).Formula _
= "=text(w2,""mmm"")"
End With

Application.Calculate

End Sub




Juan said:
Hello...

I'm working on a small macro for my boss.

Here is what I have so far:

Sub MonthAndPivot()
' Activate the Data Sheet before anything else
Sheets("Data").Activate

' Declarations
Dim LastRow As Long, LastCol As Long

LastRow = Range("A65536").End(xlUp).Row
LastCol = Range("IV1").End(xlToLeft).Column



' Create the "Booked Month" Column
Range("IV1").End(xlToLeft).Select
Selection.Copy
Range("IV1").End(xlToLeft).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
ActiveCell.FormulaR1C1 = "Booked Month"
ActiveCell.EntireColumn.AutoFit


' Populate the Month Column with new Monts
Range("IV1").End(xlToLeft).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = _

"=CHOOSE(MONTH(RC[-25]),""Jan"",""Feb"",""Mar"",""Apr"",""May"",""Jun"",""Jul"",""Aug"",""Sep"",""Oct"",""Nov"",""Dec"")"
Selection.AutoFill Destination:=Range("AL2:AL" & LastRow),
Type:=xlFillDefault
Calculate

End Sub

The part that is giving me headaches is the following:
Selection.AutoFill Destination:=Range("AL2:AL" & LastRow),
Type:=xlFillDefault

It works as it is, but I don't want to have the absolute references in there
because I'm not sure that the raw data will always have the same number of
columns.
Is there a way that I can set the Destination range in this particular case
without having the absolute references there?

thanks
Juan Correa
Click to expand...
 
J

Juan Correa

Thank you very much Dave. This worked like a charm. And your code is way
cleaner!

I do have a follow up question.

Looking at the code you provided, I see this bit:
..Range(.Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1)).Formula =
"=text(w2,""mmm"")"

This formula is looking for the value of W2 ,converting it to Text in mmm
format and then copying that formula all the way down to the last row of
existing data on the worksheet. That is exactly what I need. My problem
comes because I'm not sure that the data to be looking at will always be in
column W.

I know that the information currently on column W will always be included
with the raw data and I know that it will alwasy be labeled the same way (Is
that the correct terminology?).
This column will always be labeled "Expected Book Month".
Is there a way that I can reference that column based on its label instead
of using the absolute reference? That way I can be sure that no matter where
the data is, the formula will always look for it in the right place.

Thanks again

Juan Correa


Dave Peterson said:
You can drop the .select's and .activate's and your code may be easier to
understand and update:

Option Explicit
Sub MonthAndPivot()

Dim DataWks As Worksheet
Dim LastRow As Long
Dim LastCol As Long

Set DataWks = Worksheets("Data")

With DataWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

.Columns(LastCol).Copy
.Columns(LastCol + 1).PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

.Cells(1, LastCol + 1).Value = "Booked Month"
.Columns(LastCol + 1).AutoFit

.Range(.Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1)).Formula _
= "=text(w2,""mmm"")"
End With

Application.Calculate

End Sub




Juan said:
Hello...

I'm working on a small macro for my boss.

Here is what I have so far:

Sub MonthAndPivot()
' Activate the Data Sheet before anything else
Sheets("Data").Activate

' Declarations
Dim LastRow As Long, LastCol As Long

LastRow = Range("A65536").End(xlUp).Row
LastCol = Range("IV1").End(xlToLeft).Column



' Create the "Booked Month" Column
Range("IV1").End(xlToLeft).Select
Selection.Copy
Range("IV1").End(xlToLeft).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
ActiveCell.FormulaR1C1 = "Booked Month"
ActiveCell.EntireColumn.AutoFit


' Populate the Month Column with new Monts
Range("IV1").End(xlToLeft).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = _

"=CHOOSE(MONTH(RC[-25]),""Jan"",""Feb"",""Mar"",""Apr"",""May"",""Jun"",""Jul"",""Aug"",""Sep"",""Oct"",""Nov"",""Dec"")"
Selection.AutoFill Destination:=Range("AL2:AL" & LastRow),
Type:=xlFillDefault
Calculate

End Sub

The part that is giving me headaches is the following:
Selection.AutoFill Destination:=Range("AL2:AL" & LastRow),
Type:=xlFillDefault

It works as it is, but I don't want to have the absolute references in there
because I'm not sure that the raw data will always have the same number of
columns.
Is there a way that I can set the Destination range in this particular case
without having the absolute references there?

thanks
Juan Correa
Click to expand...
Click to expand...
 
D

Dave Peterson

You can search for it in row 1 (right?).

This seemed to work ok:

Option Explicit
Sub MonthAndPivot()

Dim DataWks As Worksheet
Dim LastRow As Long
Dim LastCol As Long
Dim ExpBookMonthCell As Range
Dim StrToFind As String

StrToFind = "Expected Book Month"

Set DataWks = Worksheets("Data")

With DataWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
With .Rows(1) 'is that where the are???
Set ExpBookMonthCell = .Rows(1).Find(what:=StrToFind, _
after:=.Cells(.Cells.Count), _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If ExpBookMonthCell Is Nothing Then
MsgBox StrToFind & " wasn't found--stopping"
Exit Sub
End If

.Columns(LastCol).Copy
.Columns(LastCol + 1).PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

.Cells(1, LastCol + 1).Value = "Booked Month"
.Columns(LastCol + 1).AutoFit

'fixed a typo--I missed a dot in front of the second .cells()
.Range(.Cells(2, LastCol + 1), .Cells(LastRow, LastCol + 1)).Formula _
= "=text(" & .Cells(2, ExpBookMonthCell.Column).Address(0, 0) _
& ",""mmm"")"
End With

Application.Calculate

End Sub

Juan said:
Thank you very much Dave. This worked like a charm. And your code is way
cleaner!

I do have a follow up question.

Looking at the code you provided, I see this bit:
.Range(.Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1)).Formula =
"=text(w2,""mmm"")"

This formula is looking for the value of W2 ,converting it to Text in mmm
format and then copying that formula all the way down to the last row of
existing data on the worksheet. That is exactly what I need. My problem
comes because I'm not sure that the data to be looking at will always be in
column W.

I know that the information currently on column W will always be included
with the raw data and I know that it will alwasy be labeled the same way (Is
that the correct terminology?).
This column will always be labeled "Expected Book Month".
Is there a way that I can reference that column based on its label instead
of using the absolute reference? That way I can be sure that no matter where
the data is, the formula will always look for it in the right place.

Thanks again

Juan Correa

Dave Peterson said:
You can drop the .select's and .activate's and your code may be easier to
understand and update:

Option Explicit
Sub MonthAndPivot()

Dim DataWks As Worksheet
Dim LastRow As Long
Dim LastCol As Long

Set DataWks = Worksheets("Data")

With DataWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

.Columns(LastCol).Copy
.Columns(LastCol + 1).PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

.Cells(1, LastCol + 1).Value = "Booked Month"
.Columns(LastCol + 1).AutoFit

.Range(.Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1)).Formula _
= "=text(w2,""mmm"")"
End With

Application.Calculate

End Sub




Juan said:
Hello...

I'm working on a small macro for my boss.

Here is what I have so far:

Sub MonthAndPivot()
' Activate the Data Sheet before anything else
Sheets("Data").Activate

' Declarations
Dim LastRow As Long, LastCol As Long

LastRow = Range("A65536").End(xlUp).Row
LastCol = Range("IV1").End(xlToLeft).Column



' Create the "Booked Month" Column
Range("IV1").End(xlToLeft).Select
Selection.Copy
Range("IV1").End(xlToLeft).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
ActiveCell.FormulaR1C1 = "Booked Month"
ActiveCell.EntireColumn.AutoFit


' Populate the Month Column with new Monts
Range("IV1").End(xlToLeft).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = _

"=CHOOSE(MONTH(RC[-25]),""Jan"",""Feb"",""Mar"",""Apr"",""May"",""Jun"",""Jul"",""Aug"",""Sep"",""Oct"",""Nov"",""Dec"")"
Selection.AutoFill Destination:=Range("AL2:AL" & LastRow),
Type:=xlFillDefault
Calculate

End Sub

The part that is giving me headaches is the following:
Selection.AutoFill Destination:=Range("AL2:AL" & LastRow),
Type:=xlFillDefault

It works as it is, but I don't want to have the absolute references in there
because I'm not sure that the raw data will always have the same number of
columns.
Is there a way that I can set the Destination range in this particular case
without having the absolute references there?

thanks
Juan Correa
Click to expand...
Click to expand...
Click to expand...
 
J

Juan Correa

Dave,

Thanks again.


You have saved the day my friend.

Juan Correa


Dave Peterson said:
You can search for it in row 1 (right?).

This seemed to work ok:

Option Explicit
Sub MonthAndPivot()

Dim DataWks As Worksheet
Dim LastRow As Long
Dim LastCol As Long
Dim ExpBookMonthCell As Range
Dim StrToFind As String

StrToFind = "Expected Book Month"

Set DataWks = Worksheets("Data")

With DataWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
With .Rows(1) 'is that where the are???
Set ExpBookMonthCell = .Rows(1).Find(what:=StrToFind, _
after:=.Cells(.Cells.Count), _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If ExpBookMonthCell Is Nothing Then
MsgBox StrToFind & " wasn't found--stopping"
Exit Sub
End If

.Columns(LastCol).Copy
.Columns(LastCol + 1).PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

.Cells(1, LastCol + 1).Value = "Booked Month"
.Columns(LastCol + 1).AutoFit

'fixed a typo--I missed a dot in front of the second .cells()
.Range(.Cells(2, LastCol + 1), .Cells(LastRow, LastCol + 1)).Formula _
= "=text(" & .Cells(2, ExpBookMonthCell.Column).Address(0, 0) _
& ",""mmm"")"
End With

Application.Calculate

End Sub

Juan said:
Thank you very much Dave. This worked like a charm. And your code is way
cleaner!

I do have a follow up question.

Looking at the code you provided, I see this bit:
.Range(.Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1)).Formula =
"=text(w2,""mmm"")"

This formula is looking for the value of W2 ,converting it to Text in mmm
format and then copying that formula all the way down to the last row of
existing data on the worksheet. That is exactly what I need. My problem
comes because I'm not sure that the data to be looking at will always be in
column W.

I know that the information currently on column W will always be included
with the raw data and I know that it will alwasy be labeled the same way (Is
that the correct terminology?).
This column will always be labeled "Expected Book Month".
Is there a way that I can reference that column based on its label instead
of using the absolute reference? That way I can be sure that no matter where
the data is, the formula will always look for it in the right place.

Thanks again

Juan Correa

Dave Peterson said:
You can drop the .select's and .activate's and your code may be easier to
understand and update:

Option Explicit
Sub MonthAndPivot()

Dim DataWks As Worksheet
Dim LastRow As Long
Dim LastCol As Long

Set DataWks = Worksheets("Data")

With DataWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

.Columns(LastCol).Copy
.Columns(LastCol + 1).PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

.Cells(1, LastCol + 1).Value = "Booked Month"
.Columns(LastCol + 1).AutoFit

.Range(.Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1)).Formula _
= "=text(w2,""mmm"")"
End With

Application.Calculate

End Sub




Juan Correa wrote:

Hello...

I'm working on a small macro for my boss.

Here is what I have so far:

Sub MonthAndPivot()
' Activate the Data Sheet before anything else
Sheets("Data").Activate

' Declarations
Dim LastRow As Long, LastCol As Long

LastRow = Range("A65536").End(xlUp).Row
LastCol = Range("IV1").End(xlToLeft).Column



' Create the "Booked Month" Column
Range("IV1").End(xlToLeft).Select
Selection.Copy
Range("IV1").End(xlToLeft).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
ActiveCell.FormulaR1C1 = "Booked Month"
ActiveCell.EntireColumn.AutoFit


' Populate the Month Column with new Monts
Range("IV1").End(xlToLeft).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = _

"=CHOOSE(MONTH(RC[-25]),""Jan"",""Feb"",""Mar"",""Apr"",""May"",""Jun"",""Jul"",""Aug"",""Sep"",""Oct"",""Nov"",""Dec"")"
Selection.AutoFill Destination:=Range("AL2:AL" & LastRow),
Type:=xlFillDefault
Calculate

End Sub

The part that is giving me headaches is the following:
Selection.AutoFill Destination:=Range("AL2:AL" & LastRow),
Type:=xlFillDefault

It works as it is, but I don't want to have the absolute references in there
because I'm not sure that the raw data will always have the same number of
columns.
Is there a way that I can set the Destination range in this particular case
without having the absolute references there?

thanks
Juan Correa
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

That's nice to hear.

Juan said:
Dave,

Thanks again.

You have saved the day my friend.

Juan Correa

Dave Peterson said:
You can search for it in row 1 (right?).

This seemed to work ok:

Option Explicit
Sub MonthAndPivot()

Dim DataWks As Worksheet
Dim LastRow As Long
Dim LastCol As Long
Dim ExpBookMonthCell As Range
Dim StrToFind As String

StrToFind = "Expected Book Month"

Set DataWks = Worksheets("Data")

With DataWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
With .Rows(1) 'is that where the are???
Set ExpBookMonthCell = .Rows(1).Find(what:=StrToFind, _
after:=.Cells(.Cells.Count), _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If ExpBookMonthCell Is Nothing Then
MsgBox StrToFind & " wasn't found--stopping"
Exit Sub
End If

.Columns(LastCol).Copy
.Columns(LastCol + 1).PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

.Cells(1, LastCol + 1).Value = "Booked Month"
.Columns(LastCol + 1).AutoFit

'fixed a typo--I missed a dot in front of the second .cells()
.Range(.Cells(2, LastCol + 1), .Cells(LastRow, LastCol + 1)).Formula _
= "=text(" & .Cells(2, ExpBookMonthCell.Column).Address(0, 0) _
& ",""mmm"")"
End With

Application.Calculate

End Sub

Juan said:
Thank you very much Dave. This worked like a charm. And your code is way
cleaner!

I do have a follow up question.

Looking at the code you provided, I see this bit:
.Range(.Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1)).Formula =
"=text(w2,""mmm"")"

This formula is looking for the value of W2 ,converting it to Text in mmm
format and then copying that formula all the way down to the last row of
existing data on the worksheet. That is exactly what I need. My problem
comes because I'm not sure that the data to be looking at will always be in
column W.

I know that the information currently on column W will always be included
with the raw data and I know that it will alwasy be labeled the same way (Is
that the correct terminology?).
This column will always be labeled "Expected Book Month".
Is there a way that I can reference that column based on its label instead
of using the absolute reference? That way I can be sure that no matter where
the data is, the formula will always look for it in the right place.

Thanks again

Juan Correa

:

You can drop the .select's and .activate's and your code may be easier to
understand and update:

Option Explicit
Sub MonthAndPivot()

Dim DataWks As Worksheet
Dim LastRow As Long
Dim LastCol As Long

Set DataWks = Worksheets("Data")

With DataWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

.Columns(LastCol).Copy
.Columns(LastCol + 1).PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

.Cells(1, LastCol + 1).Value = "Booked Month"
.Columns(LastCol + 1).AutoFit

.Range(.Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1)).Formula _
= "=text(w2,""mmm"")"
End With

Application.Calculate

End Sub




Juan Correa wrote:

Hello...

I'm working on a small macro for my boss.

Here is what I have so far:

Sub MonthAndPivot()
' Activate the Data Sheet before anything else
Sheets("Data").Activate

' Declarations
Dim LastRow As Long, LastCol As Long

LastRow = Range("A65536").End(xlUp).Row
LastCol = Range("IV1").End(xlToLeft).Column



' Create the "Booked Month" Column
Range("IV1").End(xlToLeft).Select
Selection.Copy
Range("IV1").End(xlToLeft).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
ActiveCell.FormulaR1C1 = "Booked Month"
ActiveCell.EntireColumn.AutoFit


' Populate the Month Column with new Monts
Range("IV1").End(xlToLeft).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = _

"=CHOOSE(MONTH(RC[-25]),""Jan"",""Feb"",""Mar"",""Apr"",""May"",""Jun"",""Jul"",""Aug"",""Sep"",""Oct"",""Nov"",""Dec"")"
Selection.AutoFill Destination:=Range("AL2:AL" & LastRow),
Type:=xlFillDefault
Calculate

End Sub

The part that is giving me headaches is the following:
Selection.AutoFill Destination:=Range("AL2:AL" & LastRow),
Type:=xlFillDefault

It works as it is, but I don't want to have the absolute references in there
because I'm not sure that the raw data will always have the same number of
columns.
Is there a way that I can set the Destination range in this particular case
without having the absolute references there?

thanks
Juan Correa
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot Table Code - Is there a better way? 2
Check if data has already been formatted. 2
Runtime Error 1004 when runing Sub() from btnClick 2
Complie Syntax Error 6
Why ? What is error 424 ? 2
Macro should Save and Overwrite file 1
Selection.AutoFill Destination:=Range 0
fill range with formula 2

Top