ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Defining destination Range (https://www.excelbanter.com/excel-programming/429785-defining-destination-range.html)

Juan Correa
Defining destination Range
 
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

Dave Peterson
Defining destination Range
 
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

--

Dave Peterson

Juan Correa
Defining destination Range
 
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" wrote:

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

--

Dave Peterson

Dave Peterson
Defining destination Range
 
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 Correa wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson

Juan Correa
Defining destination Range
 
Dave,

Thanks again.


You have saved the day my friend.

Juan Correa


"Dave Peterson" wrote:

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 Correa wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson

Dave Peterson
Defining destination Range
 
That's nice to hear.

Juan Correa wrote:

Dave,

Thanks again.

You have saved the day my friend.

Juan Correa

"Dave Peterson" wrote:

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 Correa wrote:

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" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 12:42 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com