• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy row from one sheet to other sheet

A

AAK

New Member
I want to copy Range (A9:L9) in "Email Data" sheet in Demo workbook
and
paste in last row (i.e. B773 to M773 and date in A773 - here the date will increment from last row date) in "Data" sheet in Devices workbook
and change both the graph in "Graph" sheet to contain last 20 days data in each graph in Devices Workbook

Both the workbook attached for your reference

Demo.xlsm

Devices.xlsx

Any help will be highly appreciated

This should be accomplished by using VBA code by using the "Crash Copy" button in the "Email Data" sheet in Demo workbook
 
You need something like this...

Code: 
Private Sub CommandButton3_Click()
Dim rng As Range, lrow As Range

Application.ScreenUpdating = False

Set rng = ActiveSheet.Range("A9:L9")
Set lrow = Workbooks("Devicess").Sheets("Data").Range("B" & Rows.Count).End(xlUp)(2)
rng.Copy
lrow.PasteSpecial xlPasteValues
lrow.PasteSpecial xlPasteFormats
Application.CutCopyMode = xlCopy
lrow.Offset(, -1).Value = Date

Set lrow = Nothing
Set rng = Nothing

Application.ScreenUpdating = True
End Sub

For chart update either use named range in or same would also be produced with VB.
 
I am getting scripts out of range in the below line -
Code: 
Set lrow = Workbooks("Devicess").Sheets("Data").Range("B" & Rows.Count).End(xlUp)(2)

And for graph i tried something below -
I tried macro but i got the code containing hard-coded range for last 20 days . But i want it range to increment each day to get the new date . Code with macro is below. Can u do the change -
Code: 
Sub GraphMacro()

    Sheets("Graph").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).Values = "=Data!$F$1:$F$21"
    ActiveChart.SeriesCollection(2).Values = "=Data!$L$1:$L$21"
    ActiveChart.SeriesCollection(2).XValues = "=Data!$A$1:$A$21"
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).Values = "=Data!$G$1:$G$21"
    ActiveChart.SeriesCollection(2).Values = "=Data!$M$1:$M$21"
    ActiveChart.SeriesCollection(2).XValues = "=Data!$A$1:$A$21"
End Sub

In the above code , the next day data will be
Data!$G$2:$G$22
Data!$M$2:$M$22
Data!$A$2:$A$22

Thanks in Advance. :)
 
AAK said:
I am getting scripts out of range in the below line -
Code: 
Set lrow = Workbooks("Devicess").Sheets("Data").Range("B" & Rows.Count).End(xlUp)(2)
Click to expand...

Pls check workbook name Devicess or Devices & same needs to be open.


.......

...........In the above code , the next day data will be
Data!$G$2:$G$22
Data!$M$2:$M$22
Data!$A$2:$A$22

Thanks in Advance. :)
Click to expand...

Check with this..

Code: 
Sub GraphMacro()
Dim ws As Worksheet, lr As Long
Set ws = Sheets("Data")
 lr = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Graph").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).Values = _
        ws.Range(ws.Cells(lr - 20, "F"), ws.Cells(lr, "F")) '"=Data!$F$1:$F$21"
    ActiveChart.SeriesCollection(2).Values = _
        ws.Range(ws.Cells(lr - 20, "L"), ws.Cells(lr, "L")) '"=Data!$L$1:$L$21"
    ActiveChart.SeriesCollection(1).XValues = _
        ws.Range(ws.Cells(lr - 20, "A"), ws.Cells(lr, "A")) '"=Data!$A$1:$A$21"
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).Values = _
        ws.Range(ws.Cells(lr - 20, "G"), ws.Cells(lr, "G")) '"=Data!$G$1:$G$21"
    ActiveChart.SeriesCollection(2).Values = _
    ws.Range(ws.Cells(lr - 20, "M"), ws.Cells(lr, "M")) '"=Data!$M$1:$M$21"
    ActiveChart.SeriesCollection(1).XValues = _
    ws.Range(ws.Cells(lr - 20, "A"), ws.Cells(lr, "A")) '"=Data!$A$1:$A$21"
Set ws = Nothing
End Sub

Or merged both codes...

Code: 
Private Sub CommandButton3_Click()

Dim rng As Range, lrow As Range, lr As Long, ws As Worksheet
Application.ScreenUpdating = False

Set rng = ActiveSheet.Range("A9:L9")
Set lrow = Workbooks("Devicess").Sheets("Data").Range("B" & Rows.Count).End(xlUp)(2)
lr = lrow.Row
Set ws = lrow.Parent
rng.Copy
lrow.PasteSpecial xlPasteValues
lrow.PasteSpecial xlPasteFormats
Application.CutCopyMode = xlCopy
lrow.Offset(, -1).Value = Date

Workbooks("Devicess").Sheets("Graph").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).Values = _
        ws.Range(ws.Cells(lr - 20, "F"), ws.Cells(lr, "F")) '"=Data!$F$1:$F$21"
    ActiveChart.SeriesCollection(2).Values = _
        ws.Range(ws.Cells(lr - 20, "L"), ws.Cells(lr, "L")) '"=Data!$L$1:$L$21"
    ActiveChart.SeriesCollection(1).XValues = _
        ws.Range(ws.Cells(lr - 20, "A"), ws.Cells(lr, "A")) '"=Data!$A$1:$A$21"
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).Values = _
        ws.Range(ws.Cells(lr - 20, "G"), ws.Cells(lr, "G")) '"=Data!$G$1:$G$21"
    ActiveChart.SeriesCollection(2).Values = _
    ws.Range(ws.Cells(lr - 20, "M"), ws.Cells(lr, "M")) '"=Data!$M$1:$M$21"
    ActiveChart.SeriesCollection(1).XValues = _
    ws.Range(ws.Cells(lr - 20, "A"), ws.Cells(lr, "A")) '"=Data!$A$1:$A$21"

Set ws = Nothing
Set lrow = Nothing
Set rng = Nothing

Application.ScreenUpdating = True

End Sub
 
You must log in or register to reply here.
Back
Top