'Column number - copying and pasting from file to another file
I have a little problem with my code. I want to copy my data from one file to my main file. When I check the code I meet with one problem. The code works fine until copied. In this line
Set cell1 = wsDest.Cells(1, Range("B1").End(xlToRight).Column + 1)
the cell is selected from the file from I am taking the data and not the folder I am pasting into.
I want my data to paste from these other files into the main file. I want to add them as columns, not rows.
Sub MoveCopyRowsColumns()
Dim mainWb As Workbook
Dim newWb As Workbook
Dim mainWs As Worksheet
Dim newWs As Worksheet
Dim strFolder As String
Set mainWb = Workbooks("Main_file.xlsm")
Set mainWs = mainWb.Worksheets("Worksheet1")
mainWs.Cells(1, Range("B1").End(xlToRight).Column + 1).Select
mainWs.Columns(ActiveCell.Column).EntireColumn.Delete
strFolder = "C:\Users\User1\Desktop\Folder_with_files\"
strFile = Dir(strFolder & "*.xls*")
Do While strFile <> ""
Set newWb = Workbooks.Open(strFolder & strFile)
Set newWs = newWb.Sheets(1)
strFile = Dir
newWs.Cells(1, Range("B1").End(xlToRight).Column + 1).Select
newWs.Columns(ActiveCell.Column).EntireColumn.Delete
newWs.Range("B1", Range("B1").End(xlDown).End(xlToRight)).Copy _
mainWs.Range("P1")
Loop
End Sub
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|