workbooks

[jompy]

Hi guys

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Can anybody help out? Looking to run a loop (For I = 1 to 50) sort of thing. No problem with that, but I want to loop through two different workbooks.

So active workbook (lets call it book insect, sheet beetle) needs to be populated with data from source workbook (lets call it book animals, sheet lion)

Both workbooks are on C drive, and in a file named “Lite Software”

Just need to know how to address both workbooks so loop is performed.

Many thanks

 

[Richard Schollar]

Hi

Just set object references to each file and then use these:

Dim wbInsect As Workbook, wbAnimals as Workbook
Dim wsBeetle As Worksheet, wsLion As Worksheet

Set wbInsect = Workbooks.Open("C:\Lite Software\Insect.xls")
Set wbAnimals = Workbooks.Open("C:\Lite Software\Animals.xls")

Set wsBeetle = wbInsect.Worksheets("Beetle")
Set wsLion = wbAnimals.Worksheets("Lion")


'now use them:
For i = 1 to 50
   wsBeetle.Cells(i,1) = wsLion.Cells(i+10,1)
Next i

 

[jompy]

Hi Richard

Thanks a lot for sharing works a treat. Had to do it from a third workbook but not a problem really, not sure why?

Thanks again

Jompy

 

[Richard Schollar]

You have a reference available to the workbook in which any code resides which is ThisWorkbook. Hence, if you have the code in either of the Insects or Animals workbook, change the way this is referenced to:

'assume code stored in Animals.xls

Set wsLion = ThisWorkbook.Sheets("Lion")   'ThisWorkbook is an object reference that points at the workbook in which the code resides

 

[jompy]

Hi Richard

That's done it.

Thanks very much for your speedy replies.

Jompy