Transfer data only one workbook to another

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Transfer data only one workbook to another

Post by jimpatel1993 »

Hi,
I hope everyone had very good Christmas and advance new year ☺
Special Christmas and new year wish to Hans ☺
Thanks a lot for looking at my post.
I got stuck with some scenario and any help will be much appreciated.
I have 2 workbook and I would like to transfer data from source to destination with some formatting please.

Source workbook:
Source 1 sheet: This sheet will be updated monthly and currently it is updated for the month of Nov
Source 2 sheet: This sheet will be updated once a week

Scenario:
From Source 1 sheet I would like to transfer to Destination sheet (Column N row 12 to Column N row 15) please
From Source 2 sheet, depending upon “Date”, I would like to transfer to Destination sheet please. This will be updated week, so from jan-21 till Dec-21 it will be updated according to source 2 sheet data please.

Any idea please. This help will be much much appreciated.

Thanks a lot again
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfer data only one workbook to another

Post by HansV »

You can use formulas for the data from the Source 2 sheet. For the data from Source 1, a macro is required.
Make sure that the source workbook is open, then open the data transfer workbook.

Best wishes for 2022!

Data transfer.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer data only one workbook to another

Post by jimpatel1993 »

That's fantastic Hans
Thanks a lot for quick reply.

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer data only one workbook to another

Post by jimpatel1993 »

Hi Hans,
Happy New Year :)

Sorry to reopen this post.
Thanks for the macro and formula.
I have a doubt in the formula please.

1. Is there anyway to tweek the formula for following condition as well please?

That is if column E in the source workbook is either "Criteria1" or "Criteria2" then dont count the number of rows with respect to date.
If not (can be anything else) count the number of rows with respect to date please?
I have given the example result in the "Data transfer" workbook for your reference.

2. Secondly, do you know how to add comments in the cell automatically please?
i.e. When the number of rows is transfered to the "Data Transfer" sheet, can we add comments with respect to "department" column in B in the source workbook please?
Really thanks and this help will be much much appreciated :)

Thanks a lot Hans :)
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfer data only one workbook to another

Post by HansV »

1. Rows with a text value will automatically be excluded. You don't have to change the formulas for that.
2. See the attached version.

Data transfer (1) (1).xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer data only one workbook to another

Post by jimpatel1993 »

Thanks a lot again Hans
You are the star

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer data only one workbook to another

Post by jimpatel1993 »

One quick question Hans,
How to extend the formula for empty cells as well please. That is instead of 500 rows, I might need to set up 2000 but not necessarily all the rows will have value.
That is source workbook might have data upto 700 rows.
Any idea please

Thanks again

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfer data only one workbook to another

Post by HansV »

You can increase the number 500 in the TransferSheet1 macro. The SUMIF formulas ignore empty cells.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer data only one workbook to another

Post by jimpatel1993 »

Oh yeah.
Thanks for that Hans.

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer data only one workbook to another

Post by jimpatel1993 »

Hi Hans,

Sorry for reopening this post. Firstly, thanks for the code and its works brilliant. You the star :)

Secondly, i am having another condition and i dont have clue of what to do. Any idea will be appreciated.

I have attached source and destination workbook. Same logic like what you done before but this time i wanted to transfer comments only if column E in the source workbook is empty and give total number of comments in the row 25 in the "Data transfer" sheet please.

Any idea please?

Thanks a lot again
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfer data only one workbook to another

Post by HansV »

Here you go.

Data transfer (1) (1) (1) (1).xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer data only one workbook to another

Post by jimpatel1993 »

Thanks a ton Hans
Total comments works fine but when I click macro,, comments are not transferring. Am I doing some thing wrong?
Thanks again

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfer data only one workbook to another

Post by HansV »

You only wanted comments if column E is empty...
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer data only one workbook to another

Post by jimpatel1993 »

Sorry for my poor explanation
I have attached now with highlighted in red for the logic.
Sorry for my poor explanation.
Hope you will forgive me
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfer data only one workbook to another

Post by HansV »

In the source sheet, you have highlighted two rows for James in February and one for Ander in December.
This is the result of the macro:

S1050.png

What exactly is the problem?
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer data only one workbook to another

Post by jimpatel1993 »

This is what exactly I am looking for.
For some reason macro does not work for.
Let me double check it.
Thanks Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer data only one workbook to another

Post by jimpatel1993 »

Wow Hans
That's great. Sorry it was my mistake. I got confused myself for some reason. Haha
Unbelievable work Hans
Thanks again.
One final question, in the total comments row (25) along with number of comments number is it possible to show those comments as well please.
Thanks a lot genius

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfer data only one workbook to another

Post by HansV »

See the attached workbook.

Data transfer updated.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer data only one workbook to another

Post by jimpatel1993 »

Fantastic
Thanks a ton Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer data only one workbook to another

Post by jimpatel1993 »

Hans,

Thanks a lot for your outstanding work. I am in my final stage and what if i need to split up the comments in two.
That is in row 24, i just wanted to look at total number of comments for row 22 and 23 and row 25 is normal like comments from row 20 till row 23.

Sorry Hans for more questions.
You do not have the required permissions to view the files attached to this post.