Buscando un método más condensado para asignar valores utilizando matrices y rangos fijos -- array campo con vba campo con excel camp codereview Relacionados El problema

Looking for a more condensed method of assigning values by using arrays and fixed ranges


1
vote

problema

Español

Actualmente tengo un libro de trabajo de plantilla que tiene tablas y gráficos que se actualizan cuando los datos nuevos se conectan a las tablas de datos.
Las tablas de datos son rangos fijos, y no cambiarán.

La macro está dentro del libro de trabajo de la plantilla, por lo que el siguiente código no establece variables para cada una de las hojas de trabajo dentro del libro de plantillas, y 99887776655544333 se utilizan para cada nombre de la hoja de trabajo.

Sé que el código a continuación es un código de matriz muy básico, y funciona. Sin embargo, ¿hay una manera más condensada de realizar esta macro de matriz? He investigado, pero parece que no puedo encontrar un ejemplo de múltiples rangos y asignar valores del 9988776665544334 al Template Workbook - Multiple Worksheets .

  Sub Main()  'Turn off screen updates and automatic calculations     Application.ScreenUpdating = False     Application.Calculation = xlManual     Application.DisplayAlerts = False      Dim wbDataSource As Workbook     Dim ws As Worksheet      Set wbDataSource = Workbooks("Segment Trends Data.xlsx")          For Each ws In wbDataSource.Worksheets         With ws             If .Index <> 1 Then                 Dim Data(13) As Variant                 Data(0) = Worksheets("Report1").Range("A1").Value2                 Data(1) = Worksheets("Report1").Range("A5:H11").Value2                 Data(2) = Worksheets("Report2").Range("A1").Value2                 Data(3) = Worksheets("Report2").Range("A5:H11").Value2                 Data(4) = Worksheets("Report3").Range("A1").Value2                 Data(5) = Worksheets("Report3").Range("A5:H11").Value2                 Data(6) = Worksheets("Report4").Range("A1").Value2                 Data(7) = Worksheets("Report4").Range("A5:H11").Value2                 Data(8) = Worksheets("Report5").Range("A1").Value2                 Data(9) = Worksheets("Report5").Range("A5:H11").Value2                 Data(10) = Worksheets("Report6").Range("A1").Value2                 Data(11) = Worksheets("Report6").Range("A5:H11").Value2                 Data(12) = Worksheets("Report7").Range("A1").Value2                 Data(13) = Worksheets("Report7").Range("A5:H11").Value2             End If         End With     Next ws          wbDataSource.Close SaveChanges:=False      With wsTTLUSCYTD         .Range("A1").Value2 = Data(0)         .Range("A5:H11").Value2 = Data(1)     End With          With wsCintiCYTD         .Range("A1").Value2 = Data(2)         .Range("A5:H11").Value2 = Data(3)     End With          With wsCOLCYTD         .Range("A1").Value2 = Data(4)         .Range("A5:H11").Value2 = Data(5)     End With          With wsDaytonCYTD         .Range("A1").Value2 = Data(6)         .Range("A5:H11").Value2 = Data(7)     End With          With wsIndyCYTD         .Range("A1").Value2 = Data(8)         .Range("A5:H11").Value2 = Data(9)     End With          With wsLouisCYTD         .Range("A1").Value2 = Data(10)         .Range("A5:H11").Value2 = Data(11)     End With          With wsCoreMktCYTD         .Range("A1").Value2 = Data(12)         .Range("A5:H11").Value2 = Data(13)     End With          Calculate          Dim TemplatePath As String         TemplatePath = "C:UserscdayOneDrive - udfinc.comBudgeting Presentation_Working Files"         ActiveWorkbook.SaveAs Filename:=TemplatePath & "Segment Trends - CYTD - Budget Template" & ".xlsm", FileFormat:=52  'Turn screen updates and automatic calculations back on     Application.ScreenUpdating = True     Application.Calculation = xlAutomatic     Application.DisplayAlerts = True  End Sub   

Uso de DEBUG.PRINT, los datos se retiran de las hojas de trabajo wbDataSource dentro del bucle. debug.print

Original en ingles

I currently have a Template Workbook set up that has charts and graphs updating when new data is plugged into the Data Tables.
The Data Tables are fixed ranges, and won't change.

The Macro is within the Template Workbook, which is why the below code doesn't set variables for each of the worksheets within the Template Workbook, and Code Names are used for each Worksheet name.

I know the below code is a very basic array code, and works. However, Is there a more condensed way to perform this array macro? I have researched, but can't seem to find an example of multiple ranges and assigning values from the Source Workbook - Multiple Worksheets to the Template Workbook - Multiple Worksheets.

Sub Main()  'Turn off screen updates and automatic calculations     Application.ScreenUpdating = False     Application.Calculation = xlManual     Application.DisplayAlerts = False      Dim wbDataSource As Workbook     Dim ws As Worksheet      Set wbDataSource = Workbooks("Segment Trends Data.xlsx")          For Each ws In wbDataSource.Worksheets         With ws             If .Index <> 1 Then                 Dim Data(13) As Variant                 Data(0) = Worksheets("Report1").Range("A1").Value2                 Data(1) = Worksheets("Report1").Range("A5:H11").Value2                 Data(2) = Worksheets("Report2").Range("A1").Value2                 Data(3) = Worksheets("Report2").Range("A5:H11").Value2                 Data(4) = Worksheets("Report3").Range("A1").Value2                 Data(5) = Worksheets("Report3").Range("A5:H11").Value2                 Data(6) = Worksheets("Report4").Range("A1").Value2                 Data(7) = Worksheets("Report4").Range("A5:H11").Value2                 Data(8) = Worksheets("Report5").Range("A1").Value2                 Data(9) = Worksheets("Report5").Range("A5:H11").Value2                 Data(10) = Worksheets("Report6").Range("A1").Value2                 Data(11) = Worksheets("Report6").Range("A5:H11").Value2                 Data(12) = Worksheets("Report7").Range("A1").Value2                 Data(13) = Worksheets("Report7").Range("A5:H11").Value2             End If         End With     Next ws          wbDataSource.Close SaveChanges:=False      With wsTTLUSCYTD         .Range("A1").Value2 = Data(0)         .Range("A5:H11").Value2 = Data(1)     End With          With wsCintiCYTD         .Range("A1").Value2 = Data(2)         .Range("A5:H11").Value2 = Data(3)     End With          With wsCOLCYTD         .Range("A1").Value2 = Data(4)         .Range("A5:H11").Value2 = Data(5)     End With          With wsDaytonCYTD         .Range("A1").Value2 = Data(6)         .Range("A5:H11").Value2 = Data(7)     End With          With wsIndyCYTD         .Range("A1").Value2 = Data(8)         .Range("A5:H11").Value2 = Data(9)     End With          With wsLouisCYTD         .Range("A1").Value2 = Data(10)         .Range("A5:H11").Value2 = Data(11)     End With          With wsCoreMktCYTD         .Range("A1").Value2 = Data(12)         .Range("A5:H11").Value2 = Data(13)     End With          Calculate          Dim TemplatePath As String         TemplatePath = "C:\Users\cday\OneDrive - udfinc.com\Budgeting Presentation_Working Files\"         ActiveWorkbook.SaveAs Filename:=TemplatePath & "Segment Trends - CYTD - Budget Template" & ".xlsm", FileFormat:=52  'Turn screen updates and automatic calculations back on     Application.ScreenUpdating = True     Application.Calculation = xlAutomatic     Application.DisplayAlerts = True  End Sub 

Using Debug.Print, the data is pulling from the wbDataSource worksheets within the Loop. Debug.Print

        
         
         

Lista de respuestas

2
 
vote

La discusión de comentarios en la publicación original se relaciona directamente con las diferencias entre el ActiveWorkbook y ThisWorkbook1 . Si bien usted código puede trabajar, no es una garantía. esta referencia (y este ) tiene buenas explicaciones para ilustrar las diferencias entre cómo referencias tiene un libro de trabajo y por qué siempre debe prestar atención específica a las referencias (Ya sea para el Workbook o Worksheet o 9988776655544334 ).

Como se explica en los comentarios, debido a que identifica específicamente la hoja de trabajo para todos los datos 9988776665544335 , no necesita hacer una vuelta a través de todas las hojas de trabajo. Esto reducirá la configuración de su matriz a

  Dim wbDataSource As Workbook Set wbDataSource = Workbooks("Segment Trends Data.xlsx")  Dim Data(13) As Variant With wbDataSource     Data(0) = .Worksheets("Report1").Range("A1").Value2     Data(1) = .Worksheets("Report1").Range("A5:H11").Value2     Data(2) = .Worksheets("Report2").Range("A1").Value2     Data(3) = .Worksheets("Report2").Range("A5:H11").Value2     Data(4) = .Worksheets("Report3").Range("A1").Value2     Data(5) = .Worksheets("Report3").Range("A5:H11").Value2     Data(6) = .Worksheets("Report4").Range("A1").Value2     Data(7) = .Worksheets("Report4").Range("A5:H11").Value2     Data(8) = .Worksheets("Report5").Range("A1").Value2     Data(9) = .Worksheets("Report5").Range("A5:H11").Value2     Data(10) = .Worksheets("Report6").Range("A1").Value2     Data(11) = .Worksheets("Report6").Range("A5:H11").Value2     Data(12) = .Worksheets("Report7").Range("A1").Value2     Data(13) = .Worksheets("Report7").Range("A5:H11").Value2 End With   

Aviso que he cambiado la cláusula 9988776655544337 para hacer referencia al libro wbDataSource Workbook, por lo que es muy claro de dónde viene todos sus datos.

El código que ha escrito en realidad no necesita una matriz, de hecho, la matriz oscurece lo que el código realmente está tratando de hacer. ¿Qué es exactamente Data(0) o ThisWorkbook0 ? Puede que lo encuentres tedioso, pero definiría más claramente qué información se mantienen todas esas variables. Como ejemplo

  ThisWorkbook1  

Ahora, cuando regrese para hacer una actualización a su código en seis meses, no tiene que recordar lo que realmente significa cada una de esas ranuras de matriz.

Finalmente, volviendo a la pregunta original de su publicación, no hay una manera mágica o fácil de realizar las tareas a múltiples hojas de trabajo de destino. Ya ha definido los nombres de código para cada una de las hojas, por lo que la VBA que tiene es tan simple como puede hacerlo. Todavía recomiendo el cambio a los nombres de variables más descriptivos para que se borra con qué datos está copiando a las hojas de trabajo de destino.

  ThisWorkbook2  
 

The comment discussion in the original post directly relate to the differences between the ActiveWorkbook and ThisWorkbook. While you code may indeed work, it's not a guarantee. This reference (and this one) have good explanations to illustrate the differences between how you reference a workbook and why you should always pay specific attention to references (whether it's to the Workbook or Worksheet or Range).

As explained in the comments, because you specifically identify the worksheet for all the .Value2 data, you don't need to loop through all the worksheets. This will reduce the setup of your array to

Dim wbDataSource As Workbook Set wbDataSource = Workbooks("Segment Trends Data.xlsx")  Dim Data(13) As Variant With wbDataSource     Data(0) = .Worksheets("Report1").Range("A1").Value2     Data(1) = .Worksheets("Report1").Range("A5:H11").Value2     Data(2) = .Worksheets("Report2").Range("A1").Value2     Data(3) = .Worksheets("Report2").Range("A5:H11").Value2     Data(4) = .Worksheets("Report3").Range("A1").Value2     Data(5) = .Worksheets("Report3").Range("A5:H11").Value2     Data(6) = .Worksheets("Report4").Range("A1").Value2     Data(7) = .Worksheets("Report4").Range("A5:H11").Value2     Data(8) = .Worksheets("Report5").Range("A1").Value2     Data(9) = .Worksheets("Report5").Range("A5:H11").Value2     Data(10) = .Worksheets("Report6").Range("A1").Value2     Data(11) = .Worksheets("Report6").Range("A5:H11").Value2     Data(12) = .Worksheets("Report7").Range("A1").Value2     Data(13) = .Worksheets("Report7").Range("A5:H11").Value2 End With 

Notice that I've changed the With clause to reference the wbDataSource workbook so that's it's very clear where all your data is coming from.

The code you've written actually doesn't need an array, in fact the array obscures what the code is really trying to do. What exactly is Data(0) or Data(11)? You might find it tedious, but I would more clearly define what information all those variables hold. As an example

Dim reportTitle As String Dim reportDescription As String Dim regionNumber As Long Dim regionManager As String  With wbDataSource     reportTitle = .Worksheets("Report1").Range("A1").Value2     reportDescription = .Worksheets("Report1").Range("A5:H11").Value2     regionNumber = .Worksheets("Report2").Range("A1").Value2     regionManager = .Worksheets("Report2").Range("A5:H11").Value2     ... End With 

Now when you come back to make an update to your code in six months, you don't have to remember what each of those array slots actually means.

Finally, getting back to the original question of your post, there's no magic or easy way to make the assignments to multiple destination worksheets. You've already defined the code names for each of the sheets, so the VBA you have is about as simple as you can make it. I still recommend the change to more descriptive variable names to make it clear which data you are copying to the destination worksheets.

With wsTTLUSCYTD     .Range("A1").Value2 = reportTitle     .Range("A5:H11").Value2 = reportDescription End With  With wsCintiCYTD     .Range("A1").Value2 = regionNumber     .Range("A5:H11").Value2 = regionManager End With 
 
 

Relacionados problema

2  Búsqueda de pruebas de abstracción y unidad en mesa Excel  ( Abstracting and unit testing lookups in excel table ) 
Fondo Tengo una solución de VBA que utilizo para ingerir informes de texto de inversión y reformatearlos para su análisis en Excel. Funciona, pero las macro...

5  Excel a JSON Parser con http descargar  ( Excel to json parser with http download ) 
He estado trabajando en un proyecto ( enlace ) a Descargue una hoja de cálculo de Ransomware y propiedades conocidas y conviértase en JSON para que pueda cons...

3  Comparando células de dos hojas de trabajo  ( Comparing cells of two worksheets ) 
Con el siguiente código, trato de comparar el contenido de dos hojas de trabajo. La primera columna contiene una clave que es única y las siguientes columnas ...

6  Fijación de archivos de reclamación médica a través del archivo de texto Lea / escribe  ( Fixing medical claim files through text file read write ) 
Con Gracias a @ Mat'Smug y @comintern por su aliento, aquí hay un programa que escribí para ayudar a mi equipo a reparar archivos de reclamación médica. El ...

3  Coloque el cálculo del costo del campamento de verano utilizando las hojas de Google  ( Summer camp cost calculation using google sheets ) 
Estoy haciendo una hoja que suma el costo total para un campamento de verano después del registro. Cada padre puede registrarse hasta cinco niños. Ya he hecho...

5  Copiando datos de libros cerrados  ( Copying data from closed workbooks ) 
Soy un pasante en una empresa industrial en Brasil y sucede que estoy usando mucho en Excel. Acabo de empezar a jugar con VBA hace un par de días, ¡y me divie...

6  Crear tabla que enumera la visibilidad de la hoja de trabajo  ( Create table that lists worksheet visibility ) 
Siguiendo mi pregunta anterior crear Una tabla que enumera las macros en un libro de trabajo o hoja de trabajo Aquí está mi Sub para determinar la visibilid...

6  Juego de serpientes - Dentro de la hoja de trabajo - Celdas como píxeles  ( Snake game within worksheet cells as pixels ) 
Desde mi intento bastante mediocre de hacer un juego de invasores del espacio, me topé con un caché de juegos de Visual Basic para solicitar juegos escritos p...

7  Recuperando datos de archivos en la carpeta  ( Retrieving data from files in the folder ) 
Estoy a usar el código VBA para simplemente recuperar ciertos datos de todos los archivos de Excel en cierta carpeta y pegarlo en la hoja de cálculo de trabaj...

2  Importando datos en Excel  ( Importing data into excel ) 
¿Existe una forma más fácil de importar datos en una matriz de Excel u otra estructura de datos? He intentado investigar colecciones, pero he encontrado la D...




© 2022 respuesta.top Reservados todos los derechos. Centro de preguntas y respuestas reservados todos los derechos