copying in the data to always choose the next column to paste data. Need VBA help here. Thanks!

[pedie]

Hi, I tried to solve this myself but cant think on how to at the moment....first time data in copied in i want it to be pasted to A1, however the next time it brings in the data i want it to keep on pasting it in +1 column...

Thanks in advance for helping!!!
current code. I have hilighted in BOLD Option Explicit

[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New]Sub BringinDesiredData_col()[/FONT]
[FONT=Courier New]Dim thislr, lr As Long[/FONT]
[FONT=Courier New]Dim colHead As Long[/FONT]
[FONT=Courier New]Dim myfound As Range[/FONT]
[FONT=Courier New]Dim wb As Workbook[/FONT]
[FONT=Courier New]Dim tofind As String[/FONT]
[FONT=Courier New]thislr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row[/FONT]
[FONT=Courier New]myfile = "C:\Users\pediez\Desktop\Data.xlsm"[/FONT]
[FONT=Courier New]Set wb = Workbooks.Open(Filename:=myfile)[/FONT]
[FONT=Courier New]ThisWorkbook.Activate[/FONT]
[FONT=Courier New]For Each colHead In Sheet1.Range("A1:A" & thislr)[/FONT]
[FONT=Courier New]       If colHead.Value <> "" Then[/FONT]
[FONT=Courier New]       tofind = colHead.Text[/FONT]
[FONT=Courier New]       wb.Activate[/FONT]
[FONT=Courier New]       Sheets("alldata").Activate[/FONT]
[FONT=Courier New]       lr = Sheets("alldata").Range("A" & Rows.Count).End(xlUp).Row[/FONT]
[FONT=Courier New]           With Sheets("alldata").Rows("1:1")[/FONT]
[FONT=Courier New]           Set myfound = .Find(What:=tofind, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole)[/FONT]
[FONT=Courier New]           If myfound Is Nothing Then[/FONT]
[FONT=Courier New]           MsgBox myfound & "not found"[/FONT]
[FONT=Courier New]           Exit Sub[/FONT]
[FONT=Courier New]           Else[/FONT]
[FONT=Courier New]           [B][U][COLOR=blue]myfound.Resize(lr).copy thisworkbook.Sheets("Sheet1").[/COLOR][/U][/B][/FONT]
[FONT=Courier New]           End If[/FONT]
[FONT=Courier New]           End With[/FONT]
[FONT=Courier New]       End If[/FONT]
[FONT=Courier New]Next colHead[/FONT]
[FONT=Courier New]End Sub[/FONT]

 

[VoG]

Hi Pedie. Maybe

 myfound.Resize(LR).Copy ThisWorkbook.Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)

 

[pedie]

Hi Peter, i tried that line and it start pasting data from COL B
Improved code....please validate...

[/FONT]
[FONT=Courier New]Option Explicit
Sub BringinDesiredData_col()
Dim thislr, lr As Long
Dim colHead As Range
Dim myfound As Range
Dim wb As Workbook
Dim tofind As String
Dim col As String
Dim myfile As String
Dim mycol As Integer[/FONT]
[FONT=Courier New]mycol = 1
thislr = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
myfile = "C:\Users\Pediez\Desktop\Data.xlsm"
Set wb = Workbooks.Open(Filename:=myfile)
ThisWorkbook.Activate
For Each colHead In ThisWorkbook.Sheets("Sheet1").Range("A1:A" & thislr)
        If colHead.Value <> "" Then
        tofind = colHead.Text
        wb.Activate
        Sheets("alldata").Activate
        lr = Sheets("alldata").Range("A" & Rows.Count).End(xlUp).Row
            With Sheets("alldata").Rows("1:1")
            Set myfound = .Find(What:=tofind, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole)
            If myfound Is Nothing Then
            MsgBox tofind & "not found"
            GoTo closewb
            Exit Sub
            Else
            myfound.Resize(lr).Copy ThisWorkbook.Sheets("Here").Columns(0 + mycol)
          
            End If
            End With
            
            mycol = mycol + 1
        End If
Next colHead
GoTo closewb
End
closewb:
wb.Close False
End
End Sub