How to Copy data from one sheet to another

J

jeevaaustin

New Member
Joined
Jun 29, 2013
Messages
4
Hello Guru's,

I am new to this form and VB macro i need help ..

I get a file where i have some data , its as below if the total dr is not equal to cr then i want the data to be copied to a new sheet ...how can i create a macro for this ?

[TABLE="width: 448"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Line[/TD]
[TD="class: xl63, width: 64"]Account[/TD]
[TD="class: xl63, width: 64"]Desc[/TD]
[TD="class: xl63, width: 64"]dr[/TD]
[TD="class: xl63, width: 64"]cr[/TD]
[TD="class: xl63, width: 64"]dr[/TD]
[TD="class: xl63, width: 64"]cr[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]123[/TD]
[TD="class: xl63"]test1[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]345[/TD]
[TD="class: xl63"]test2[/TD]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]14[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]total[/TD]
[TD="class: xl63, align: right"]26[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]Line[/TD]
[TD="class: xl63"]Account[/TD]
[TD="class: xl63"]Desc[/TD]
[TD="class: xl63"]dr[/TD]
[TD="class: xl63"]cr[/TD]
[TD="class: xl63"]dr[/TD]
[TD="class: xl63"]cr[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]123[/TD]
[TD="class: xl63"]test1[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]345[/TD]
[TD="class: xl63"]test2[/TD]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]14[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]9393[/TD]
[TD="class: xl63"]test3[/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]total[/TD]
[TD="class: xl63, align: right"]27[/TD]
[TD="class: xl63, align: right"]14[/TD]
[/TR]
[TR]
[TD="class: xl63"]Line[/TD]
[TD="class: xl63"]Account[/TD]
[TD="class: xl63"]Desc[/TD]
[TD="class: xl63"]dr[/TD]
[TD="class: xl63"]cr[/TD]
[TD="class: xl63"]dr[/TD]
[TD="class: xl63"]cr[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]345[/TD]
[TD="class: xl63"]test8[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]14[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]234[/TD]
[TD="class: xl63"]test1[/TD]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]14[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]total[/TD]
[TD="class: xl63, align: right"]14[/TD]
[TD="class: xl63, align: right"]14[/TD]
[/TR]
</tbody>[/TABLE]


result i am looking for :
[TABLE="width: 448"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Line[/TD]
[TD="class: xl65, width: 64"]Account[/TD]
[TD="class: xl65, width: 64"]Desc[/TD]
[TD="class: xl65, width: 64"]dr[/TD]
[TD="class: xl65, width: 64"]cr[/TD]
[TD="class: xl65, width: 64"]dr[/TD]
[TD="class: xl65, width: 64"]cr[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]123[/TD]
[TD="class: xl65"]test1[/TD]
[TD="class: xl65, align: right"]10[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]12[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]345[/TD]
[TD="class: xl65"]test2[/TD]
[TD="class: xl65, align: right"]12[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]14[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]total[/TD]
[TD="class: xl65, align: right"]26[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"]Line[/TD]
[TD="class: xl65"]Account[/TD]
[TD="class: xl65"]Desc[/TD]
[TD="class: xl65"]dr[/TD]
[TD="class: xl65"]cr[/TD]
[TD="class: xl65"]dr[/TD]
[TD="class: xl65"]cr[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]123[/TD]
[TD="class: xl65"]test1[/TD]
[TD="class: xl65, align: right"]10[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]12[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]345[/TD]
[TD="class: xl65"]test2[/TD]
[TD="class: xl65, align: right"]12[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]14[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65, align: right"]9393[/TD]
[TD="class: xl65"]test3[/TD]
[TD="class: xl65, align: right"]15[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]15[/TD]
[TD="class: xl65, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]total[/TD]
[TD="class: xl65, align: right"]27[/TD]
[TD="class: xl65, align: right"]14[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Code: 
Sub rangecopy()
With Sheets(1)
  LR = .Cells(.Rows.Count, "E").End(xlUp).Row
  drow = 1
  r1 = 2
  Application.ScreenUpdating = False
  For j = 1 To LR
    If .Range("A" & j) = "Line" Then
      r1 = j
    ElseIf .Range("E" & j) = "total" Then
      r2 = j
      If .Range("F" & j) <> .Range("G" & j) Then
        .Range("A" & r1 & ":G" & r2).Copy Sheets(2).Range("A" & drow)
        drow = drow + r2 - r1 + 1
      End If
    End If
  Next
End With
Application.ScreenUpdating = True

End Sub
 
Upvote 0
patel45 said:
Code: 
Sub rangecopy()
With Sheets(1)
  LR = .Cells(.Rows.Count, "E").End(xlUp).Row
  drow = 1
  r1 = 2
  Application.ScreenUpdating = False
  For j = 1 To LR
    If .Range("A" & j) = "Line" Then
      r1 = j
    ElseIf .Range("E" & j) = "total" Then
      r2 = j
      If .Range("F" & j) <> .Range("G" & j) Then
        .Range("A" & r1 & ":G" & r2).Copy Sheets(2).Range("A" & drow)
        drow = drow + r2 - r1 + 1
      End If
    End If
  Next
End With
Application.ScreenUpdating = True

End Sub
Click to expand...


Thanks Patel45 ..it worked ..
 
Upvote 0
Hi, jeevaaustin,

although this has been solved I like to show a different approach basicly based on the code patel45 posted (which may seem much more complicated as it uses constants opposite to hard coded ranges). Another difference is that I compsre the criteria in uppercase:

Code: 
Sub MR_Excel_711476()

Dim lngStart          As Long                 'StartRow for a subset of data
Dim lngEnd            As Long                 'EndRow for this subset
Dim lngCounter        As Long                 'counter to loop through the rows
Dim lngCopyRow        As Long                 'first free row on target sheet

Const cstrSHEET_FROM  As String = "Sheet1"    'sheet from which to copy
Const cstrSHEET_To    As String = "Sheet2"    'target sheet

Const cstrCOL_LINE    As String = "A"         'column in which the new subset begins
Const cstrCOL_TOTAL   As String = "E"         'column in which the subset ends

Const cstrCOL_CR      As String = "F"         'first column with data to compare
Const cstrCOL_DR      As String = "G"         'second column with data to compare

Const cstrSTART       As String = "Line"      'item which will define start of subset
Const cstrEND         As String = "total"     'item which will define end of subset

Application.ScreenUpdating = False

With Worksheets(cstrSHEET_FROM)
  For lngCounter = 1 To .Cells(.Rows.Count, cstrCOL_TOTAL).End(xlUp).Row
    If UCase(.Range(cstrCOL_LINE & lngCounter)) = UCase(cstrSTART) Then
      lngStart = lngCounter
    ElseIf UCase(.Range(cstrCOL_TOTAL & lngCounter)) = UCase(cstrEND) Then
      lngEnd = lngCounter
      If .Range(cstrCOL_CR & lngCounter) <> .Range(cstrCOL_DR & lngCounter) Then
        lngCopyRow = Worksheets(cstrSHEET_To).Cells(.Rows.Count, cstrCOL_TOTAL).End(xlUp).Row + 1
        Worksheets(cstrSHEET_To).Range(cstrCOL_LINE & lngCopyRow & ":" & cstrCOL_DR & lngCopyRow + lngEnd - lngStart + 1).Value = _
            .Range(cstrCOL_LINE & lngStart & ":" & cstrCOL_DR & lngEnd - lngStart + 1).Value
      End If
    End If
  Next lngCounter
End With

Application.ScreenUpdating = True

End Sub
Ciao,
Holger
 
Upvote 0
HaHoBe said:
Hi, jeevaaustin,

although this has been solved I like to show a different approach basicly based on the code patel45 posted (which may seem much more complicated as it uses constants opposite to hard coded ranges). Another difference is that I compsre the criteria in uppercase:

Code: 
Sub MR_Excel_711476()

Dim lngStart          As Long                 'StartRow for a subset of data
Dim lngEnd            As Long                 'EndRow for this subset
Dim lngCounter        As Long                 'counter to loop through the rows
Dim lngCopyRow        As Long                 'first free row on target sheet

Const cstrSHEET_FROM  As String = "Sheet1"    'sheet from which to copy
Const cstrSHEET_To    As String = "Sheet2"    'target sheet

Const cstrCOL_LINE    As String = "A"         'column in which the new subset begins
Const cstrCOL_TOTAL   As String = "E"         'column in which the subset ends

Const cstrCOL_CR      As String = "F"         'first column with data to compare
Const cstrCOL_DR      As String = "G"         'second column with data to compare

Const cstrSTART       As String = "Line"      'item which will define start of subset
Const cstrEND         As String = "total"     'item which will define end of subset

Application.ScreenUpdating = False

With Worksheets(cstrSHEET_FROM)
  For lngCounter = 1 To .Cells(.Rows.Count, cstrCOL_TOTAL).End(xlUp).Row
    If UCase(.Range(cstrCOL_LINE & lngCounter)) = UCase(cstrSTART) Then
      lngStart = lngCounter
    ElseIf UCase(.Range(cstrCOL_TOTAL & lngCounter)) = UCase(cstrEND) Then
      lngEnd = lngCounter
      If .Range(cstrCOL_CR & lngCounter) <> .Range(cstrCOL_DR & lngCounter) Then
        lngCopyRow = Worksheets(cstrSHEET_To).Cells(.Rows.Count, cstrCOL_TOTAL).End(xlUp).Row + 1
        Worksheets(cstrSHEET_To).Range(cstrCOL_LINE & lngCopyRow & ":" & cstrCOL_DR & lngCopyRow + lngEnd - lngStart + 1).Value = _
            .Range(cstrCOL_LINE & lngStart & ":" & cstrCOL_DR & lngEnd - lngStart + 1).Value
      End If
    End If
  Next lngCounter
End With

Application.ScreenUpdating = True

End Sub
Ciao,
Holger
Click to expand...

Thanks Holger...
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Populate summary by sorting data for customer for each sheet alone based on dates
Replies
4
Views
168
KalilMe
K
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
182
Pbobas
P
K
  • Solved
Combine Tables (without Total Row) into Master Tables (With Total Sum Row) based on specific sheet Like 1,2,3 to 24
Replies
2
Views
532
Kalim Shaikh
K
V
  • Solved
Creating a new filtered list from a dynamically created list based on previous numbers
Replies
6
Views
83
DanteAmor
DanteAmor
W
  • Question Question
I have been gone a while. pulling data from a C&E document. Cause and Effect
Replies
8
Views
311
wylothar
W

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

Share this page

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
Back
Top