Visual Basic - Macros
 

Hi,

I have two questions regarding macros and how to adapt the VB code so that
the macro can work on any file you may use later.

When recording a macro, if you select a column onto the last record, it only
really works on the current file that you are working on. How can you in VB
change the code so that it adapts to any file knowing that the number of
records will vary?

Also I currently need to copy and paste values of a pivot table onto a blank
sheet. Then I would filter each column on the blanks and add a formula that
the blank cell should equal the cell right above.
Is there a way to do this with a macro? Knowing that the number of records
will vary and that the blank cells will be located in different spots from
one file to another? This would save me some precious time.

Thanks a lot,
Kanga

Visual Basic - Macros
 

It all revolves around the last record.

With ACtiveworkbook
Set rng = .Range("A1").Resize(Cells(Rows.Count,"A"),End(xlUp ).Row)
End With

will create a range object of the correct amount of data in column A, which
you can then work on in your code.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kanga" wrote in message
...
Hi,

I have two questions regarding macros and how to adapt the VB code so that
the macro can work on any file you may use later.

When recording a macro, if you select a column onto the last record, it
only
really works on the current file that you are working on. How can you in
VB
change the code so that it adapts to any file knowing that the number of
records will vary?

Also I currently need to copy and paste values of a pivot table onto a
blank
sheet. Then I would filter each column on the blanks and add a formula
that
the blank cell should equal the cell right above.
Is there a way to do this with a macro? Knowing that the number of records
will vary and that the blank cells will be located in different spots from
one file to another? This would save me some precious time.

Thanks a lot,
Kanga

Visual Basic - Macros
 

Excellent! Thanks a lot!

"Bob Phillips" wrote:

It all revolves around the last record.

With ACtiveworkbook
Set rng = .Range("A1").Resize(Cells(Rows.Count,"A"),End(xlUp ).Row)
End With

will create a range object of the correct amount of data in column A, which
you can then work on in your code.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kanga" wrote in message
...
Hi,

I have two questions regarding macros and how to adapt the VB code so that
the macro can work on any file you may use later.

When recording a macro, if you select a column onto the last record, it
only
really works on the current file that you are working on. How can you in
VB
change the code so that it adapts to any file knowing that the number of
records will vary?

Also I currently need to copy and paste values of a pivot table onto a
blank
sheet. Then I would filter each column on the blanks and add a formula
that
the blank cell should equal the cell right above.
Is there a way to do this with a macro? Knowing that the number of records
will vary and that the blank cells will be located in different spots from
one file to another? This would save me some precious time.

Thanks a lot,
Kanga