Prev: delete rows with header, excluding 1st row
Next: How Do I?
From: Don Guillett on 31 Mar 2010 08:53
application.screenupdating=false
code
reset to true

or, use formula links to closed file.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Peter" <noMorespam(a)MSUK.com> wrote in message
news:uIUz4zG0KHA.4636(a)TK2MSFTNGP06.phx.gbl...
> Thanks for the help that works, just one more question.
>
> When I open the second workbook, the one that I'm coping data from
> Workbooks.Open Filename:="c:\UserInfor.xls" the workbook actually opens
> and is visible to the user. Is there a way to hide it so a person can't
> see it?
>
>
> "Peter" <noMorespam(a)MSUK.com> wrote in message
> news:umc%23uCF0KHA.4752(a)TK2MSFTNGP04.phx.gbl...
>> Hello
>>
>> I'm working a project where I've got a list of names in one spreadsheet,
>> and I need to pull corrasponding data from another spreadsheet. The
>> concept is simple.... get a name from spreadsheet1, then go into
>> spreadsheet2, find that username, and copy the row over to spreadsheet1.
>> At least that is how you do it manually.
>>
>> In excel VBa, from one workbook, how do I a open another workbook and
>> worksheet then get the correct data i need?
>>
>>
>>
>
>

From: Peter on 31 Mar 2010 10:40
I'm able to find what I need, just having some issues with the syntax for
coping a row of data from the source sheet to the destination.

Here is what I've got so far...

Sub FindStuff()
Dim wbkthis As Workbook
Dim shtthis As Worksheet
Dim rngThis As Range
Dim rngFind As Range
Dim firstAddress As String
Dim addSelection As String
Dim cnt
Dim Workbook
Dim Worksheet
Dim Xobj
Dim SrcRange As Range


Set wbkthis = ThisWorkbook
Set shtthis = wbkthis.Worksheets("UserList")
Workbooks.Open Filename:="c:\UserInfo.xls", ReadOnly:=True
Set Xobj = ActiveWorkbook.Sheets("owssvr(1)") 'owssvr is in userinfo.xls
Windows("UserInfo.xls").Visible = False


Set SrcRange = Xobj.Range("B02", "B275")

With SrcRange
Set rngFind = .Find("username1")

cnt = 0
If Not rngFind Is Nothing Then
firstAddress = rngFind.Address 'Take a note of where we first
found it
addSelection = addSelection & rngFind.Address & "," 'Add the
cell's range to our selection
rngFind.EntireRow.Copy
Workbooks(wbkthis).Sheets(shtthis).Range("H2") '(attempt to copy)
cnt = cnt + 1
End If
End With
Debug.Print cnt
Workbooks("UserInfo.xls").Close SaveChanges:=False
End Sub



The idea, is in the userlist workbook(ThisWorkBook) I've got a list of user
namea, and I'm searching for the match over in userInfo.xls, then I'd copy
that row, and paste it in the workbook. So if I want to pull information
about username1, which happens to be at H3 in this example, I search
UserInfo.xls, when I find the match, I copy the row and paste it in the cell
next to the username, H3 H4 etc etc.




"Peter" <noMorespam(a)MSUK.com> wrote in message
news:umc%23uCF0KHA.4752(a)TK2MSFTNGP04.phx.gbl...
> Hello
>
> I'm working a project where I've got a list of names in one spreadsheet,
> and I need to pull corrasponding data from another spreadsheet. The
> concept is simple.... get a name from spreadsheet1, then go into
> spreadsheet2, find that username, and copy the row over to spreadsheet1.
> At least that is how you do it manually.
>
> In excel VBa, from one workbook, how do I a open another workbook and
> worksheet then get the correct data i need?
>
>
>


First  |  Prev  | 
Pages: 1 2
Prev: delete rows with header, excluding 1st row
Next: How Do I?