Home

Excel Makro: Markierte Spalten in nächste leere Spalten einfügen

%3CLINGO-SUB%20id%3D%22lingo-sub-780738%22%20slang%3D%22de-DE%22%3EExcel%20Macro%3A%20Insert%20selected%20columns%20into%20next%20empty%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-780738%22%20slang%3D%22de-DE%22%3E%3CP%3EGreetings%20God%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20ask%20a%20question%2C%20how%20can%20I%20write%20in%20the%20Excel%20macro%20if%20I%20mark%20and%20copy%20certain%20columns%20and%20want%20to%20add%20them%20to%20the%20back%20of%20an%20existing%20Excel.%3C%2FP%3E%3CP%3Ee.g.%20I%20mark%20column%20A%20to%20X%20from%20the%20Excel%20tab%20%22Excel_alt%22%20and%20want%20to%20insert%20this%20in%20the%20Excel%20tab%20%22Excel_new%22%20at%20the%20next%20free%20column.%3C%2FP%3E%3CP%3EIf%20now%20in%20%22Excel_new%22%20in%20row%201%20is%20the%20last%20filled%20column%20M%20and%20in%20row%202%20is%20the%20L%20and%20in%20row%203%20is%20the%20N%2C%20then%20please%20first%20insert%20from%20column%20O.%3C%2FP%3E%3CP%3EIt%20may%20be%20that%20there%20are%20also%20300%20rows%20here%2C%20is%20there%20a%20command%2C%20without%20having%20to%20check%20in%20each%20single%20row%20which%20is%20the%20last%20column%20described%20and%20thus%20the%20selected%20columns%20can%20be%20inserted%20a%20column%20afterwards%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%3C%2FP%3E%3CP%3EDear%20greetings%3C%2FP%3E%3CP%3ELisa%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-780738%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-780803%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Makro%3A%20Markierte%20Spalten%20in%20n%C3%A4chste%20leere%20Spalten%20einf%C3%BCgen%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-780803%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Lisa%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attach%20my%20solution.%20I%20would%20first%20use%20a%20formula%20to%20determine%20the%20last%20column%20with%20data%20in%20Excel_new%20sheet%2C%20then%20use%20the%20result%20in%20a%20copy-paste%20macro.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20that%20your%20maximum%20data%20range%20in%20the%20Excel_new%20sheet%20is%20A1%3AAZ100%2C%20the%20formula%20would%20be%20%3CFONT%3E%3DMAX(IF(LEN(Excel_new!A1%3AAZ100)%2CCOLUMN(Excel_new!A%3AAZ)%2C0))%3C%2FFONT%3E%2C%20which%20needs%20to%20be%20array-entered%20by%26nbsp%3B%20selecting%20cell%20with%20the%20formula%2C%20pressing%20F2%2C%20then%20Ctrl%2BShift%2BEnter.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20that%20the%20cell%20with%20the%20formula%20is%20named%20'%3CEM%3E%3CFONT%3Elast_column_with_data%3C%2FFONT%3E'%2C%3C%2FEM%3Eyour%20'Excel_alt'%20data%20range%20is%20named%20'%3CEM%3E%3CFONT%3EAlt_Data%3C%2FFONT%3E'%3C%2FEM%3E%2C%20and%20the%20'Excel_new'%20sheet%20is%20named%20%3CEM%3ESheet3%3C%2FEM%3Ein%20the%20VBA%20editor%2C%20you%20can%20use%20the%20following%20macro%20to%20copy%20data%20across%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CFONT%3E%3CBR%20%2F%3ESub%20paste_after_last_data_column()%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20vaAltData%20As%20Variant%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20shtNew%20As%20Worksheet%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20iLastColNo%20As%20Integer%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Set%20shtNew%20%3D%20Sheet3%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20'Load%20Alt%20data%20into%20array%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20vaAltData%20%3D%20Range(%22Alt_Data%22)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20'Load%20last%20column%20number%20with%20data%20in%20Excel_new%20sheet%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20iLastColNo%20%3D%20Range(%22last_column_with_data%22).Value2%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20'Paste%20Alt%20data%20in%20New%20sheet%20after%20last%20non-empty%20column%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20shtNew.Cells(1%2C%20iLastColNo%20%2B%201).Resize(UBound(vaAltData%2C%201)%2C%20UBound(vaAltData%2C%202))%20%3D%20vaAltData%3CBR%20%2F%3E%26nbsp%3B%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CFONT%3EHope%20this%20helps%20%26nbsp%3B%20%26nbsp%3B%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%3EThanks%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%3EYury%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3C%2FFONT%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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 markiere Spalte A bis X vom Excel-Registerblatt "Excel_alt" und will das bei der nächsten freien Spalte einfügen im Excel-Registerblatt "Excel_neu" einfügen.

Wenn nun im "Excel_neu" in Zeile 1 die letzte befüllte Spalte M ist und in Zeile 2 ist das L und in Zeile 3 ist das N, dann soll bitte erst ab Spalte O eingefügt werden.

Es kann sein, dass hier auch 300 Zeilen sind, gibt es einen Befehl, ohne dass man in jeder einzelnen Zeile überprüfen muss, welche die letzte beschriebene Spalte ist und somit dann die markierten Spalten eine Spalte danach eingefügt werden können?

 

Vielen Dank im Voraus!

Liebe Grüße

Lisa

1 Reply
Highlighted

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
 
Hope this helps    
Thanks
Yury
   

 

 

 

Related Conversations
Request is invalid?
Thriftman in Excel on
0 Replies
Drop down list on ipad
Skippy95 in Excel on
0 Replies
Using another sheet like function
Phobosd in Excel on
0 Replies
VLOOKUP Function Help
dustinhammerle in Excel on
2 Replies
Data Validation with a string in a cell
Dongda in Excel on
0 Replies
Macros
ColinK in Excel on
1 Replies