Column number - copying and pasting from file to another file-CodePudding

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

CodePudding user response：

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