Update values in a transposed range when source is modified in a vba-generated workbook

[Pteroglossus]

I created a VBA script that automatically imports and formats daily-generated csv files then copies formulas and displays results. (Same project as my previous question here).

The last step of this macro is the transposition of the dynamically-located data at the bottom of the table ("J" & lastRow + 1 & ":AA" & lastRow + 4) to a fixed range "AG1:AJ18" in order to sum all the data up in monthly dashboard files.

I tried two ways:

Range("AG1:AJ18").Value = WorksheetFunction.Transpose(Range("J" & lastRow + 1 & ":AA" & lastRow + 4))

And

Range("J" & lastRow + 1 & ":AA" & lastRow + 4).Copy
Range("AG1:AJ18").PasteSpecial Paste:=xlPasteValues, Transpose:=True

Which both work once when the file are generated, but when I manually modifiy the data within the table later, the tranposed data is not updated.

I read about "worksheet.change events", but they would need to be included in the code of each generated worksheet...which sounds complicated.

Isn't there an easier way to achieve this?

 

[HighAndWilder]

I created a VBA script that automatically imports and formats daily-generated csv files then copies formulas and displays results. (Same project as my previous question here).

The last step of this macro is the transposition of the dynamically-located data at the bottom of the table ("J" & lastRow + 1 & ":AA" & lastRow + 4) to a fixed range "AG1:AJ18" in order to sum all the data up in monthly dashboard files.

I tried two ways:

Range("AG1:AJ18").Value = WorksheetFunction.Transpose(Range("J" & lastRow + 1 & ":AA" & lastRow + 4))

And

Range("J" & lastRow + 1 & ":AA" & lastRow + 4).Copy
Range("AG1:AJ18").PasteSpecial Paste:=xlPasteValues, Transpose:=True

Which both work once when the file are generated, but when I manually modifiy the data within the table later, the tranposed data is not updated.

I read about "worksheet.change events", but they would need to be included in the code of each generated worksheet...which sounds complicated.

Isn't there an easier way to achieve this?

What version of Excel are you using?

Can you please update your profile?

The version used often determines the options available.

 

[hungtbatman1]

I read about "worksheet.change events", but they would need to be included in the code of each generated worksheet...which sounds complicated.

You are talking about Private Sub Worksheet_Change(ByVal Target As Range) in each sheet module.

Use Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) in ThisWorkbook module

1. Sh: sheet where the change occurred
2. Target: cell on sheet Sh where the change occurred.

 

[Pteroglossus]

What version of Excel are you using?

Can you please update your profile?

Office 365, profile updated!

Use Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) in ThisWorkbook module

To be honest, it was quite a struggle to get the macro to this point, and I'm hoping for something easier than creating a whole new sub.

There must be a way for the "transpose" VBA instruction to behave as a typical TRANSPOSE formula in the sheet.

If you need a screenshot or the whole code, please let me know.

 

[hungtbatman1]

To be honest, it was quite a struggle to get the macro to this point, and I'm hoping for something easier than creating a whole new sub.

There must be a way for the "transpose" VBA instruction to behave as a typical TRANSPOSE formula in the sheet.

If you need a screenshot or the whole code, please let me know.

If you want the code to react to changes made by the user, you have to write the appropriate code in Worksheet_Change. You already know that if you want to handle changes in different sheets and you write the same code in different Worksheet_Change, it is wrong and unwise. That is why I showed you where to write the code only once. Don't you want that? Do you have a better place for code that reacts to changes in different sheets?

 

[Fuji]

Try change that line to

[AG1].Formula2 = "=transpose(" & Range("J" & lastrow + 1 & ":AA" & lastrow + 4).Address & ")"

So that it will automatically updates when you change the source data.

 

[Pteroglossus]

Try change that line to

[AG1].Formula2 = "=transpose(" & Range("J" & lastrow + 1 & ":AA" & lastrow + 4).Address & ")"

So that it will automatically updates when you change the source data.

This is it!! It's working flawlessly, and I'm done with the macro. Thank you!

If you want the code to react to changes made by the user, you have to write the appropriate code in Worksheet_Change. You already know that if you want to handle changes in different sheets and you write the same code in different Worksheet_Change, it is wrong and unwise. That is why I showed you where to write the code only once. Don't you want that? Do you have a better place for code that reacts to changes in different sheets?

Thanks for your explanation but Fuji's solution is exactly what I meant by "easier way".

 

[Fuji]

This is it!! It's working flawlessly, and I'm done with the macro. Thank you!

You are welcome and thanks for the feedback.