How to perform a dynamic or named range in another Excel workbook?

I'm making a macro that takes a range of cells from a workbook in Excel and copies it to a different workbook.

I would like to know how to perform a range of cells from another sheet in Excel to make a copy of that range on a new sheet.

Try naming the range and it didn't work, as the source file you want to copy from won't always have the same range.

Here is the Code: 

Sub Obtener_Datos()

    Application.ScreenUpdating = False

    With Sheets("Hoja1")
        'verificamos si hay datos en la hoja
        'donde se almacenaran los resultados
        datos = Application.CountA(.Range("A2:A9"))

        'insertamos una linea para poder limpiar el rango
        .Range("A1:D1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

        'se define el rango de celdas y cuales columnas se desean copiar
        With .Range("TAMANO")'Es aquí en donde tengo la duda!! <-------------------
            'si hay datos en la hoja donde se almacenaran los resultados
            'los borramos para actualizarla
            If datos > 0 Then .ClearContents

            'definimos de que libro extraeremos los datos, aca va la ruta en donde se encuentra el archivo
            ruta_libro = "D:\documentos laborales\Documentos de costos"
            nombre_libro = "Consolidado final.xlsx"
            nombre_hoja = "Procesos validos"

            'insertamos las formulas
            .Formula = "='" & ruta_libro & "\[" & _
            nombre_libro & "]" & nombre_hoja & "'!a1"

            'dejamos solo los valores
            .Value = .Value
            .HorizontalAlignment = xlCenter
        End With

        'recorremos el rango para eliminar ceros
        For b = 1 To 4
            With .Columns(b)
                .AutoFilter Field:=1, Criteria1:="=0", Operator:=xlOr, Criteria2:="=00"
                .SpecialCells(xlCellTypeVisible).ClearContents
                .AutoFit
            End With
        Next

        'eliminamos la linea que habiamos insertado
        .Range("A1:D1").Delete Shift:=xlUp
    End With

    Application.ScreenUpdating = True
End Sub
 0
Author: Mariano, 2016-09-13
Source

3 answers

At some point I pass something similar, solve it in 2 ways: the first by performing a counter that looked down until there were two blanks. with that counter I knew the range I had to take to copy the data.

The second way, was to record by hand, like when you select the first cell of a range and press ctrl + shift +down and select it full.

I wish I could help you.

 2
Author: Felipe González, 2016-09-13 23:08:22

It seems that you ask how it is possible to set dynamic ranges in a macro in Excel, I regularly do it in 2 ways: 

activesheet.usedrange

This function is useful when you occupy all the data within a table that can change in x or y. 

range(range("A1"),range("A1").End(xlDown))

This function takes the range from an initial cell ("A1") to a final cell ("AX") where X is the row where a non-blank cell exists.

 2
Author: Maikflow, 2016-10-22 04:57:20

You define one variable for the range and another for calculating the last line with data: 

Dim MiRango as Range
Dim ufila as Long

Then you look for the last row with data from your range, which may vary clearly, but for that you use this: 

ufila = Thisworkbook.Sheets("MiHoja").Cells(Rows.Count, 1).End(xlUp).Row

Now you initialize the range, let's assume it spans from Column A to H: 

Set MiRango = Thisworkbook.Sheets("MiHoja").Range("A1:H" & ufila)

Then you can copy the range: 

MiRango.Copy Libro_de_destino
 0
Author: dogwall, 2019-10-21 08:47:43