copy and pasting values to a closed workbook

[hudson andrew]

Hi all,

Is there a way we can copy past or send data to a closed workbook .

while sending it should only copy certain certain rows and send to closed workbook example:

Open Items
1
2
3
4
Open Item End

i want to send those lines or rows to closed workbook that lines are between Open items and open items end


Unfortunately i cant share the file hence kindly advice please.

[spitfireblue]

Yes and no...
You cannot paste data to a closed workbook... but you can open the closed workbook, paste the data and then close it again.
Here is an example:

Please Login or Register  to view this content.

[jolivanes]

Another possibility

Is something like this a start?

Please Login or Register  to view this content.

[hudson andrew]

Hello mate,

Thanks for your time.

I have tried the above code but it is throwing error message at (lastrow) 5th line . can that be tweeked again please .


And more over I have more than 10 sheets in my source book , so if you can also include that would be helpful .

To be precise I want to copy lines from each sheets till 10 sheets and then paste it in destination file .

Can you please look into it

[jolivanes]

Do you have a space after "Open Item End"? Is the spelling exactly the same?
If "Open Item End" is in cell A14 and somewhere you put "=Len(A14)", what do you get? Should be 13.
So you want to copy only from the left 10 sheets, is that right? Are there always a minimum of 10 sheets or could it be less also?

[hudson andrew]

Do you have a space after "Open Item End"? Is the spelling exactly the same? --- mate this is good not a problem here
If "Open Item End" is in cell A14 and somewhere you put "=Len(A14)", what do you get? Should be 13.
So you want to copy only from the left 10 sheets, is that right? Are there always a minimum of 10 sheets or could it be less also? not 10 but 14 and 14 sheets have a name like below and I wanted to be copied rows from all the below sheets if we have and then cope to destination file

BADALOE
BEGAEXP
CANFACT
FMASIA
FPCHINA
KOSTAM
LINAN
MEXIIGN
MGCAAFT
MONDOOE
PINCHAPARC
PUEBLA
RAIMCV
SAISA
TEPOFRI

I have fix the above problem now the problem encountered at below line

.Range(Cells(firstrow, 1), Cells(lastrow, l)).Copy wb.Sheets("JULY'17").Cells(Rows.Count, 1).End(xlUp).Offset(1) '<---- Change sheet name as required

in above line what should the sheet name is it my source file or destination file .

[jolivanes]

Post #4: "from each sheets till 10 sheets" To me this means 10 sheets only.

Post #6: "from all the below sheets if we have". The "if we have" to me means that it could be any amount.

Let us know what it is.

The 2nd last line in your Post #6 (.Range(Cells(firstrow, 1), etc etc) has a bunch of periods missing.

This part copies from the open workbook

Please Login or Register  to view this content.

and this part pastes into the previously closed and now temporarily open workbook.

Please Login or Register  to view this content.

Re: "I have fix the above problem"
What was the problem?

[hudson andrew]

Hello mate .

I am not able to put the scenario clear my apologies for that . for your better understanding i am attaching you two files (source and destination file).

Now i wanted only those rows between comments "Open Item Details" and "Open Item Details End" to be copied from source file and posted to be destination file . like this i wanted this loop until last sheet leaving control sheet . i mean in every sheet we see this lines hence i wanted to be copied and pasted in destination file .l

i am attaching you the files for your reference and kindly advice hope this will help you to write better code.

thanks again for your help again .

[jolivanes]

So it is the 2nd Sheet till the last Sheet? Right? The first Sheet, "control" is empty.
I assume that you want to paste in Column C in the destination workbook.
In future posts, mention that you have hidden sheets so people don't need to go hunting why errors are thrown up.
Obviously, the code goes into the source workbook.

Please Login or Register  to view this content.

[hudson andrew]

This is brilliant mate , this is what exactly i am looking for .

thank you very much .

another request if you can put some narration between lines that would be helpful for me (this is how i pick some knlowedeg)

thanks for your help .

[jolivanes]

Like this?

Please Login or Register  to view this content.

[hudson andrew]

Hi Jolivanes,

One small advice from you . How do I run above macro in different workbooks rather than the code that is embedded in one workbook . I have more than 20 workbooks that should be running using same macro. i tried setting the macro for all workbooks but did not work .

can you please.

and yes i wanted the comments like above .thanks for the narration .

[jolivanes]

Can you explain in more detail please.

[hudson andrew]

Sure !!! . I have macro embedded in workbook (data). if I run that macro it only runs in that data book and what ever the macro it effects only on that book . now I have same size file similar in nature but data size if different and that file name is Data_1070. so on like that I have 20 work books I cant go head and past the code in each workbook module and run it .correct me if I am wrong

now I am looking for a feature that could really help me with running dynamically in each and every book if the data structure is same .

[jolivanes]

You can put code in each workbook if you want. Not that it is needed if I understand you right.
Do you want data from multiple workbooks copied into one workbook?
Where are these (20) workbooks saved? All in the same folder? Is the workbook where the data will be pasted into also saved in that same folder?

[jolivanes]

All the Workbooks (Destination and Data) need to be saved in the same folder.
The "Destination" Workbook needs to have the code in it.
Open the "Destination" Workbook (all data workbooks need to be closed) and run either macro.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[hudson andrew]

Hello Jolivans,

Thanks for the dynamic idea.

I tried it but it is throwing me the error. can you please check it.

and the error is at below line
firstrow = .Columns(1).Find("Open Item Details", , , 1).Row + 2

[hudson andrew]

I have ensured all my data and destination files are in one folder besides I am run the code from "Detination file" module window .
and still I get the error message " Object variable or with block variable not set"

can you please advice

[jolivanes]

Re your Post #17.
It probably has copied some data before it throws that error. You should be able to figure out which sheet causes that by elimination.
Could be that that line does not exist in a sheet or that there is a spelling mistake or that there is an extra space.
You could write a short piece of code to run through each workbook and all sheets to tell you where (workbook, sheet and row) it found the error.
With a temp folder with files, both codes from Post #16 work for me.

[jolivanes]

You could put this

Please Login or Register  to view this content.

just after

Please Login or Register  to view this content.

and this

Please Login or Register  to view this content.

just before

Please Login or Register  to view this content.

Run your macro and it'll tell you where the problem is.
Make sure to delete it after.

[hudson andrew]

Hello mate : this is amazing . it really worked I found the issue and I removed . this is now working fine as always you are the best !!!

I have one quick question why did you pasted colan in the below line
LetMeKnow: MsgBox ActiveWorkbook.Name & " in sheet " & ActiveSheet.Name
On Error GoTo 0

sorry for to many questions .

and thank you very much for your time .

[jolivanes]

GoTo can be used for branching. In this case just to tell you where the problem lies.
https://docs.microsoft.com/en-us/dot...goto-statement

Good luck