CODEWITHSUNDEEP
vbaexcel
Cwala
Cwala

Reputation: 29

How to insert extra column

the folowing code allows me to browse for multiple different excel files and paste them in a single sheet below each other.the excel file have the same column names but have different data in them and is working fine, my problem is i need it when it paste a file it must also create extra column and write the name of that file in that column for each and every file it paste. 

Sub Button5_Click()
 Dim fileStr As Variant
 Dim wbk1 As Workbook, wbk2 As Workbook
 Dim ws1 As Worksheet

 fileStr = Application.GetOpenFilename(FileFilter:="microsoft excel files (*.xlsx), *.xlsx", Title:="Get File", MultiSelect:=True)
 Set wbk1 = ActiveWorkbook
 Set ws1 = wbk1.Sheets("Sheet3")

 'handling first file seperately
 MsgBox fileStr(1), , GetFileName(CStr(fileStr(1)))
 Set wbk2 = Workbooks.Open(fileStr(1))
 wbk2.Sheets(1).UsedRange.Copy ws1.Cells(ws1.Range("A" & Rows.Count).End(xlUp).Row + 2, 1)

 wbk2.Close

 For i = 2 To UBound(fileStr)
 MsgBox fileStr(i), , GetFileName(CStr(fileStr(i)))

 Set wbk2 = Workbooks.Open(fileStr(i))
 'using offset to skip the header - not the best solution,  but a quick one

 wbk2.Sheets(1).UsedRange.Offset(1, 0).Copy ws1.Cells(ws1.Range("A" & Rows.Count).End(xlUp).Row + 2, 1)

 wbk2.Close
 Next i
 End Sub

Upvotes: 0

Views: 186

Answers (1)

Olle Sjögren
Olle Sjögren

Reputation: 5385

Use the Insert method of a Range object to insert a column:

'***** Inserts new column to the left of column C
Range("C:C").Insert

Entering text in a cell:

'***** Entering text in A1
ws1.Cells(1, 1).Value = fileStr(i)

Upvotes: 1

Related Questions