When There Is a Value In a Cell, Copy The Row to Another Sheet

Playboy

New Member
Joined
May 20, 2018
Messages
5
Hi everyone,

This is my first post here. I`m a n00b in excel macros and been working on this problem the last few days. It`s been really hard for me and I tried many example codes from different places but they all gave errors and didn`t do what I need.

I just want this;
When there is a value other than 0 in F3:F34
I want that B3:J3 to be copied and pasted with the same format, colors and all in another sheet on a specific table or on a cell
But when there another new change there other than going back to 0, it should copy and paste it again in a new row in that other sheet.

So basically I`m looking for some kind of log of the entire row when there is a value more than zero in a cell

Anyone can help me please?
I feel like I`ll be pretty active in your forum as I`m just starting on the macros and It`s amazing how much you can accomplish with these.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Playboy

New Member
Joined
May 20, 2018
Messages
5
Sorry. I couldn`t find the edit option for my thread so I~m writing it in a reply.

I also need the pasted data to have date/time of the change in a new sheet.
 

Playboy

New Member
Joined
May 20, 2018
Messages
5
Value Change In a Cell, Will Be Copied As The Row In Another Sheet

Hi everyone,

This is my first post here.

I just want this;


When there is a value other than 0 in F3:F34
I want that specific row to be copied and pasted with the same format, colors and all in another sheet on a specific table or on a cell with a date&time stamp
But when there`s another new change there other than going back to 0, it should copy and paste it again in a new row in that other sheet again with a date&time stamp.


So basically I`m looking for some kind of log of the entire row when there is a value more than zero in a cell.


I use this sheet for my trades and I just want each change to be logged with a date&time stamp when I complete a buy/sell order and it gives me the profit value.


I`m a n00b in excel macros and been working on this problem the last few days. It`s been really hard for me and I tried many example codes from different places but they all gave errors and didn`t do what I need.


Anyone can help me please?
I feel like I`ll be pretty active in your forum as I`m just starting on the macros and It`s amazing how much you can accomplish with these.

PS: I couldn`t find the delete option for my old thread. So Please remove https://www.mrexcel.com/forum/excel...-there-value-cell-copy-row-another-sheet.html because I cleaned it up and posted here.
 

Nishant94

Well-known Member
Joined
May 8, 2015
Messages
512
Office Version
  1. 365
Platform
  1. Windows
Re: Value Change In a Cell, Will Be Copied As The Row In Another Sheet

Is this what you want?


Code:
Sub Extract_Summary()


Dim x As Range
Dim SheetAdd As Worksheet
Dim CurrentSheet As Worksheet
Dim Sum_Sheet As Worksheet
Dim objTable As ListObject
Dim TableRng As Range


Set CurrentSheet = ActiveSheet


With ThisWorkbook
    
    If Information.IsError(Worksheets("Summary").Index) Then
        .Worksheets.Add after:=ActiveSheet
        .ActiveSheet.Name = "Summary"
    Else
        Worksheets("Summary").Activate
        Set Sum_Sheet = ActiveSheet
    End If
    Sum_Sheet.Cells.Clear
    
End With


CurrentSheet.Activate


For Each x In Range("F3:F34")


    If x.Value <> 0 Then
    
        x.EntireRow.Copy
        Sum_Sheet.Cells(Sum_Sheet.Cells(Rows.Count, 6).End(xlUp).Row + 1, 1).Insert
        
    End If


Next x
Set TableRng = Sum_Sheet.Cells(Sum_Sheet.Cells(Rows.Count, 6).End(xlUp).Row, 6).CurrentRegion


Set objTable = Sum_Sheet.ListObjects.Add(xlSrcRange, TableRng, xlYes)


End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,857
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Re: Value Change In a Cell, Will Be Copied As The Row In Another Sheet

Welcome to the MrExcel board!

I have merged your threads. Please read #12 of the Forum Rules and 7. of the Forum Use Guidelines
 

Playboy

New Member
Joined
May 20, 2018
Messages
5
Re: Value Change In a Cell, Will Be Copied As The Row In Another Sheet

Is this what you want?


Code:
Sub Extract_Summary()


Dim x As Range
Dim SheetAdd As Worksheet
Dim CurrentSheet As Worksheet
Dim Sum_Sheet As Worksheet
Dim objTable As ListObject
Dim TableRng As Range


Set CurrentSheet = ActiveSheet


With ThisWorkbook
    
    If Information.IsError(Worksheets("Summary").Index) Then
        .Worksheets.Add after:=ActiveSheet
        .ActiveSheet.Name = "Summary"
    Else
        Worksheets("Summary").Activate
        Set Sum_Sheet = ActiveSheet
    End If
    Sum_Sheet.Cells.Clear
    
End With


CurrentSheet.Activate


For Each x In Range("F3:F34")


    If x.Value <> 0 Then
    
        x.EntireRow.Copy
        Sum_Sheet.Cells(Sum_Sheet.Cells(Rows.Count, 6).End(xlUp).Row + 1, 1).Insert
        
    End If


Next x
Set TableRng = Sum_Sheet.Cells(Sum_Sheet.Cells(Rows.Count, 6).End(xlUp).Row, 6).CurrentRegion


Set objTable = Sum_Sheet.ListObjects.Add(xlSrcRange, TableRng, xlYes)


End Sub

Hi,

Could you please explain me this code? Like what should I customize there? Which sheet it logs the rows in? and where does it start with?
 

Forum statistics

Threads
1,148,293
Messages
5,745,931
Members
423,985
Latest member
sayed manzar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top