Column number - copying and pasting from file to another file

Question

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

Stop using `.Select` (see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba)). And specify in which workbook and worksheet your `Cells` and `Columns` are. If you don't specify them Excel cannot know which workbook and worksheet you mean when you just write `Cells(1, Range("B1").End(xlToRight).Column + 1)`. • Also if you `Set cell1` then this is already a range so instead of `wsDest.Range(cell1).PasteSpecial` you need to use just `cell1.PasteSpecial`. — Pᴇʜ, Feb 11 '22 at 10:04

score 1 · Accepted Answer · answered Feb 11 '22 at 10:27

You have to declare which file/sheet is which. Each line should refer to the right worksheet. All lines starting with cell or range should have worksheet first like: "mainWs.Cell". In the new file you have not declared any worksheet, only workbook (wb). I haven't tryed the code below, but I hope it unlocks your problem thinking. Good luck!

Sub MoveCopyRowsColumns()
Dim mainWb As Workbook
Dim newWb As Workbook
Dim mainWs As Worksheet
Dim newWs As Worksheet
Dim strFolder As String
Dim strFile As String
Dim cell1 As Range

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
'in my main file I delete the last column  only one

    strFolder = "C:\Users\User1\Desktop\Folder_with_files\"
    strFile = Dir(strFolder & "*.xls*")
    Do While strFile <> ""
        Set newWb = Workbooks.Open(strFolder & strFile)
        'Set the sheet you want to use, using "first sheet" or sheet by name
        'Set newWs = newWb.Sheets(1)
        'Set newWs = newWb.Worksheets("Worksheet1")
        strFile = Dir
        
        newWs.Cells(1, Range("B1").End(xlToRight).Column + 1).Select
        newWs.Columns(ActiveCell.Column).EntireColumn.Delete
        
        'Set cell1 = newWs.Cells(1, Range("B1").End(xlToRight).Column + 1)
        newWs.Cells(1, Range("B1").End(xlToRight).Column + 1).Copy  
        'the adress is taking from file when I take the data, not the main file which should take.
        
        mainWs.Range(cell1).PasteSpecial Paste:=xlPasteValues
    Loop

End Sub

Column number - copying and pasting from file to another file

1 Answers1