[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel Macro - Copy & Paste columns

Posted on 2016-07-25
17
Medium Priority
?
106 Views
Last Modified: 2016-07-27
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
0
Comment
Question by:Shailesh Shinde
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
17 Comments
 
LVL 3

Expert Comment

by:Shabbir Rao
ID: 41728867
Your question is a bit unclear to me but I think this link would be helpful.

https://raywoodcockslatest.wordpress.com/2014/11/29/excel-for-batch/
0
 
LVL 3

Author Comment

by:Shailesh Shinde
ID: 41728876
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
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41728933
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?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 3

Author Comment

by:Shailesh Shinde
ID: 41728939
Hi,
Yes, column "c" has header "Test1" and column "D" has header "replace".

Thanks,
Shail
0
 
LVL 3

Author Comment

by:Shailesh Shinde
ID: 41728942
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
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41728951
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?
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41728953
What are the actual sheet names in those files, are they always named as Sheet1, Sheet2 and Sheet3?
Same query for the macro workbook.
0
 
LVL 3

Author Comment

by:Shailesh Shinde
ID: 41728964
Hi,
Sheet names were different and not necessary that it always contains three sheets might contains more or less.

Thanks,
Shail
0
 
LVL 3

Author Comment

by:Shailesh Shinde
ID: 41728965
Hi,

Column "D" will be always blank. So, Column "C" data needs to be paste into Column "D".

Thanks,
Shail
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41728970
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?
0
 
LVL 3

Author Comment

by:Shailesh Shinde
ID: 41728971
Hi,
Yes, this assumption is correct. Extensions will be .xlsx

Thanks,
Shail
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41728973
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?
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41728994
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

Open in new window

0
 
LVL 3

Author Comment

by:Shailesh Shinde
ID: 41729271
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
0
 
LVL 33

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41729386
I think, I misunderstood the requirement.
Okay try this....
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)
         lr = ws.Cells(Rows.Count, 3).End(xlUp).Row
         If lr > 1 Then
            ws.Range("C2:C" & lr).Copy ws.Range("D2")
         End If
      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

Open in new window

0
 
LVL 3

Author Closing Comment

by:Shailesh Shinde
ID: 41730699
Thanks a lot!
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41730700
You're welcome.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question