Data to Transfer in Sheet2

**RJ1969** · 03-22-2023, 10:01 PM

Hello,

I need a vba code that will transfer data from Sheet1 to Sheet2 that has the data ?yes?. I just don?t know if it will be possible.
Here is my scenario?.

From Sheet1, I will be entering the item in A3:A25 and in H where I will put yes or no.

I will probably be needing a macro button that will transfer the data that has ?yes? on in Sheet2 from A1:I1048576.

Then, the next day it will be a new items. So I will clear the Sheet1 to start with a new item A3:A25. Once all the new item entered then I will click the macro button to transfer with ?yes? only in Sheet2 where it should go to the last entry. All the entry that was transferred in Sheet2 will stay remain and new entry should go in the last entry previous the transfer.

Thank you in advance.

**beyond Excel** · 03-22-2023, 11:25 PM

Hi, RJ1969. Try with:

PHP Code:


Sub Macro8() 
Dim a, i& 
With Sheets("Sheet1") 
  With .Range("I3", .Cells(Rows.Count, "I").End(xlUp)) 
    a = .Worksheet.Evaluate(Replace("Transpose(If(| = ""YES"", Row(|)))", "|", .Address)) 
    a = Filter(a, False, False) 
    If UBound(a) < 0 Then Exit Sub 
  End With 
  For i = 0 To UBound(a): a(i) = Replace("A|:I|", "|", a(i)): Next 
  .Range(Join(a, ",")).Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2) 
End With 
End Sub

**sintek** · 03-23-2023, 04:08 AM

Another Option

Please Login or Register  to view this content.

**RJ1969** · 03-23-2023, 12:02 PM

Thank you both!!!

**RJ1969** · 03-23-2023, 02:42 PM

Hi beyondExcel,

Instead of A moved to B. My item was moved to B column.

Thanks

**RJ1969** · 03-23-2023, 05:12 PM

Here is the worksheet I have revised. Can you fix it please.
I added a Sheet2 and adjust the columns. Both "yes" should go to Sheet3.

Sheet1 macro 8 and Sheet2 is macro9.

Can you please add a comment in the vba code what need to change for the future reference. Just in case if I need to adjust.

Thank you in advance!

**beyond Excel** · 03-23-2023, 06:45 PM

It is enough that you use a single macro.

I've changed the name of the sheet receiving the copy to 'Destination':

Please Login or Register  to view this content.

**RJ1969** · 03-23-2023, 07:51 PM

Hello

Thank you.... I will try it right now.

Let you know.

**sintek** · 03-24-2023, 07:11 AM

Please Login or Register  to view this content.

OR

Please Login or Register  to view this content.

**RJ1969** · 03-25-2023, 09:11 AM

BeyondExcel,

I have a conditional format or other format settings like the color font, color cells in an "activesheet" but when I transfer to "Destination" it also copied the format what's on the activesheet. In the "Destination" I just want the value to be copied. The "destination" worksheet has its own format like column size, font size and so on.

Thank you

Please Login or Register  to view this content.

**sintek** · 03-25-2023, 09:44 AM

Just chopped liver...I guess I am...

**beyond Excel** · 03-25-2023, 11:02 AM

Originally Posted by RJ1969

BeyondExcel,

I have a conditional format or other format settings like the color font, color cells in an "activesheet" but when I transfer to "Destination" it also copied the format what's on the activesheet. In the "Destination" I just want the value to be copied. The "destination" worksheet has its own format like column size, font size and so on.

Thank you

Let's go with another version:

Please Login or Register  to view this content.

**RJ1969** · 03-25-2023, 10:12 PM

BeyondExcel

Thank you. It did work.

**RJ1969** · 03-25-2023, 10:16 PM

Sintek
I want to say "Thank you" to you too. I did try yours as well.

**RJ1969** · 03-27-2023, 01:37 PM

BeyondExcel,

What do i need to change for "Destination" range. I want to start in B4 instead of B2.

on this code? I tried to change (xlUp)(2) to (xlUp)(4) but it skip 1 row.

Please Login or Register  to view this content.

Thank you.

Please Login or Register  to view this content.

**beyond Excel** · 03-27-2023, 02:31 PM

Originally Posted by RJ1969

BeyondExcel, What do i need to change for "Destination" range.
I want to start in B4 instead of B2. on this code?

Nothing... You don't have to change anything!

**RJ1969** · 03-27-2023, 03:16 PM

Oh ok.
When I click the button from sheet1 to “destination” it started in row2. I wanted to start in row4 so that i can put something on top of row4.

Thank you

**sintek** · 03-27-2023, 04:03 PM

I want to start in B4 instead of B2.

Change red snippet below to

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**beyond Excel** · 03-27-2023, 04:26 PM

Originally Posted by RJ1969

Oh ok.
When I click the button from sheet1 to “destination” it started in row2. I wanted to start in row4 so that i can put something on top of row4.

Who defines where the data begins is the title row of the data range (in this case in the Destination sheet).

Make sure the titles are present on the sheet then.

**RJ1969** · 03-29-2023, 12:19 PM

Good morning ExcelBeyond

My worksheets are protected cells so I added a code to unlock and lock the worksheet.

The code you provided will work with several sheets (Sheet1 and Sheet2) to move the data in the “Destination” sheet.

Adding the unlock/lock code, I know that Sheet1 should be replace in order to work in accordance to the code you provided. Should I add sheet2 to it?

i.e. Sheets("Sheet1,Sheet2").Unprotect?

To unlock the worksheet is code 1234.

Please advised.

Thank you

Please Login or Register  to view this content.

**RJ1969** · 03-30-2023, 11:47 AM

Hi

I am getting an error '1004' when I click the button in Sheet1 and Sheet2.

Can you help me fix it.

Thank you

Data to Transfer in Sheet2

LinkBack

Thread Tools

Rate This Thread

Display

Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Re: Data to Transfer in Sheet2

Thread Information

Users Browsing this Thread

Similar Threads

VBA code to Transfer specific data from sheet1 to Sheet2

Can Excel transfer data From sheet1 to Sheet2?

Transfer Data from Sheet1 to Sheet2

auto transfer data to sheet2

I want to transfer data from SHEET1 to SHEET2

Transfer data from sheet1 to sheet2 and sheet3

Transfer data from sheet1 to sheet2

Bookmarks

Bookmarks

Posting Permissions