Transfer data only one workbook to another
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Transfer data only one workbook to another
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
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.
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer data only one workbook to another
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!
Make sure that the source workbook is open, then open the data transfer workbook.
Best wishes for 2022!
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data only one workbook to another
That's fantastic Hans
Thanks a lot for quick reply.
Thanks a lot for quick reply.
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data only one workbook to another
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 :)
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.
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer data only one workbook to another
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.
2. See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data only one workbook to another
Thanks a lot again Hans
You are the star
You are the star
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data only one workbook to another
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
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
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer data only one workbook to another
You can increase the number 500 in the TransferSheet1 macro. The SUMIF formulas ignore empty cells.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data only one workbook to another
Oh yeah.
Thanks for that Hans.
Thanks for that Hans.
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data only one workbook to another
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
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.
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer data only one workbook to another
Here you go.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data only one workbook to another
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
Total comments works fine but when I click macro,, comments are not transferring. Am I doing some thing wrong?
Thanks again
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer data only one workbook to another
You only wanted comments if column E is empty...
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data only one workbook to another
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
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.
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer data only one workbook to another
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:
What exactly is the problem?
This is the result of the macro:
What exactly is the problem?
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data only one workbook to another
This is what exactly I am looking for.
For some reason macro does not work for.
Let me double check it.
Thanks Hans
For some reason macro does not work for.
Let me double check it.
Thanks Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data only one workbook to another
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
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
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer data only one workbook to another
See the attached workbook.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data only one workbook to another
Fantastic
Thanks a ton Hans
Thanks a ton Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data only one workbook to another
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.
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.