Import data from workbooks and change the range

Question

I would like to copy data from one workbook and paste into the next available column of the active workbook.

I have been able to import the data from one workbook and paste it into the active sheet at a specified cell.

Sub ImportData()

Dim FileToOpen As Variant
Dim Openbook As Workbook

Application.ScreenUpdating = False

FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.csv*), *csv*")
    If FileToOpen <> False Then
        Set Openbook = Application.Workbooks.Open(FileToOpen)
        Openbook.Sheets(1).Range("B2:B145").Copy
        ThisWorkbook.Worksheets(ActiveSheet.Name).Range("B3").PasteSpecial xlPasteValues
        Openbook.Close False
    End If
    
Application.ScreenUpdating = True

End Sub

How do I change the range from B3 to the next available column, so it will begin on B3 then C3 then D3 etc., after every workbook I select?

score 0 · Answer 1 · answered Nov 18 '20 at 13:12

0

Instead of using the literal "B2:B145" use a range specified by cells, e.g.

Range(Cells(2,i),Cells(145,i))

where i iterates over the columsn you need.

Same in the target:

Range(Cells(j,k))

where j is ruwnumber and k column of target first cell.

answered Nov 18 '20 at 13:12

YeOldHinnerk

73
8

Sorry I am new to VBA and have limited knowledge on the topic. Is there a change I can make to to the Range("B3") part of the code that will paste the first set of data from B2:B145 into cell B3 then the next will be C3 etc? – Peter Sheerin Nov 18 '20 at 13:38
Implicit `ActiveSheet` is bad practice and leads to difficult-to-debug errors. See [How to avoid using select](https://stackoverflow.com/q/10714251/8769365) – Nacorid Nov 18 '20 at 13:39
Use good practices. Use ThisWorkbook instead of ActiveWorkbook - most of the time they are identical, but now always! Use with/end with to avoid long objects. – YeOldHinnerk Nov 18 '20 at 16:16
@YeOldHinnerk Thanks for the tips! I still have a question regarding the Range(Cells(j,k)) section? how can this be used to paste the in the next available column? for example paste B2:B145 from one workbook into a new workbook cells B3 then C3 etc – Peter Sheerin Nov 18 '20 at 16:32

score 0 · Answer 2 · answered Nov 22 '20 at 06:00

Declare a public Range in a module:

Public CurrentRange As Range

Inside your ImportData() subroutine, you need to initialize it to the starting range:

If CurrentRange Is Nothing Then CurrentRange = [b3]

After you have used it, move it along to the next range:

Openbook.Sheets(1).Range("B2:B145").Copy
ThisWorkbook.Worksheets(ActiveSheet.Name).CurrentRange.PasteSpecial xlPasteValues
Openbook.Close False   
CurrentRange = CurrentRange.Offset(0,1)

score -1 · Answer 3 · edited Nov 22 '20 at 05:54

Is this what you are looking for?

Sub ImportData()

Dim FileToOpen As Variant
Dim Openbook As Workbook
Dim i As Integer                    '<~~~ Add integer for next range

Application.ScreenUpdating = False
Application.DisplayAlerts = False   '<~~~ Add for any pop up screen

FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.csv*), *csv*")
    If FileToOpen <> False Then
        
        For i = 2 To 4              '<~~~ 2=B 3=C 4=D and so on
        
        Set Openbook = Application.Workbooks.Open(FileToOpen)
        Openbook.Sheets(1).Range("B2:B145").Copy
        ThisWorkbook.Worksheets(ActiveSheet.Name).Cells(3, i).PasteSpecial xlPasteValues '<~~~ range change to cell
    

         Openbook.Close False
         
        Next i                      '<~~~ next after B then C then D...
    End If

    
Application.ScreenUpdating = True
Application.DisplayAlerts = True    '<~~~ Add for any pop up screen

End Sub

Import data from workbooks and change the range

3 Answers3