Shailesh Shinde
asked on
Excel Macro - Copy & Paste columns
Hi All,
I have an requirement for batch processing to copy the column "C" data into column "D" from all the worksheets of multiple excel file in a folder. Can you please provide any reference or sample code for this.
Thanks,
Shail
I have an requirement for batch processing to copy the column "C" data into column "D" from all the worksheets of multiple excel file in a folder. Can you please provide any reference or sample code for this.
Thanks,
Shail
ASKER
Hi,
We have multiple excel files in a folder. Each excel file has "n" number of worksheets and in each worksheet column "c" data needs to be paste into column "D". We would like to use macro in which user will select the folder and this gets processed.
Thanks,
Shail
We have multiple excel files in a folder. Each excel file has "n" number of worksheets and in each worksheet column "c" data needs to be paste into column "D". We would like to use macro in which user will select the folder and this gets processed.
Thanks,
Shail
Column C data of each sheets from all the files from a folder need to be pasted in col. D of Sheet1 of Macro workbook?
Also is there header in C1?
Also is there header in C1?
ASKER
Hi,
Yes, column "c" has header "Test1" and column "D" has header "replace".
Thanks,
Shail
Yes, column "c" has header "Test1" and column "D" has header "replace".
Thanks,
Shail
ASKER
Hi,
Column C data of each sheets from all the files from a folder need to be pasted in col. D of there respective sheets only.
For example...
excelfile1.xls
Sheet1->columnC paste in Sheet1->columnD
Sheet2->columnC paste in Sheet2->columnD
Sheet3->columnC paste in Sheet3->columnD
excelfile2.xls*
Sheet1->columnC paste in Sheet1->columnD
Sheet2->columnC paste in Sheet2->columnD
Sheet3->columnC paste in Sheet3->columnD
Thanks,
Shail
Column C data of each sheets from all the files from a folder need to be pasted in col. D of there respective sheets only.
For example...
excelfile1.xls
Sheet1->columnC paste in Sheet1->columnD
Sheet2->columnC paste in Sheet2->columnD
Sheet3->columnC paste in Sheet3->columnD
excelfile2.xls*
Sheet1->columnC paste in Sheet1->columnD
Sheet2->columnC paste in Sheet2->columnD
Sheet3->columnC paste in Sheet3->columnD
Thanks,
Shail
Does that mean, all the files have only three sheets in them?
Also the data from these files will be appended in col. D of sheet1,2 and 3 and also initially clearing the col. D of these sheets before the code paste the col. C in them or just paste the data below the last row of existing data?
Also the data from these files will be appended in col. D of sheet1,2 and 3 and also initially clearing the col. D of these sheets before the code paste the col. C in them or just paste the data below the last row of existing data?
What are the actual sheet names in those files, are they always named as Sheet1, Sheet2 and Sheet3?
Same query for the macro workbook.
Same query for the macro workbook.
ASKER
Hi,
Sheet names were different and not necessary that it always contains three sheets might contains more or less.
Thanks,
Shail
Sheet names were different and not necessary that it always contains three sheets might contains more or less.
Thanks,
Shail
ASKER
Hi,
Column "D" will be always blank. So, Column "C" data needs to be paste into Column "D".
Thanks,
Shail
Column "D" will be always blank. So, Column "C" data needs to be paste into Column "D".
Thanks,
Shail
Does that mean sheet index will play a role to decide that column C of which sheet needs to be pasted in column D of which sheet in the macro workbook?
e.g. File1
Sheets(1).Columns("C") goes into the Sheets(1).Columns("D")
.
.
.
Sheets(4).columns("C") goes into the Sheets(4).Columns("D")
Is that assumption correct?
Also what is the file extension of excel files you want to copy the data from?
e.g. File1
Sheets(1).Columns("C") goes into the Sheets(1).Columns("D")
.
.
.
Sheets(4).columns("C") goes into the Sheets(4).Columns("D")
Is that assumption correct?
Also what is the file extension of excel files you want to copy the data from?
ASKER
Hi,
Yes, this assumption is correct. Extensions will be .xlsx
Thanks,
Shail
Yes, this assumption is correct. Extensions will be .xlsx
Thanks,
Shail
Column "D" will be always blank. So, Column "C" data needs to be paste into Column "D".Okay. But what if col. C from the more than one file needs to be pasted in col. D of one sheet? Data needs to be appended then?
Please try this to see if that works for you...
Sub CopyDataFromColumnC()
Dim swb As Workbook, wb As Workbook
Dim sws As Worksheet, ws As Worksheet
Dim lr As Long, i As Long
Dim fso As Object
Dim dFolder As Object
Dim dFile As Object
Dim SelectedFolder As String
Application.ScreenUpdating = False
Set swb = ThisWorkbook
Set fso = CreateObject("Scripting.FileSystemObject")
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select A Folder!"
.ButtonName = "Confirm"
If .Show = -1 Then
SelectedFolder = .SelectedItems(1)
Set dFolder = fso.GetFolder(SelectedFolder)
Else
MsgBox "You didn't select a folder.", vbExclamation, "Folder Not Selected!"
Exit Sub
End If
End With
For Each dFile In dFolder.Files
If Left(fso.GetExtensionName(dFile), 4) = "xlsx" Then
Workbooks.Open dFile
Set wb = ActiveWorkbook
For i = 1 To wb.Worksheets.Count
Set ws = wb.Sheets(i)
On Error Resume Next
Set sws = swb.Sheets(i)
On Error GoTo 0
If Not sws Is Nothing Then
lr = ws.Cells(Rows.Count, 3).End(xlUp).Row
If lr > 1 Then
ws.Range("C2:C" & lr).Copy sws.Range("D" & Rows.Count).End(3)(2)
End If
End If
Set sws = Nothing
Next i
wb.Close True
End If
Set wb = Nothing
Next dFile
Application.ScreenUpdating = True
MsgBox "Data from Column C from all the files has been successfully copied.", vbInformation, "Done!"
End Sub
ASKER
Hi,
I have created the .xlsm file and added this line of code. When process files from the folder, this code copies the column C data and paste it in column D of .xlsm file instead of open file from folder column D.
Thanks,
Shail
I have created the .xlsm file and added this line of code. When process files from the folder, this code copies the column C data and paste it in column D of .xlsm file instead of open file from folder column D.
Thanks,
Shail
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot!
You're welcome.
https://raywoodcockslatest.wordpress.com/2014/11/29/excel-for-batch/