Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Imported Visual Basic Module - Save Macros for use in Excel | Excel Discussion (Misc queries) | |||
Visual Basic Macros | Excel Discussion (Misc queries) | |||
Visual Basic Macros, relative position | Charts and Charting in Excel | |||
how to write visual basic macros | Excel Programming | |||
creating an exe file with visual basic (installation file for Macros and Userforms) | Excel Programming |