Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO QUESTION
Hello
I'm doing a macro that combines data from many files to one worksheet master file. The problem is in the worksheet master file, when there are data updated in one of those files it can't appear in the master worksheet becos when recording of macro (copy & paste ) the range already fixed. How can I solve this problem. Any help appreciated. TQ Nickie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO QUESTION
when you copy your data, instead of using
ActiveSheet.Range("A1:Z20") use Activesheet.Range("A1").CurrentRegion or Activesheet.UsedRange Currentregion assumes all you data is contiguous. You can test it by selecting A1 and doing Ctrl+Shift+8 and see if it picks up all your data. If it higlights all your data, then you can use currentregion. A final alternative is to use With ActiveSheet set rng = .Range(.cells(1,1),.Cells(rows.count,1).End(xlup)) .Resize(,10) End With rng.Select change the 10 to indicate the number of columns. This is dependent on there being values in Column A, but you could change it to check another column (although you would need to possibly add an offset to pick up all the columns you want. -- Regards, Tom Ogilvy "dkcipoh" wrote in message ... Hello I'm doing a macro that combines data from many files to one worksheet master file. The problem is in the worksheet master file, when there are data updated in one of those files it can't appear in the master worksheet becos when recording of macro (copy & paste ) the range already fixed. How can I solve this problem. Any help appreciated. TQ Nickie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO QUESTION
Thanks it works...
"Tom Ogilvy" wrote in message ... when you copy your data, instead of using ActiveSheet.Range("A1:Z20") use Activesheet.Range("A1").CurrentRegion or Activesheet.UsedRange Currentregion assumes all you data is contiguous. You can test it by selecting A1 and doing Ctrl+Shift+8 and see if it picks up all your data. If it higlights all your data, then you can use currentregion. A final alternative is to use With ActiveSheet set rng = .Range(.cells(1,1),.Cells(rows.count,1).End(xlup)) .Resize(,10) End With rng.Select change the 10 to indicate the number of columns. This is dependent on there being values in Column A, but you could change it to check another column (although you would need to possibly add an offset to pick up all the columns you want. -- Regards, Tom Ogilvy "dkcipoh" wrote in message ... Hello I'm doing a macro that combines data from many files to one worksheet master file. The problem is in the worksheet master file, when there are data updated in one of those files it can't appear in the master worksheet becos when recording of macro (copy & paste ) the range already fixed. How can I solve this problem. Any help appreciated. TQ Nickie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Macro/VB Question DDE Question | Excel Worksheet Functions | |||
macro question | Excel Discussion (Misc queries) | |||
Macro Question | Excel Worksheet Functions | |||
question on macro | Excel Discussion (Misc queries) | |||
Macro Question? | Excel Discussion (Misc queries) |