MACRO QUESTION

[dkcipoh]

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

 

[Tom Ogilvy]

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.

 

[dkcipoh]

Thanks it works...

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.