Need macro modify for copy range one sheet to another

[nur2544]

Dear all,
Need to modify macro for copy range one sheet to another sheet
Below macro is working fine. But I need some modification
myCopy = "A6,R6,S6,T6,U6,V6,W6,X6,Y6,Z6,AA6,AB6" except this
here i want to copy as range "A6:A1005 & R6:AB1005" , this range will copy one
sheet to another sheet.

Please Login or Register  to view this content.

I also need to skip below logic, this blank cell notification not required in my above macro

Please Login or Register  to view this content.

[patel45]

I's not clear for me where(range) you want paste the selected ranges

[nur2544]

Hi,Patel45
Thanks for response, yes I want to past the selected ranges. Details below
copy range "A6:A1005” & “R6:AB1005” from sheet “i6” (if possible then copy only which having value in between the range and skip blank cell) and past this range(A6:A1005 & R6:AB1005) the another sheet “d1”. And cleared the copied range from sheet “i6”.
Again I will fill up sheet “i6” & copy all this data and past to another sheet(d1) (remeber 2nd time past will be in the blank cell of the sheet "d1" ). This process will continue after press the button “adddata”

[patel45]

Hi,Patel45

copy range "A6:AB1005” & “R6:AB1005” from sheet “i6” (if possible then copy only which having value in between the range and skip blank cell) and past this range(A6:AB1005 & R6:AB1005) the another sheet “d1”.

I understand paste to another sheet “d1”, but where in sheet("d1") ? in A1 ? where I want ?

[nur2544]

Hi,Patel45
Past data in the cell A2 of sheet “d1” , sorry for not mentioning this..

[patel45]

attach a sample file with current and desired sheets

[nur2544]

Hi,Patel45
Pls check the attachment.

[patel45]

you don't want copy range "A6:AB1005” but A6:A1005, correct ?

[nur2544]

Hi,Patel45
i want to copy both range same time first range A6:A1005 and then R6:AB1005.

[patel45]

check your attachment, no range A6:A1005 was pasted in d1, please spend time to give a correct question and correct attachment

[nur2544]

Hi patel45,
I am extremely sorry. i edited my previous post that make you complex
here i want to copy as range "A6:A1005 & R6:AB1005" from sheet "i6" to "d1"

[patel45]

I am extremely sorry, no good attachment, no answer from me

[AB33]

Try this one

Please Login or Register  to view this content.

[nur2544]

Hi AB33,
Thanks for response. you are almost correct.
I have edited below one line of your code [Cells(Rows.Count, 2)], it works perfectly for me

Please Login or Register  to view this content.

now when i press "adddata" button, all data range "A6:A1005" & "R6:AB1005" of sheet "i6" past in the sheet "d1". & it absolutely correct.
after that what i will do, I will fill up again sheet “i6” with the range "A6:A1005" & "R6:AB1005" any other data. And I will press again “adddata” button , in this time data will be past in the blank cell of the sheet “d1” as some cell are occupied with previous data .
Just assume data past at 2nd time will be start from the range“A7:A & R7:AB”. (currently your macro every time while pressing “adddata “button data past in the same range of sheet “d1”).
I don’t want that logic , just add some logic in your macro code so that when 2nd time I press “adddata “ button, the data of sheet “i6” will be past in the sheet “d1” which having blank cell.
I am sorry I might not express my view due to lack of English competence. But I tried …

[AB33]

Nur,
I understand it is difficult for you to explain in words, but as the Chinese said " A picture is worth a million words", So what you can do is to show in a sample book what you wanted. You did it with your first request. You should do the same with the second request. So, one sheet with data before the macro and another sheet with sample after the macro, you should clearly label them, so that we can test the code.

[nur2544]

Hi AB33,
I’m very happy to get response from you. Please remember that my first posted macro is working Fine for me!!
Pleas check the attachment . file “AB_before” I am using it for my daily activity .
need to modify File “AB_after” as well..

[AB33]

Okay! This is my guess. I will explain to you as simple as posssible.
You can add any input, as many as possible.

Please Login or Register  to view this content.

What these two lines do is to look at the next empty cell in column "A". It does not matter where the heading(Your input) may be. For e.g if you have heading from rows 1 to 7, the next empty cell is row 8, so the macro copies starting from row 8.
If you have heading from rows 1- to 15, the next row is row 16, so the macro copies stating from row 16 and so on. The code simple looks for the next empty row. When I looked at your spreadsheet yesterday, my code was copying starting from 1000, instead of row 5. Reason, because you have some data after row 5. I have two solutions for you.
First one
You can add this line in to your code.
d1.Range("A2:AB" & Rows.Count).ClearContents
This line will clear any data(Except the heading which is now in row 1), but if you have input heading from rows 1 to 15, we can change it to
d1.Range("A15:AB" & Rows.Count).ClearContents.
The code then copies in to d1, starting at 16.
The second solution would be to use row numbers. Please see code
It is very flexible, you can easily change where you want to copy to row, which is the NR. This code copies starting from row 8, it does not matter if you have data before or after. The disadvantage is to loops through each row.

Please Login or Register  to view this content.

The third option is
this code clears existing data from row all the way down to last row and then starts to copy in the next empty cell.
But to answer your question, there is not thing wrong with the code you have. What you need to do is clear the data after the heading and the code will copy the data in the next empry row, it does not matter where the next empty row is

Please Login or Register  to view this content.

[nur2544]

Hi AB33,
Thanks for your time. How ever my requirement is not fulfillment. I need to close all chain chat,
it killed my time and as well as yours valuable time also. Extremely Sorry for that.
From today I will continue my previous job With the help of my first posted code. take care..

[AB33]

How your quirement is not fulfillment? The question you have asked is very simple, but some how lost in translation. Do not worry, it is not your fault. It happens all the time.I will try to show you with attachment and you will then comment on it

[AB33]

Nur,
Please see attached. I have Your original data sheet, and have also added two codes. The copied cells on both sheets adddata1 and adddata are shaded with red. If you run one of the codes on sheet i6 and see what you get on sheet d1. What is now missing from my code? What the code does not do? You can insert 100 rows if you wish, but the first code will look in the next empty row. Could you try the codes and tell me what do you want me to add?

[nur2544]

Dear all Excel Macro expert,
!At last i got my solution by myself!
i have already completed the range from "A6:A6,R6:AB6" to "A20:A20,R20:AB20".likewise i will do until A1005 & AB1005
Finally I thanks both “AB33” and “Patel45” brother for wasting their valuable time for me. Take care..

Please Login or Register  to view this content.

[AB33]

Sorry, we could not help more, but glad you have found a solution. Please close this thread as solved

[HaHoBe]

Hi, nur2544,

maybe shorten all your code writing a bit as to me this is a perfect example on how to use a loop:

Please Login or Register  to view this content.

Ciao,
Holger

[nur2544]

Dear HaHoBe,

There is no doubt. You are absolutely brilliant. it is really God gifted talents.
You did outstanding job for me. Excellent macro logic. Very small macro. Fantastic, wonderful
exceptional , No complicity , easy to understand , I appreciate it. I respect you as well as your
Merit. I salute you for keeping cosmic contribution in this forum.. I am totally speechless..
Many thanks…..