Copy data from another workbook and define range

[cogumelo]

Hi,

I have this code, which works great if the data is always the same size, however on this particular case the range i'm copying can change (everyday will have more lines - columns remain the same) and therefore this will affect:

original range is dynamic:

Windows("data1.xlsx").Activate
Range("A1:C4").Select
Selection.Copy

cell where the 2nd range will be copied to will have start on a different variable cell:

Windows("master.xlsm").Activate
Range("F14").Select
ActiveSheet.Paste

and finally the ending range will also be dynamic.

Range("F10:H17").Select
ActiveWorkbook.Names.Add Name:="grupo", RefersToR1C1:="=Folha1!R10C6:R17C8"
ActiveWorkbook.Names("grupo").Comment = ""


Is it possible to change the code so that it will adapt to these dynamic ranges?

Any help would be appreciated. Thx in advance.

Best regards



Full code:

Sub Macro1()
'
' Macro1 Macro
'

'
Windows("data1.xlsx").Activate
Range("A1:C4").Select
Selection.Copy

Windows("master.xlsm").Activate
Range("F10").Select
ActiveSheet.Paste

Windows("data2.xlsx").Activate
Range("A2:C5").Select
Selection.Copy

Windows("master.xlsm").Activate
Range("F14").Select
ActiveSheet.Paste

Range("F10:H17").Select
ActiveWorkbook.Names.Add Name:="grupo", RefersToR1C1:="=Folha1!R10C6:R17C8"
ActiveWorkbook.Names("grupo").Comment = ""
End Sub

 

[JLGWhiz]

Including the additional rows is easy. The following finds the last cell with data in the used range for a sheet and puts it in a variable. That variable is then used to define the range, which can be one column or many columns. I use 'lr' for the variable to represent 'last row' but you can use anything that makes sense to you.

Dim Dim sh As Worksheet, lr As Long, rng As Range 'use long integer to avoid stack overflow.
Set sh = Sheets(1) 'put the worksheet in an Object Variable
lr = sh.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
Set rng = sh.Range("A2:A" & lr) 'Put the dynamic range in an Object variable

With the above code at the beginning of a macro, The entire range of data in column a would be included in the rng variable no matter how many rows were added or deleted between runs. But be careful here. If you include the adding or deleting of rows in the same macro, then your rng variable does not automatically change until the next time you run the macro, so if you wanted to use a variable for the range after rows are added or deleted during the run, then it would be necessary to re-initialize the lr and the rng variables.
The part about using a different starting point would need to have a defined criteria and be controlled with an If...Then statement or a Select Case statement based on that criteria. i.e. If a = b then start row = 5, etc. In otherwords, what causes the range to change.

 

[cogumelo]

Hi,

JLGWhiz -> Thank you very much for your quick reply. Your code was great!

Now i'm stuck on 2 issues:

Issue 1

After pasting the data, the surrounding columns need to be pulled down up to the pasted range line.

So i need to pull column D down to line 5 and 6 (which are the new lines i just added)

A B C D
1 Name1 Address1 Country1 formula1
2 Name1 Address1 Country1 formula1
3 Name1 Address1 Country1 formula1
4 Name1 Address1 Country1 formula1
5 Name1 Address1 Country1
6 Name1 Address1 Country1

Issue 2

And after pulling it down i need to update the name range with those new lines

Are these 2 things possible to do?

Thx in advance