Copying Into New Column Each Time: HELP

[carsolemons]

Hello,
I'm new to VBA. I have finished a subroutine that allows me to press a button to run Macro1 that will open a dialogue box to select FileB (that contains data I want to import). Right now, it will import that data (always B4:B20) from Workbook1 and insert it into Workbook2.

The help I need:

I would like to adjust this macro so that each time it is run the imported data won't replace into one column but instead be inserted into the next empty column. i.e. Right now the first run places it into B4:B20 in Workbook 2, and I want the 2nd, 3rd, etc times it's run to place the data in column C, then D, etc.

This is what I have so far, but it only does it for the first piece of data. I want all 16 entries.

Thanks for any help!

Sub Macro1()
'
' Macro1 Macro
'
'
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
Set targetWorkbook = Application.ActiveWorkbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)

targetSheet.Range("B4:B20").Range("IV1").End(xlToLeft).Offset(0, 1).Value = sourceSheet.Range("B4:B20").Value

customerWorkbook.Close
End Sub

 

[JoeMo]

Dim nextColNum as long
nextColNum= targetSheet.Cells(4,columns.count).End(xlToLeft).Column + 1

 

[carsolemons]

Thank you.

The same thing keeps happening. Only the first piece of data is importing to that next column. Have I inserted your feedback in the wrong space maybe?

Sub Macro1()
'
' Macro1 Macro
'
'
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
Set targetWorkbook = Application.ActiveWorkbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)
Dim nextColNum As Long
nextColNum = targetSheet.Cells(4, Columns.Count).End(xlToLeft).Column + 1
targetSheet.Range("B4:B20").Range("IV1").End(xlToLeft).Offset(0, nextColNum).Value = sourceSheet.Range("B4:B20").Value

customerWorkbook.Close
End Sub

 

[JoeMo]

I'm guessing (because you didn't say what the named range IV1 represents) that your problem is in this line:

targetSheet.Range("B4:B20").Range("IV1").End(xlToLeft).Offset(0, nextColNum).Value

Maybe try:
targetSheet.Range(Cells(4,nextColNum),Cells(20,nextColNum)).Value = .....

 

[carsolemons]

SOLVED!

Thanks for the help JoeMo.

I had to work with it still. It started posting to the next column as desired, but only one row would go. It's probably not the most efficient way, but I simply duplicated the code so it would repeat for all 16 rows of data.

In case someone else stumbles into this situation here is the code I used:

Sub Macro1()
'
' Macro1 Macro
'
'
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
Set targetWorkbook = Application.ActiveWorkbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Select the file to import new data from "
customerFilename = Application.GetOpenFilename(filter, , caption)
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)
Dim nextColNum As Long
'Taking data from each row and putting into the next column every time the macro is run
nextColNum = targetSheet.Cells(4, Columns.Count).End(xlToLeft).Column
targetSheet.Range("B4").End(xlToLeft).Offset(, nextColNum).Value = sourceSheet.Range("B4").Value
nextColNum = targetSheet.Cells(5, Columns.Count).End(xlToLeft).Column
targetSheet.Range("B5").End(xlToLeft).Offset(, nextColNum).Value = sourceSheet.Range("B5").Value
nextColNum = targetSheet.Cells(6, Columns.Count).End(xlToLeft).Column
targetSheet.Range("B6").End(xlToLeft).Offset(, nextColNum).Value = sourceSheet.Range("B6").Value
nextColNum = targetSheet.Cells(7, Columns.Count).End(xlToLeft).Column
targetSheet.Range("B7").End(xlToLeft).Offset(, nextColNum).Value = sourceSheet.Range("B7").Value
nextColNum = targetSheet.Cells(8, Columns.Count).End(xlToLeft).Column
targetSheet.Range("B8").End(xlToLeft).Offset(, nextColNum).Value = sourceSheet.Range("B8").Value
nextColNum = targetSheet.Cells(9, Columns.Count).End(xlToLeft).Column
targetSheet.Range("B9").End(xlToLeft).Offset(, nextColNum).Value = sourceSheet.Range("B9").Value
nextColNum = targetSheet.Cells(10, Columns.Count).End(xlToLeft).Column
targetSheet.Range("B10").End(xlToLeft).Offset(, nextColNum).Value = sourceSheet.Range("B10").Value
nextColNum = targetSheet.Cells(11, Columns.Count).End(xlToLeft).Column
targetSheet.Range("B11").End(xlToLeft).Offset(, nextColNum).Value = sourceSheet.Range("B11").Value
nextColNum = targetSheet.Cells(12, Columns.Count).End(xlToLeft).Column
targetSheet.Range("B12").End(xlToLeft).Offset(, nextColNum).Value = sourceSheet.Range("B12").Value
nextColNum = targetSheet.Cells(13, Columns.Count).End(xlToLeft).Column
targetSheet.Range("B13").End(xlToLeft).Offset(, nextColNum).Value = sourceSheet.Range("B13").Value
nextColNum = targetSheet.Cells(14, Columns.Count).End(xlToLeft).Column
targetSheet.Range("B14").End(xlToLeft).Offset(, nextColNum).Value = sourceSheet.Range("B14").Value
nextColNum = targetSheet.Cells(15, Columns.Count).End(xlToLeft).Column
targetSheet.Range("B15").End(xlToLeft).Offset(, nextColNum).Value = sourceSheet.Range("B15").Value
nextColNum = targetSheet.Cells(16, Columns.Count).End(xlToLeft).Column
targetSheet.Range("B16").End(xlToLeft).Offset(, nextColNum).Value = sourceSheet.Range("B16").Value
nextColNum = targetSheet.Cells(17, Columns.Count).End(xlToLeft).Column
targetSheet.Range("B17").End(xlToLeft).Offset(, nextColNum).Value = sourceSheet.Range("B17").Value
nextColNum = targetSheet.Cells(18, Columns.Count).End(xlToLeft).Column
targetSheet.Range("B18").End(xlToLeft).Offset(, nextColNum).Value = sourceSheet.Range("B18").Value
nextColNum = targetSheet.Cells(19, Columns.Count).End(xlToLeft).Column
targetSheet.Range("B19").End(xlToLeft).Offset(, nextColNum).Value = sourceSheet.Range("B19").Value
nextColNum = targetSheet.Cells(20, Columns.Count).End(xlToLeft).Column
targetSheet.Range("B20").End(xlToLeft).Offset(, nextColNum).Value = sourceSheet.Range("B20").Value

customerWorkbook.Close
End Sub