I have been extracting data from a number of workbooks and in this case I need B4:B34 from sheets 4 and 6 on external workbooks.
What I need is the filename in col A and the extracted data in column B, so 2 lots of 32 cells. First sheet would then occupy A1:B64. Next sheet would take A65:B128 and so on.
This code works fine but only extracts from sheet 4 on each file.
Can anyone see the flaw(S)? Thanks.
Please Login or Register to view this content.
Hi, ids,
In your code to assign the very same destiantion range to both ranges instead of setting the second after the paste or including the offset for the first range withFirst sheet would then occupy A1:B64. Next sheet would take A65:B128
And if you copy justPlease Login or Register to view this content.
Iīm missing the part likePlease Login or Register to view this content.
either before or after that. Again: make sure youīre not assigning the same ranges to the destination ranges.Please Login or Register to view this content.
Ciao,
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
Thanks for coming back to me there.
I have changed the destination ranges as you suggest.
Also, adjusted the 2nd destination range.Please Login or Register to view this content.
This is good because I now get all the info that I need, but the filename only appears in the first 30 rows.
Having said that and seen the results, I am now think that it would be better to have rows that do not contain any data supressed. I think this means a rethink of all the code?
Please ignore last post - back on track again.
Stepping through the program using F8, Filename 1 pastes to A2-A31 then sheet 4 data is pasted into B2-B31, then sheet 6 is pasted into B32-B61. Filename 2 pastes to A32-A61 and the next sheet 4 overwrites the previous data.
Need Filename 1 A2-A61, sheet 4 cells to B2-B31, sheet 6 cells to B32-B61, then
Filename 2 A62-A121, sheet 4 cells to B62-B91, sheet 6 cells to B92-B121 etc., etc.
Cannot see how to increase the offset in column A with there being a knock on effect.
Can anyone see how this could be modified?
Current code:
Please Login or Register to view this content.
Hi, ids,
are the least of the lines which would need to be altered (no sheet6 to be copied when I glimpsed through the code).Please Login or Register to view this content.
Ciao,
Holger
Hi, ids,
regarding different Columns you could use code like
Regarding the rows where to copy you should comment outPlease Login or Register to view this content.
in order not to go down the rows.Please Login or Register to view this content.
Ciao,
Holger
Thanks Holger.Originally Posted by HaHoBe
Hi, ids,
are the least of the lines which would need to be altered (no sheet6 to be copied when I glimpsed through the code).Please Login or Register to view this content.
Ciao,
Holger
The problem I think is that I am trying to use 1 variable (sourcerange) to do two different things
Here for pasting filename:
and here for pasting sheet 4 & sheet 6Please Login or Register to view this content.
The sheet 6 paste does occur herePlease Login or Register to view this content.
Please Login or Register to view this content.
Hi, ids,
To be honest: I think you donīt really understand what the code is doing and maybe choose it because you believe it to be fitting for your problem but the code may be simplified due to your requests. Maybe itīs a good idea to put the cursor isnoide the procedure and use F8 to single step through the code in order to view what the code is supposed to do.The problem I think is that I am trying to use 1 variable (sourcerange) to do two different things
The problem about the filename is that you either have to do it every other column in order to get the proper name for each set of data. Or you put it above the data (like it is in my version). And I donīt believe it to be very efficient to create an array with the file names and only after that get on opening the files and work on them.
Ciao,Please Login or Register to view this content.
Holger
Hi Holger, thanks for your continued assistance. I think your assumptions are quite correct
I am stepping through your new code with F8 and the first filename is written into A2. This is then overwritten by the sheet 6 data. Sheet 4 data is then written into A32.
Then the 2nd filename is written into A2, overwriting the first cell of sheet 6 data. Then sheet 4(2nd file) data overwrites the sheet 4 (1st file) data. This cycle is ongoing with further files.
Getting close but all data is being overwritten onto the same footprint at A2 and A32 start locations.
Last edited by ids; 01-09-2014 at 07:47 AM. Reason: wrong file number
Hi, ids,
you would need to correct the number where to start (I used 2 there - it should have been row 1 instead). So the data should get aligned under teh filename .
Ciao,
Holger
OK thanks, I am getting data in adjacent columns for each external sheet now.
For my application, I need to reformat these sections vertically into the first two columns, "filename" & "Data" respectively.
You may try:
Please Login or Register to view this content.
- Please remember to mark threads Solved with Thread Tools link at top of page.
- Please use code tags when posting code: [code]Place your code here[/code]
- Please read Forum Rules
Hi Izandol, I will try this now. Thanks
Perfect! Works exactly as I need - thanks Izandol
You are welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks