Forum Discussion
Excel Makro: Markierte Spalten in nächste leere Spalten einfügen
Grüß Gott, Ich hätte bitte eine Frage, wie kann ich im Excel-Makro schreiben, wenn ich gewisse Spalten markiere und kopiere und diese hinten bei einem bestehende Excel dazufügen will. zB ich mar...
Hi Lisa,
I attach my solution. I would first use a formula to determine the last column with data in Excel_new sheet, then use the result in a copy-paste macro.
Assuming that your maximum data range in the Excel_new sheet is A1:AZ100, the formula would be =MAX(IF(LEN(Excel_new!A1:AZ100),COLUMN(Excel_new!A:AZ),0)), which needs to be array-entered by selecting cell with the formula, pressing F2, then Ctrl+Shift+Enter.
Assuming that the cell with the formula is named 'last_column_with_data', your 'Excel_alt' data range is named 'Alt_Data', and the 'Excel_new' sheet is named Sheet3 in the VBA editor, you can use the following macro to copy data across:
Sub paste_after_last_data_column()
Dim vaAltData As Variant
Dim shtNew As Worksheet
Dim iLastColNo As Integer
Set shtNew = Sheet3
'Load Alt data into array
vaAltData = Range("Alt_Data")
'Load last column number with data in Excel_new sheet
iLastColNo = Range("last_column_with_data").Value2
'Paste Alt data in New sheet after last non-empty column
shtNew.Cells(1, iLastColNo + 1).Resize(UBound(vaAltData, 1), UBound(vaAltData, 2)) = vaAltData
End Sub
Dim shtNew As Worksheet
Dim iLastColNo As Integer
Set shtNew = Sheet3
'Load Alt data into array
vaAltData = Range("Alt_Data")
'Load last column number with data in Excel_new sheet
iLastColNo = Range("last_column_with_data").Value2
'Paste Alt data in New sheet after last non-empty column
shtNew.Cells(1, iLastColNo + 1).Resize(UBound(vaAltData, 1), UBound(vaAltData, 2)) = vaAltData
End Sub
Hope this helps
Thanks
Yury