copy data from one workbook to an other

[edwin13387]

Good morning,

I am not experienced in VBA, but trying to learn by copying other code's.
So far, with a lot of assistance i have been able to handle it.
But now i have a code that i can't get to work, and the forum i normally address doesn’t get it ether.

It is supposed to look at the data, and if certain field from the history file are empty it should copy them back to the report.
(so if a certain action isn't finished the next shift knows it's still ongoing)

This is the code:

Please Login or Register  to view this content.

It does all it needs to do, except for placing it in the other file.
The red line should do so, but the command i tried bugged the script. i added wb. and wblog.

[Doc.AElstein]

Hi edwin13387, Good morning
Welcome to the Forum.

It is very difficult to help you with the limited information you have given us.

I can get your code to work. - The line you highlighted in Red does not error as it is written when I run the code. But I do not understand what it is supposed to be doing.

When this line runs_...

Sh.Cells(InvulRij, 2).Resize(, 13) = Sh.Cells(SampleRij, 2).Resize(, 13).Value

_.. the value of InvulRij is always equal to SampleRij. So you could just as well have this line to do the same thing

Sh.Cells(InvulRij, 2).Resize(, 13) = Sh.Cells(InvulRij, 2).Resize(, 13).Value

_.. I am not sure f that is what you want ? You have not given us enough information to help you.

So I must now guess what you want to do. ....copy data from one workbook to an other..??.. --- If, for example , you want to copy values to the first Worksheet in wblog, then this would be the modified code line

wblog.Worksheets.Item(1).Cells(InvulRij, 2).Resize(, 13) = Sh.Cells(SampleRij, 2).Resize(, 13).Value

Note:
_1) Always 1.2.3 -- Workbook then Worksheet then Range
Workbooks(“ “).Worksheets(“ “).Range(“ “)
or
Workbooks(“ “).Worksheets(“ “).Cells( )

This will not work:
Workbooks(“ “).Cells( )


_2) It is always better to always include the .Value. VBA will usually default to this, but not always. So this would be better.

wblog.Worksheets.Item(1).Cells(InvulRij, 2).Resize(, 13).Value = Sh.Cells(SampleRij, 2).Resize(, 13).Value

Hope that may be some help

It is much easier for us to help if you show some sample data to explain what you have and what you want.
http://www.excelforum.com/showthread...42#post4519006
http://www.excelforum.com/forums-rul...ederlands.html

_.....

You need to explain much better what you want

_.....

It would be helpful you took some time ( if you have not already ) to read the Forum rules and suggestions for posting questions,
http://www.excelforum.com/forums-rul...ederlands.html
http://www.excelforum.com/showthread...42#post4519006
http://www.excelforum.com/forums-rules/
http://www.excelforum.com/forum-rule...rum-rules.html

Alan

[edwin13387]

Good afternoon Albert E.,

Thank you for responding, i rushed in to things and neglected to read first...
I will try to make the wrongs wright:

To start with:
I have attached a stripped version of the file (blend log gisteren=yesterdays data/stripped blend log=remove the stripped part and its the daily report), so let's try to explain what it does is used for etc.

It is a daily report from an control room, the 1st 3 slides are different machines which log the tasks.
The 4th slide is for comments, and the 5th is for samples that are in the laboratory.

There are a few code's in the module with the code that doesn't function:

Sub datum vast
Sets the cell with the value of today as hard value.

Sub log gisteren
creates a copy of the log files, so a shift can see what happened the past 24 hours.

Sub VenA
copy’s the data to a database for analyses.

Sub datum vandaag
sets the date back to formula (resets 1st sub).

Sub datapresentatie
sends data from database to a file which is source of a powerpoint.

Sub samplesgister
The sub that doesn’t work; it has to look at the log from yesterday, and sets unfinished tasks back to the log of today.


regarding the solutions you have given with this lack of information:
Rij stands for row, so indicates at what level the code is active.
The samplerow is the source, the invulrow is the destination.
Using you suggestion:
wblog.Worksheets.Item(1).Cells(InvulRij, 2).Resize(, 13) = Sh.Cells(SampleRij, 2).Resize(, 13).Value
actualy almost does it… so I am impressed from the get-go.

It does copy the right data between the files, but pastes them all on the same sheet.
So it doesn't match the sheet name, using simular referances as in the code above it doesn't seem to work (i tryed replacing it by/adding (Sh.Name)).

This might be the part of to much information, but i am not sure so whent for rather to much as to little.

thank you
Edwin

(If you know how this needs to be altered, can you explain as well why the Item(1) solves the main issue?)

[edwin13387]

This is the yellow part... obvious not my best choice (tryed editing it, but got only a blank field. Same happens if i try to previeuw):

Sub samplesgister
The sub that doesn’t work; it has to look at the log from yesterday, and sets unfinished tasks back to the log of today.

[edwin13387]

Hello again,

Just altering it a little made it work!
Thank you, this is the result:

Please Login or Register  to view this content.

[Doc.AElstein]

Hi Edwin

Thanks very much for the detailed Feedback.
Thanks for talking the time to explain in such good detail
Well Done. Clearly you took some effort to do this

Unfortunately I could not look at this further as I cannot get the File blend log gisteren.xlsx to open. . Excel errors on attempting to open it , saying that it has invalid data? )
This is a shame as you explained very well the situation and so it would have been interesting to follow.

_............................

Maybe I can still help a little:

..... can you explain as well why the Item(1) solves the main issue?)

I hope you can translate this to help you understand:

For any Workbook there is a Worksheets Object.
The Worksheets Object , ( sometimes called the Worksheets Collection Object ) contains information ( Properties such as Names ) for the collection of the Worksheets ( Spreadsheets) in the Workbook
Most Objects of the “collection type” organise the members of the same basic Class type of the collection into Items. One Item for each Member. In this case one Item for each Worksheet.

You can see these as follows:
_1) Go to the VB Editor environment such as by
short cut Alt+F11 ) ,
or
DevelopmentTools -- Macros -- WorkOnMacro
DevelopmentToolsWorkOnMacro123.JPG
http://imgur.com/I91Yckx
or
View – macros – Show macros --- WorkOnMacro
ViewWorkOnMacro1234.JPG
http://imgur.com/4jovFhV

_2) Select the code of interest, and start running in debug Mode ( In VB Editor click anywhere in the code and hit continually F8 ). Step through the code until your variable for a Workbook has been assigned ( anywhere after the Set) , and then

highlight any occurrence of that Workbook variable

F8ThenHighlightVariable.JPG
http://imgur.com/L06pu78

_3) Hit Shift+F9 and select to Add ( 3 ) to Watch Window
AddWatch1234.JPG
http://imgur.com/sNJw1mp

_3a) Work through, as shown in the following screenshots, Hitting an appropriate + to show the Worksheets --- Items ---- etc...
_3b) Look for example at Worksheets Item 1.
_3b)(i) Open Workbook by clicking on +
OpenWBWatch.JPG
http://imgur.com/SPtRH94

_3b(ii) Scroll down to Worksheets
WorksheetsWatchWindow.JPG
http://imgur.com/29DrdJQ
Hit the + to open the Worksheets Collection Object
and
_3b(iii) scroll down to items. Hit + on , for example , Item1 to open that Instance or the Worksheet Object
FirstWorksheetItem1WatchWindow.JPG
http://imgur.com/pH0xOWF

_3b(iv) You can now see for example Properties such as the Item Number ( Index ) and the Name
ItemNumberNameWartchWindow.JPG
http://imgur.com/c1EmfGB

_........................

Different Collection Objects organise the order of the constituent Items in different ways.
For the Worksheets Collection Object it is in the order that you see the Tabs counting from the left
WorksheetsIndex(ItemNumber).jpg
http://imgur.com/8ZtSmjt

_..........

Referring to a particular Worksheet
We may refer to a Worksheet Item in a code in a number of ways, for example, by its Item Number ( Index ) or Its item Name

This is the correct full syntax to refer by Index or Name

__ Worksheets.Item(1) ‘ Refer by Item Index to first Worksheet
__ Worksheets.Item(“Nieuwe blender") ‘ Refer by Name

You can also use a variable, - but it is important to Declare the variable appropriately in the correct type

‘ Refer by Item Index by variable
Dim Var As Long
_Let Var = 1
__ Worksheets.Item(Var)

‘ Refer by Item Name by variable
Dim Var As String
_ Let Var = “Nieuwe blender"
__ Worksheets.Item(Var)

_.....

There is also a “shorthand” way to do the referring

__ Worksheets(1) ‘ “shorthand “ Refer by Item Index
__ Worksheets.( “Nieuwe blender") ‘ “shorthand “ Refer by Item Name
__ Worksheets.(Var) ‘ “shorthand “ Refer by variable. Make Sure Variable is dimensioned correctly : for - item Number ( Number variable such as Long, Integer etc... ) - or Item Name ( String )



Hope that is some help.
( Shame I could not open your File "blend log gisteren.xlsx" )


Alan
Aka

Good afternoon Albert E.,.....

Albert Einstein ( oh no ! – he found my secret out – I was hiding out of shame for helping inventing of the atom Bomb )

[edwin13387]

Good afternoon, I’ll stop referring to your actual name, let’s hope it blows over.

It clears it up, with a giant load of pictures. Talking about being thorough in explaining yourself!
Just to make sure; in the old situation it worked in one workbook.
But bij adding the extra workbook as destination, the sheet had to be specificly targeted as well.

Using here just the (sh) code isn't enough, it has to be told to be looking at a worksheet.

Thank you once more,

Edwin

[Doc.AElstein]

Hi Edwin

.....
Thank you once more,...

Your Welcome.
A.E.