You are here:

Excel/Copy datas from Sheet1 to Sheet2 - Row or Range Wise

Advertisement


Question
QUESTION: Got 2 sheets Sheet1 and Sheet2.

Sheet1 is were all the datas are maintained and Sheet2 is were report is generated from Sheet1.

The report is generated based on specific columns from Sheet1 and then pasted to specific columns in Sheet2.

Below is the code which achieves this :

Private Sub CommandButton1_Click()
   Sheets("Sheet1").Columns("A").Copy Sheets("Sheet2").Columns("O")
   Sheets("Sheet1").Columns("B").Copy Sheets("Sheet2").Columns("H")
   Sheets("Sheet1").Columns("C").Copy Sheets("Sheet2").Columns("F")
   Sheets("Sheet1").Columns("D").Copy Sheets("Sheet2").Columns("P")
   Sheets("Sheet1").Columns("E").Copy Sheets("Sheet2").Columns("A")
   Sheets("Sheet1").Columns("F").Copy Sheets("Sheet2").Columns("D")
   Sheets("Sheet1").Columns("G").Copy Sheets("Sheet2").Columns("K")
   Sheets("Sheet1").Columns("H").Copy Sheets("Sheet2").Columns("I")
   Sheets("Sheet1").Columns("I").Copy Sheets("Sheet2").Columns("J")
   Sheets("Sheet1").Columns("J").Copy Sheets("Sheet2").Columns("L")
   Sheets("Sheet1").Columns("K").Copy Sheets("Sheet2").Columns("B")
   Sheets("Sheet1").Columns("L").Copy Sheets("Sheet2").Columns("G")
   Sheets("Sheet1").Columns("M").Copy Sheets("Sheet2").Columns("E")
End Sub

Looking for few additions as below :

1) Whenever the report is generated Column N in Sheet2 should also be updated with number 1.

2) Since the data in Sheet1 is vast, I need a pop-up box asking for row number and performs the above task from the row number specified and paste the data on the next empty row in Sheet2.

3) Need a button on Sheet2 which will run the macro.

ANSWER: Bimmy,

Your copying entire columns between sheets so when you say ask for a row number, do you just want to copy that single row?  

If not, then I have no idea what you want to do.

The code you show is the event macro for a commandbutton.  So if you need a button, just put another commandbutton on sheet2 and put your code in the click event for that button.

Putting a 1 in column N.  If you were doing one row at a time, that might make sense but since I don't know what you want to do, then that is also a question.

so if it is for a single row here is some code:


Private Sub CommandButton1_Click()
Dim ans As Long
Dim rw2 As Long
With Sheets("Sheet2")
 rw2 = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
ans = Application.InputBox("Enter Row Number", Type:=1)
If ans < 1 Or ans > Rows.Count Then
 MsgBox "Invalid row: " & ans
 Exit Sub
End If
  Sheets("Sheet1").Range("A" & ans).Copy Sheets("Sheet2").Range("O" & rw2)
  Sheets("Sheet1").Range("B" & ans).Copy Sheets("Sheet2").Range("H" & rw2)
  Sheets("Sheet1").Range("C" & ans).Copy Sheets("Sheet2").Range("F" & rw2)
  Sheets("Sheet1").Range("D" & ans).Copy Sheets("Sheet2").Range("P" & rw2)
  Sheets("Sheet1").Range("E" & ans).Copy Sheets("Sheet2").Range("A" & rw2)
  Sheets("Sheet1").Range("F" & ans).Copy Sheets("Sheet2").Range("D" & rw2)
  Sheets("Sheet1").Range("G" & ans).Copy Sheets("Sheet2").Range("K" & rw2)
  Sheets("Sheet1").Range("H" & ans).Copy Sheets("Sheet2").Range("I" & rw2)
  Sheets("Sheet1").Range("I" & ans).Copy Sheets("Sheet2").Range("J" & rw2)
  Sheets("Sheet1").Range("J" & ans).Copy Sheets("Sheet2").Range("L" & rw2)
  Sheets("Sheet1").Range("K" & ans).Copy Sheets("Sheet2").Range("B" & rw2)
  Sheets("Sheet1").Range("L" & ans).Copy Sheets("Sheet2").Range("G" & rw2)
  Sheets("Sheet1").Range("M" & ans).Copy Sheets("Sheet2").Range("E" & rw2)
  Sheets("Sheet2").Range("N" & rw2).Value = 1
End Sub

test this on a copy of your workbook until you are sure it does what you want.  It assumes that row 1 of sheet2 has headers/column labels in it.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Need 1 more addition to the above code.

Sheet1 Column F has 6 type of requests.

(Using Dummy detail)

Macro should not copy data from Range A (Sheet1) and paste to Range O (Sheet2)
if it come across keywords AAAAA and BBBBB.

ANSWER: Bimmy,

I am not sure why someone running the code would identify a row that shouldn't be copied, but if you want a safeguard:


Private Sub CommandButton1_Click()
Dim ans As Long
Dim rw2 As Long
Dim r as Range
With Sheets("Sheet2")
rw2 = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
ans = Application.InputBox("Enter Row Number", Type:=1)
If ans < 1 Or ans > Rows.Count Then
MsgBox "Invalid row: " & ans
Exit Sub
End If
 set r = Sheets("Sheet1").Range("F" & ans)
 if r.value <> "AAAAA" and r.Value <> "BBBBB" then
 Sheets("Sheet1").Range("A" & ans).Copy Sheets("Sheet2").Range("O" & rw2)
 Sheets("Sheet1").Range("B" & ans).Copy Sheets("Sheet2").Range("H" & rw2)
 Sheets("Sheet1").Range("C" & ans).Copy Sheets("Sheet2").Range("F" & rw2)
 Sheets("Sheet1").Range("D" & ans).Copy Sheets("Sheet2").Range("P" & rw2)
 Sheets("Sheet1").Range("E" & ans).Copy Sheets("Sheet2").Range("A" & rw2)
 Sheets("Sheet1").Range("F" & ans).Copy Sheets("Sheet2").Range("D" & rw2)
 Sheets("Sheet1").Range("G" & ans).Copy Sheets("Sheet2").Range("K" & rw2)
 Sheets("Sheet1").Range("H" & ans).Copy Sheets("Sheet2").Range("I" & rw2)
 Sheets("Sheet1").Range("I" & ans).Copy Sheets("Sheet2").Range("J" & rw2)
 Sheets("Sheet1").Range("J" & ans).Copy Sheets("Sheet2").Range("L" & rw2)
 Sheets("Sheet1").Range("K" & ans).Copy Sheets("Sheet2").Range("B" & rw2)
 Sheets("Sheet1").Range("L" & ans).Copy Sheets("Sheet2").Range("G" & rw2)
 Sheets("Sheet1").Range("M" & ans).Copy Sheets("Sheet2").Range("E" & rw2)
 Sheets("Sheet2").Range("N" & rw2).Value = 1
 else
   msgbox "Nothing copied because F" & ans & " contains " & r.value
 end if
End Sub

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hope this explanation Helps

Task 1

If Range F has keywords AAAAA and/or BBBBB, macro should copy data from
Range B,C,D,E,F,H,I,J,K,M from Sheet1 and paste it to
Range H,F,P,A,D,I,J,L,B,E of Sheet2

Task 2

If Range F has any other keywords other than AAAAA and/or BBBBB,
macro should copy data from Sheet1
Range A,B,C,D,E,F,G,K,L,M and paste it to
Range O,H,F,P,A,D,K,B,G,E of Sheet2

All this should be achieved through a pop-up box asking for Row Number.

So if I enter Row Number as 3, macro should perform both the above mentioned tasks.

The data should be pasted on the next empty row available on Sheet2

Answer
Bimmy,

This is what I understand you to want:

Private Sub CommandButton1_Click()
Dim ans As Long, i As Long
Dim rw2 As Long
Dim r As Range
Dim sCol1 As String, sCol2 As String
Dim s1 As Variant, s2 As Variant
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
' Assumes column A in sheet2 will always have a value in column
' A of the last used row
rw2 = sh2.Cells(sh2.Rows.Count, "A").End(xlUp).Row + 1

ans = Application.InputBox("Enter Row Number", Type:=1)
If ans < 1 Or ans > Rows.Count Then
MsgBox "Invalid row: " & ans
Exit Sub
End If
Set r = Sheets("Sheet1").Range("F" & ans)
If r.Value = "AAAAA" Or r.Value = "BBBBB" Then
 s1 = Split("B,C,D,E,F,H,I,J,K,M", ",")
 s2 = Split("H,F,P,A,D,I,J,L,B,E", ",")
Else
 s1 = Split("A,B,C,D,E,F,G,K,L,M", ",")
 s2 = Split("O,H,F,P,A,D,K,B,G,E", ",")
End If
For i = LBound(s1) To UBound(s1)
   sCol1 = s1(i)
   sCol2 = s2(i)
   sh1.Range(sCol1 & ans).Copy sh2.Range(sCol2 & rw2)
Next

 
End Sub

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.