Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have two workbooks and am working on some code that processes cells in one and then writing out the results to the other. I have to process sequentially; code could look something like:

    Workbooks(BookName1).Worksheets(SheetName1).Range( "A1") =
    Workbooks(BookName2).Worksheets(SheetName2).Range( "B4")

    Or:

    Variable=Workbooks(BookName1).Worksheets(SheetName 1).Range("A1")
    {calculations on Variable}
    Workbooks(BookName2).Worksheets(SheetName2).Range( "B4")=Variable

    I'm not a real user of the "set" command but I'm hoping there is a shorter notation.

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='ErikJan' post='785057' date='17-Jul-2009 07:52']I have two workbooks and am working on some code that processes cells in one and then writing out the results to the other. I have to process sequentially; code could look something like:

    Workbooks(BookName1).Worksheets(SheetName1).Range( "A1") =
    Workbooks(BookName2).Worksheets(SheetName2).Range( "B4")

    Or:

    Variable=Workbooks(BookName1).Worksheets(SheetName 1).Range("A1")
    {calculations on Variable}
    Workbooks(BookName2).Worksheets(SheetName2).Range( "B4")=Variable

    I'm not a real user of the "set" command but I'm hoping there is a shorter notation.[/quote]

    Assuming BookName1.SheetName1 is active; try
    Code:
    	 With Workbooks(BookName2).Worksheets(SheetName2)
    		 Range("A1") = .Range("B4")
    		 Range("A2") = .Range("B5")
    		 Range("A3") = .Range("B6")		
    	 End With
    Regards
    Don

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Or use variables:

    Dim wsh1 As Worksheet
    Dim wsh2 As Worksheet
    Set wsh1 = Workbooks(BookName1).Worksheets(SheetName1)
    Set wsh2 = Workbooks(BookName2).Worksheets(SheetName2)

    You can now use

    wsh1.Range("A1") = wsh2.Range("B4")

    or

    Variable = wsh1.Range("A1")
    ...
    ...
    wsh2.Range("B4") = Variable

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='785116' date='17-Jul-2009 20:23']Or use variables:

    Dim wsh1 As Worksheet
    Dim wsh2 As Worksheet
    Set wsh1 = Workbooks(BookName1).Worksheets(SheetName1)
    Set wsh2 = Workbooks(BookName2).Worksheets(SheetName2)

    You can now use

    wsh1.Range("A1") = wsh2.Range("B4")

    or

    Variable = wsh1.Range("A1")
    ...
    ...
    wsh2.Range("B4") = Variable[/quote]

    Hans, Don, thanks for the feedback, this is indeed what I've been looking for!

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you have multiple cell combinations that you need to process, you might consider putting a table in the workbook with the code somewhere that lists the worksheet names and cell references for both source and target workbook. Then your code can read that table and loop through it to do the "copying". If you need to add and or update sources and/or destinations you can simply edit the cells in the table.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •