Macro to scan current raw and put selected data in next sheet

pragnesh_50 · Nov 30, 2011

HI,

I have data in sheet1 in column A, B, C, D, E, F, G, H, I, J, K, L, M as followes:

Columns as follows:

Date,

BROWSER,

P/S/D/L/

PC class

Nan Code...............

EAN Code

GTG/NGTG ............

IMAGE(Y/N)...........

Shared country.........

Char ID....................contain INDEX MATCH IF FUNCTION Chartype..................

Current Value,...........

New Value...............

I want to put button at column N for every raw and assign macro to that button so that i get following data in next sheet(columns highlited as......) from sheet1. Note i want to scan data raw by raw

Sheet2 columns like which provide output from sheet1 by each raw by pressing button

Date, (value should be copied form sheet1)

Nan code, (value should be copied form sheet1)

GTG/NGTG (value should be copied form sheet1)

IMAGE (Y/N) (value should be copied form sheet1)

Shared country.........(value should be cut form sheet1)

Char ID....................(value should be cut form sheet1)

Char type.................(value should be cut form sheet1).

Current Value,...........(value should be cut form sheet1) New Value...............(value should be cut form sheet1)

same is given as example at following link https://docs.google.com/spreadsheet/...WNNTkFMSDNoTlE

Could any one prepare macro for me?

Regards,

Pragnesh

Luke M · Nov 30, 2011

Looks like your link got shortened. Can you try reposting the link?

pragnesh_50 · Nov 30, 2011

hi,

check https://docs.google.com/spreadsheet/ccc?key=0Apmikxh9sFKcdEpUSjhrNXZTZXFXTWNNTkFMSDNoTlE#gid=0

Luke M · Dec 1, 2011

It looks like the data is just getting copied over in a different order. You could probably record a macro of you copying over the columns in correct order to give you 90% of the solution.

I'm not sure what you need the macro to do when it scans each row...is there some particular criteria you are looking for? If so, you could record a macro of that by using an AutoFilter before you copy & paste.

pragnesh_50 · Dec 2, 2011

I think my problem is quite simple:

Cell A2 of sheet1 should be copied to A2 in sheet2

Cell E2 of sheet1 should be copied to B2 in sheet2

Cell G2 of sheet1 should be Cut to C2 in sheet2

Cell H2 of sheet1 should be Cut to D2 in sheet2

Cell I2 of sheet1 should be Cut to E2 in sheet2

Cell J2 of sheet1 should be Cut to F2 in sheet2 (j2 contain formula in f2 which is not needed in F2 in sheet2 )

Cell K2 of sheet1 should be Cut to G2 in sheet2

Cell L2 of sheet1 should be Cut to H2 in sheet2

Cell M2 of sheet1 should be Cut to I2 in sheet2

Could any one suggest macro? Please

i have current macro as followes but it is not working well:

Sub Macro3()

'

' Macro3 Macro

' Macro recorded 9/30/2011 by patelhi02

'

'

Selection.Copy

ActiveSheet.Next.Select

Range("B2").Select

ActiveSheet.Paste

ActiveSheet.Previous.Select

Range("a2").Select

Application.CutCopyMode = False

Selection.Copy

ActiveSheet.Next.Select

Range("A2").Select

ActiveSheet.Paste

ActiveSheet.Previous.Select

Range("e2").Select

Application.CutCopyMode = False

Selection.Copy

ActiveSheet.Next.Select

Range("B2").Select

ActiveSheet.Paste

ActiveSheet.Previous.Select

Range("G2").Select

Application.CutCopyMode = False

Selection.Copy

ActiveSheet.Next.Select

Range("C2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveSheet.Previous.Select

Range("J2:K2").Select

Application.CutCopyMode = False

Selection.Copy

ActiveSheet.Next.Select

Range("G3").Select

ActiveSheet.Paste

Rows("3:3").Select

Application.CutCopyMode = False

Selection.Insert Shift:=xlDown

ActiveSheet.Previous.Select

ActiveWorkbook.Save

End Sub

Regards,

Pragnesh

Luke M · Dec 2, 2011

Like this?

[pre]

Code:

Sub TransferData()
Dim StartSheet As String
Dim EndSheet As String

&#39;Define sheet names
StartSheet = &#34;Sheet1&#34;
EndSheet = &#34;Sheet2&#34;
Application.ScreenUpdating = False

With Worksheets(StartSheet)
Worksheets(EndSheet).Range(&#34;A2&#34;) = .Range(&#34;A2&#34;).Value
Worksheets(EndSheet).Range(&#34;B2&#34;) = .Range(&#34;E2&#34;).Value
Worksheets(EndSheet).Range(&#34;C2&#34;) = .Range(&#34;G2&#34;).Value
Worksheets(EndSheet).Range(&#34;D2&#34;) = .Range(&#34;H2&#34;).Value
Worksheets(EndSheet).Range(&#34;E2&#34;) = .Range(&#34;I2&#34;).Value
Worksheets(EndSheet).Range(&#34;F2&#34;) = .Range(&#34;J2&#34;).Value
Worksheets(EndSheet).Range(&#34;G2&#34;) = .Range(&#34;K2&#34;).Value
Worksheets(EndSheet).Range(&#34;H2&#34;) = .Range(&#34;L2&#34;).Value
Worksheets(EndSheet).Range(&#34;I2&#34;) = .Range(&#34;M2&#34;).Value
End With
Application.ScreenUpdating = True
End Sub

[/pre]

pragnesh_50 · Dec 2, 2011

HI Luck M

this macro is right for raw 2, it need to scan every raw on by one.

(Note value for C to H should be cut and pasted in next sheet)

Actually i want to put Control Button in every raw of Column J,

I want to press that control button to scan current raw so that selected data transfers to next sheet.

Luke M · Dec 2, 2011

Why make so many buttons??

How about you just select a cell from the row you want to copy, then press a single button/shortcut key?

[pre]

Code:

Sub TransferData()
Dim StartSheet As String
Dim EndSheet As String
Dim iRow As Integer
Dim nRow As Integer
iRow = ActiveCell.Row
&#39;Define sheet names
StartSheet = &#34;Sheet1&#34;
EndSheet = &#34;Sheet2&#34;
Application.ScreenUpdating = False
nRow = Worksheets(EndSheet).Range(&#34;A65536&#34;).End(xlUp).Row + 1

With Worksheets(StartSheet)
Worksheets(EndSheet).Cells(nRow, &#34;A&#34;) = .Cells(iRow, &#34;A&#34;).Value
Worksheets(EndSheet).Cells(nRow, &#34;B&#34;) = .Cells(iRow, &#34;E&#34;).Value
Worksheets(EndSheet).Cells(nRow, &#34;C&#34;) = .Cells(iRow, &#34;G&#34;).Value
Worksheets(EndSheet).Cells(nRow, &#34;D&#34;) = .Cells(iRow, &#34;H&#34;).Value
Worksheets(EndSheet).Cells(nRow, &#34;E&#34;) = .Cells(iRow, &#34;I&#34;).Value
Worksheets(EndSheet).Cells(nRow, &#34;F&#34;) = .Cells(iRow, &#34;J&#34;).Value
Worksheets(EndSheet).Cells(nRow, &#34;G&#34;) = .Cells(iRow, &#34;K&#34;).Value
Worksheets(EndSheet).Cells(nRow, &#34;H&#34;) = .Cells(iRow, &#34;L&#34;).Value
Worksheets(EndSheet).Cells(nRow, &#34;I&#34;) = .Cells(iRow, &#34;M&#34;).Value
End With
Application.ScreenUpdating = True
End Sub

[/pre]

pragnesh_50 · Dec 2, 2011

Hi Luck M

It is good to transfer all data at time.

Here data of column H to M should be removed from sheet1 after running macro rather that it remain there.

Luke M · Dec 2, 2011

Within the with statement then, add line:

[pre]

Code:

.Range(.Cells(i,&#34;H&#34;),.Cells(i,&#34;M&#34;)).ClearContents

[/pre]

pragnesh_50 · Dec 3, 2011

HI Luck M,

I am not able to judge where to put the above line in current macro.

Could u just update the macro.

Luke M · Dec 5, 2011

[pre]

Code:

Sub TransferData()
Dim StartSheet As String
Dim EndSheet As String
Dim iRow As Integer
Dim nRow As Integer
iRow = ActiveCell.Row
&#39;Define sheet names
StartSheet = &#34;Sheet1&#34;
EndSheet = &#34;Sheet2&#34;
Application.ScreenUpdating = False
nRow = Worksheets(EndSheet).Range(&#34;A65536&#34;).End(xlUp).Row + 1

With Worksheets(StartSheet)
Worksheets(EndSheet).Cells(nRow, &#34;A&#34;) = .Cells(iRow, &#34;A&#34;).Value
Worksheets(EndSheet).Cells(nRow, &#34;B&#34;) = .Cells(iRow, &#34;E&#34;).Value
Worksheets(EndSheet).Cells(nRow, &#34;C&#34;) = .Cells(iRow, &#34;G&#34;).Value
Worksheets(EndSheet).Cells(nRow, &#34;D&#34;) = .Cells(iRow, &#34;H&#34;).Value
Worksheets(EndSheet).Cells(nRow, &#34;E&#34;) = .Cells(iRow, &#34;I&#34;).Value
Worksheets(EndSheet).Cells(nRow, &#34;F&#34;) = .Cells(iRow, &#34;J&#34;).Value
Worksheets(EndSheet).Cells(nRow, &#34;G&#34;) = .Cells(iRow, &#34;K&#34;).Value
Worksheets(EndSheet).Cells(nRow, &#34;H&#34;) = .Cells(iRow, &#34;L&#34;).Value
Worksheets(EndSheet).Cells(nRow, &#34;I&#34;) = .Cells(iRow, &#34;M&#34;).Value
.Range(.Cells(i,&#34;H&#34;),.Cells(i,&#34;M&#34;)).ClearContents
End With
Application.ScreenUpdating = True
End Sub

[/pre]

pragnesh_50 · Dec 5, 2011

hi

i got application define or object define error for clear contents statement

regards,

Pragnesh

Macro to scan current raw and put selected data in next sheet

pragnesh_50

Member

Luke M

Excel Ninja

pragnesh_50

Member

Luke M

Excel Ninja

pragnesh_50

Member

Luke M

Excel Ninja

pragnesh_50

Member

Luke M

Excel Ninja

pragnesh_50

Member

Luke M

Excel Ninja

pragnesh_50

Member

Luke M

Excel Ninja

pragnesh_50

Member