REAL-TIME

VBA Projects

Full Access with Source Code

  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

120+ PROFESSIONAL

Project Management Templates

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Effortlessly Manage Your Projects

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

Share Post

Description:

Copying Data from One Range to Another range is most commonly performed task in Excel VBA programming. We can copy the data, Formats, Formulas or only data from particular range in Excel Workbook to another range or Sheet or Workbook.

This example to show you how to copy data from one Range to another using Excel VBA. This is one of the frequently used codes in the VBA, we often do this activity, copying the data from one range to another range in a worksheet.r

Copy Data from One Range to Another in Excel VBA- Solution(s):

Copy Data from One Range to Another in Excel VBAYou can use Copy method of a range to copy the data from one range to another range.

Copy Data from One Range to Another in Excel VBA – An Example

The following example will show you copying the data from one range to another range in a worksheet using Excel VBA.

Code:
'In this example I am Copying the Data from Range ("A1:B10") to Range(E1")
Sub sbCopyRange()

'Method 1
Range("A1:B10").Copy Destination:=Range("E1")
'Here the first part is source range, 
'and the second part is target range or destination.
'Target can be either one cell or same size of the source range.

'Method 2
Range("A1:B10").Copy
Range("E1").Select
ActiveSheet.Paste

'In the second method, first we copy the source data range
'Then we select the destination range
'Target can be either one cell or same size of the source range.
'Then we paste in the active sheet, so that it will paste from active range

Application.CutCopyMode = False

End Sub
Instructions:
  1. Open an excel workbook
  2. Enter some data in Sheet1 at A1:B10
  3. Press Alt+F11 to open VBA Editor
  4. Insert a Module for Insert Menu
  5. Copy the above code and Paste in the code window
  6. Save the file as macro enabled workbook
  7. Press F5 to run it

Now you should see the required data (from Range A1 to B10 ) is copied to the target range (Range E1 to F10).

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBATags: Last Updated: December 19, 2022

57 Comments

  1. rajaudaiyar March 31, 2014 at 3:18 PM - Reply

    How to copy excel cell to excel another cell

  2. PNRao April 1, 2014 at 9:46 AM - Reply

    Hi,
    You can use Cells(1,1).Copy Destination:=Cells(1,2) to copy the data from one cell to another cell, it is same as copying a range from to another range.

    Thanks-PNRao!

  3. Amit Shah October 30, 2014 at 7:07 AM - Reply

    Hi, please help me on my data. Cell A1 has a roll number and cell C1 has the name. But my data is not continues. There are blanks and some miscellaneous data also. I want is, if A1 has roll number then copy the name from C1 and paste in F1 cell. Please help me with the coding.

    Thanks
    Amit

  4. Galo November 20, 2014 at 3:36 AM - Reply

    Hello, please your help.

    How do I copy a range of cells from workshhet1 to worksheet2 based on the name of a column in worksheet1?

    For example I have in WS1 a column named “Items”. I want the macro in WS2 to look for the column “Items” in WS1 and copy such column´s data in WS2.

  5. Amol January 5, 2015 at 1:38 PM - Reply

    Its working perfect , but my cells contain formula in it and this methods not working to move data from cells have formula in it . please help me in this .

  6. PNRao January 6, 2015 at 8:04 PM - Reply

    Hi Amol,

    When you have the formulas in the Cell, you can do the paste special as values: Here the Simple example to copy the data (cells or range) with formulas:

    Range(“B5:B6”).Copy
    Range(“J5”).PasteSpecial (xlPasteValues)

    Thanks-PNRao!

  7. Jun Rizalde January 19, 2015 at 3:28 PM - Reply

    Hi Valli or Anyone,

    Please help, On sheet-1 I have an data entry field. I want my macro to copy any data I place on the cell on sheet-1 and paste it to the cells in sheet-2. I works on the first line however, I want the next data to copied and pasted on the next Row in sheet-2

    Example:
    Sheet 1 Row1 Column 1 Data Field: JUN
    Sheet 2 Row1 Column 1: JUN
    Sheet 1 Row1 Column 1 Data Field: RAJAUDAIYAR
    Sheet 2 Row2 Column 1: RAJAUDAIYAR
    Sheet 1 Row1 Column 1 Data Field: PNRAO
    Sheet 2 Row2 Column 1: PNRAO
    Sheet 1 Row1 Column 1 Data Field: AMIT SHAH
    Sheet 2 Row3 Column 1: AMITH SHAH

  8. PNRao January 19, 2015 at 6:42 PM - Reply

    Hi Jun,
    It seems like you want to synchronize the Worksheet2 with Worksheet1.
    Place the below code in the Sheet1 Code module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Sheets("Sheet2").Range(Target.Address) = Range(Target.Address)
    End Sub
    

    Please feel free to ask me if you are looking for something else.

    Thanks-PNRao!

  9. Jun Rizalde January 20, 2015 at 12:27 PM - Reply

    Hi PNRao,

    I tried using the script you gave but I encountered errors.

    Here is what I wanted to do actually.

    In sheet1 cell A1 I am using it as a entry field. Let say I type in your name PNRao then I will have a click button that will paste it to Sheet2 cell A1. Then I will type in my name on shee1 cell A1 Jun and click on the button. I will then paste my name on Sheet2 cell A2.

    My statement may be confusing, sorry.

  10. PNRao January 23, 2015 at 10:43 AM - Reply

    Hi Jun,

    Assign this macro to your button click:

    Sub btnClikToEnter()
    'Find last used row in sheet 2
    lastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
     lRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
     Do While Application.CountA(ActiveSheet.Rows(lRow)) = 0 And lRow <> 1
     lRow = lRow - 1
     Loop
     lastRow = lRow
     
     If Sheets("Sheet2").Range("A1") = " And lastRow = 1 Then
      Sheets("Sheet2").Range("A1") = Sheets("Sheet1").Range("A1")
     Else
     Sheets("Sheet2").Range("A" & lastRow + 1) = Sheets("Sheet1").Range("A1")
     End If
    End Sub
    

    Thanks-PNRao!

  11. boskey March 10, 2015 at 1:04 PM - Reply

    hi,
    i m working in solar industry, i have a problem related to data.i have two excel file i st have the particular data with data and second one containing all year data.so i have to merge means particular dates data from excel 2 to excel 1 ..how do i do thisin easy manner

  12. Khaled March 13, 2015 at 9:09 AM - Reply

    please check the last row in the following code, it contains error but I cant find

    Sub copyDataFromMultipleWorkbooksIntoMaster()

    Dim FolderPath As String, Filepath As String, Filename As String

    FolderPath = “C:workexcel_tutorialsuppliers”

    Filepath = FolderPath & “*.xls*”

    Filename = Dir(Filepath)

    Dim lastrow As Long, lastcolumn As Long

    Do While Filename “”
    Workbooks.Open (FolderPath & Filename)
    ‘Range(“A2:D2″).Copy
    lastrow = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Row
    lastcolumn = ActiveSheet.Cells(1, Columns.count).End(xlToLeft).Column
    Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy
    Application.DisplayAlerts = False
    ActiveWorkbook.Close

    erow = Sheet1.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row

    ‘If we wanted to paste data of more than 4 columns we would define a last column here also
    ‘lastcolumn = ActiveSheet.Cells(1, Columns.count).End(xlToLeft).Column
    ‘ActiveSheet.Paste Destination:=Worksheets(“Sheet1″).Range(Cells(erow, 1),
    Cells(erow, lastcolumn))
    ActiveSheet.Paste Destination:=Worksheets(“Sheet1″).Range(Cells(erow, 1),
    Cells(erow, 4))

    Filename = Dir

    Loop

    End Sub

  13. jen March 15, 2015 at 10:36 PM - Reply

    Hi,

    I want to copy data from multiple cells of one sheet into a single cell in another sheet,
    For example is cells A1- apple
    B1 – orange
    C1 – grape.
    I want cell A1 in sheet 2 to have apple,orange,grape

    How to write macor for this please

  14. Sanchit March 16, 2015 at 2:54 PM - Reply

    Hi,
    I used the above method to insert the columns from another sheet. It worked perfectly well.

    I used the button click to copy columns from another sheet. Now I want to insert checkboxes against those copied columns on the option click.
    Also I want the checkbox to be checked in one case and unchecked if in another case.
    Please help me with that!!!

  15. PNRao March 21, 2015 at 2:45 PM - Reply

    You can use below syntax to copy and paste:

    Method1:
    Range(“YourSourceRange”).Copy Destination:=Range(“YourDestionationRange”)
    Example:
    Range(“A1:D5”).Copy Destination:=Range(“E5”)

    Method 2:
    Range(“YourSourceRange”).Copy
    Range(“YourDestionationRange”).Select
    Activesheet.Paste
    Example:
    Range(“A1:D5”).Copy
    Range(“E5”).Select
    Activesheet.Paste

    Hope this helps! Thanks-PNRao!

  16. PNRao March 21, 2015 at 2:51 PM - Reply

    Hi Jen,
    You can use the below code:

    Sub sbConcatenateCells()
        Sheet2.Range("A1") = Sheet1.Range("A1") & "," & Sheet1.Range("B1") & "," & Sheet1.Range("C1")
    End Sub
    

    Thanks-PNRao!

  17. uday April 7, 2015 at 1:18 PM - Reply

    Hi All,

    Can anyone help me in printing data from one filed to another field using a button in the same worksheet

    Thanks

  18. Dragony2000 April 12, 2015 at 10:12 AM - Reply

    I make find code to select multiple cells i wanna copy the selected cells to another rows in the same column

    i mean if searching catch C1 , M1 , R1 it copies automatically in ( C5, M5, R5) and fill until (C9, M9 , R9)

  19. Teecee April 29, 2015 at 2:30 PM - Reply

    I would like to add a function to my Excel workbook something like as follows:
    set(destination,source) {destination.value=source.value; current_cell.value=source.value}

    The current cell would be the cell that contains the usage of the “set” function along with the needed arguments. It’s output result isn’t actually necessary though, what is important is the ability to transfer values to another cell range.

  20. khine su win May 13, 2015 at 1:32 PM - Reply

    How to copy excel file data only by avoiding formula?
    I want to copy only data to another file.
    Please let me know the way.
    Thank you.

    Khine Su Win

  21. PNRao May 13, 2015 at 8:46 PM - Reply

    Hi Khine su win,
    Here is the code to copy the range A1 to C10 and paste at D1:
    Paste:=xlPasteValues option allow you to copy only the values and paste.

    Sub CopyTheDataAndPasteOnlyValues() '
        Range("A1:C10").Copy
        Range("D1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End Sub
    

    Thanks-PNRao!

  22. rajni kumar May 14, 2015 at 7:30 PM - Reply

    Hi PNRao,
    I have a requirement to move the data from columns into rows, and also I need parameterized the starting point of the columns that are to be converted into rows. Find below my example

    raw data
    FName LName Num1 Num2 Num3
    rajni kumar 10 11 23
    David Betts 5 3 8

    to covert like this
    FName LName Values
    rajni kumar 10
    rajni kumar 11
    rajni kumar 23
    David betts 5
    David betts 3
    David betts 8

    Please can you help or give in some guidance I can try few options.

  23. khine su win May 15, 2015 at 7:39 AM - Reply

    Hi PNRao!

    Thank for your answer.
    Please may i ask another question.
    I want to copy entire excel file but i need only data.
    I mean that wnat to copy data by avoiding formula.
    And then, i also don’t want to open excel file when do copy data.
    Please let me know the way.
    Thank you.

    Khine Su Win

  24. tejas May 15, 2015 at 2:45 PM - Reply

    how to copy the values column wise present in the sheet1 and paste each column values in the different sheets in the workbook.

  25. Tommy May 27, 2015 at 3:52 AM - Reply

    Hello..

    I have 4 columns in sheet 1. I want to match a value in column A, and match a value in column B. Once both values match at a row X, I want to copy column C, rowX to rowX+1 and copy column D, row X to rowX+1 and paste them into a certain cells in Sheet 2.

    Please advice. Thank you,

  26. Nits June 22, 2015 at 9:00 PM - Reply

    Hi,

    I wanted little assistance in the issue I’m facing currently.
    I have 2 sheets, (A) Master Data Sheet (B) Detailed Activities.
    Master Data sheet will have all the process name and there corresponding activities along with some other details in other column. (Basically

    the Database)
    I have 4 columms in Master Data Sheet namely Process name, Activity, Activity Owner and Hours.
    In the Detailed Activities sheet there are multiple coloumns including the columns present in the Master Data Sheet. (i.e. Process name,

    Activity, Activity Owner and Hours.)

    Issue:

    When I enter a process name in the Detailed Activities Sheet in “Process Name”, I want to autopopulate the corresponding Activity, Activity

    Owner and Hours column in the Scheduler Sheet.
    Then when a enter another process name below the previos process name entered the corresponding columns should get auto populated.(As

    per the details mentioned in the Master Data Sheet).

    I made this code but I have given specific range. The following code is only for 1 process name. (Thats why i gave specific range)
    I want to code for atleast 40 processes and I should be able to enter one range below the other.

    For Detailed Activity Sheet
    Column C = Process name
    Column L = Activity
    Column M = Activity Owner
    Column N = Hours

    For Master Data Sheet
    Column A = Process name
    Column B = Activity
    Column C = Acivity Owner
    Column D = Hours

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Select Case Range(“C2”).Value

    Case “Transfer”

    Sheets(“Detailed Activities”).Range(“L2:L12”).Value = Sheets(“Master Data Sheet”).Range(“B2:B12”).Value
    Sheets(“Detailed Activities”).Range(“M2:M12”).Value = Sheets(“Master Data Sheet”).Range(“C2:C12”).Value
    Sheets(“Detailed Activities”).Range(“N2:N12”).Value = Sheets(“Master Data Sheet”).Range(“D2:D12”).Value
    Sheets(“Detailed Activities”).Range(“D2:D12”).Value = “-”
    Sheets(“Detailed Activities”).Range(“E2:E12”).Value = “-”
    Sheets(“Detailed Activities”).Range(“I2:I12”).Value = “-”
    Sheets(“Detailed Activities”).Range(“J2:J12”).Value = “-”

    Case ” ”

    Sheets(“Detailed Activities”).Range(“L2:L12″).Value = ” ”
    Sheets(“Detailed Activities”).Range(“M2:M12″).Value = ” ”
    Sheets(“Detailed Activities”).Range(“N2:N12″).Value = ” ”

    Case ”

    Sheets(“Detailed Activities”).Range(“L2:L12″).Value = ” ”
    Sheets(“Detailed Activities”).Range(“M2:M12″).Value = ” ”
    Sheets(“Detailed Activities”).Range(“N2:N12″).Value = ” ”

    Case Else

    Sheets(“Detailed Activities”).Range(“L2:L12”).Value = “Please enter a valid Process Name to update the Activities”
    Sheets(“Detailed Activities”).Range(“M2:M12”).Value = “Please enter a valid Process Name to update the Activity Owner”
    Sheets(“Detailed Activities”).Range(“N2:N12”).Value = “Please enter a valid Process Name to calculate estimated HRS”

    End Select
    Application.EnableEvents = True
    End Sub

    For transfer process, there are 10 rows of Acitvity, Activity owner and hours.
    So when i enter ” Transfer” in the process column in the Detailed activities sheet, the code should search the keyword “transfer” in the

    master data shet and then copy the corresponding number of rows of Activity, activity owner and hrs from master data sheet to detailed

    activities sheet.
    Then when I enter another process name the same function should be carried out again.

    Please advise.

    Kind Regards,
    Nits.

  27. esskaybee July 1, 2015 at 9:46 AM - Reply

    Sub abcd()
    ‘ Get customer workbook…
    Dim customerBook As Workbook
    Dim filter As String
    Dim caption As String
    Dim customerFilename As String
    Dim customerWorkbook As Workbook
    Dim targetWorkbook As Workbook

    ‘ make weak assumption that active workbook is the target
    Set targetWorkbook = Application.ActiveWorkbook

    ‘ get the customer workbook
    filter = “Text files (*.xlsx),*.xlsx”
    caption = “Please Select an input file ”
    customerFilename = Application.GetOpenFilename(filter, , caption)

    Set customerWorkbook = Application.Workbooks.Open(customerFilename)

    ‘ assume range is A1 – C10 in sheet1
    ‘ copy data from customer to target workbook
    Dim targetSheet As Worksheet
    Set targetSheet = targetWorkbook.Worksheets(1)
    Dim sourceSheet As Worksheet
    Set sourceSheet = customerWorkbook.Worksheets(1)

    targetSheet.Range(“A1”, “C10”).Value = sourceSheet.Range(“A1”, “C10”).Value

    targetSheet.UsedRange.AutoFilter Field:=1, Criteria1:=Array(“A123”, “A234”, “A128”, “A129”), Operator:=xlFilterValues, VisibleDropDown:=False

    ‘ Close customer workbook
    customerWorkbook.Close
    End Sub

  28. Preet August 10, 2015 at 5:54 PM - Reply

    I am struggling with copying data from master file to rest of tabs based on filter. Assuming i have master sheet with column name application and my tabs are arranged with column names, i wanted to copy data from master sheet to corresponding tabs of each application, with keeping master data as well. Also if something is present in master tab but not in application tab, that row must be moved to separate tab i created. This tab is common to all application tab. Reason being, this master sheet is going to get updated everyday and i want my tabs to have current data but keeping the old one in misc tab. I will really appreciate the help. Thanks.

  29. Ajay September 2, 2015 at 12:19 PM - Reply

    Hi Sir,

    With below mention code i able to copy data from _Update Sheet To Master Sheet but In _Update Sheet My Data have formula so i not getting the desire result because formula is getting copied in Master Sheet so please help me

    Sub CopyRangeFromSheet1toSheet2()
    Dim lastRow As Long
    lastRow = Sheets(“Master Sheet”).Range(“A100000”).End(xlUp).Row + 1 ‘ then next free row in sheet2
    Sheets(“_Update”).Range(“A3:F3”).Copy Destination:=Sheets(“Master Sheet”).Range(“C” & lastRow)
    End Sub

    Best Regards
    Ajay

  30. PNRao September 2, 2015 at 11:14 PM - Reply

    Hi Ajay,
    You can copy any range and paste on values using paste special method. Please see the example in the below article:

    http://analysistabs.com/vba-code/range/m/pastespecial/

    Thanks-PNRao!

  31. Shally June 24, 2016 at 2:59 PM - Reply

    Hello,

    I just started doing macros and I need to do a task which includes changing the values automatically if there is a change in 1 cell. For example if I change a value from 1 to 0 in cell A1 then which ever cell I want to put that 1 from cell A1 automatically goes to the different cell and the value there increases.

  32. Priya K August 5, 2016 at 12:51 PM - Reply

    hi

    I used the code provided by you as i have similar query. But what it does is it only copies into rows and then whatever entered gets overwritten in row 2 of sheet2.

  33. SP November 17, 2016 at 10:11 AM - Reply

    Hi, I need to paste different cells in a one sheet to another sheet with same formatting and add the next set of results one below another in sheet 2

  34. Aissa March 8, 2017 at 1:27 PM - Reply

    Thanks for your great answers
    The code works very well and I am in a need to use the same way to copy data from a closed csv file to an active worksheet.

    Would you help?

    thanks

  35. Viral March 21, 2017 at 11:51 AM - Reply

    I have the VBA Code below,

    which ask the use to select the folder from the system, then the code runs and ckecks the excel file, if the folder have the excel file then it will copy and paste the same in the macro file “Copy and paste.xlsx”.

    Now there can be 1 excel file or more than 1.

    Private Sub CommandButton1_Click()
    Sheets(“Sheet1”).Select
    Dim wb As Workbook
    Dim myPath As String
    Dim myFile As String
    Dim myExtension As String
    Dim FldrPicker As FileDialog
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
    With FldrPicker
    .Title = “Select A Target Folder”
    .AllowMultiSelect = False
    If .Show -1 Then GoTo NextCode
    myPath = .SelectedItems(1) & ”
    End With

    NextCode:
    myPath = myPath
    If myPath = ” Then GoTo ResetSettings
    myExtension = “*.xlsx”
    myFile = Dir(myPath & myExtension)
    Do While myFile ”
    Set wb = Workbooks.Open(Filename:=myPath & myFile)

    With Workbooks(myFile)
    With .Range(“a2:BZ” & .Range(“B” & .Rows.Count).End(xlUp).Row)
    .Copy
    End With
    End With

    wb.Close SaveChanges:=False
    myFile = Dir
    Loop
    MsgBox “Task Complete!”
    ResetSettings:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    End Sub

  36. Iva April 14, 2017 at 7:44 PM - Reply

    Hi there:
    I need to copy an entire column to another one but just part of the data in each cell. is it possible to do that?
    For Example:
    Column 1 contains last name, name and I just want to copy last name (which is before the coma) to another range.

  37. PNRao April 15, 2017 at 11:59 AM - Reply

    Hi, Can do this by looping through the each row and split the data, and get the first part.

    But this will slow down the process when you have data in more Rows.

    Here is my suggested approach to Copy the Data (First part of the String in a Column) to another Column.

    I am adding a temporary sheet and copy the required column to the temporary sheet. And splitting the Column by Comma. Then Copying the First Column to required sheet and Column.

    Sub sbCopyOnlyFirstPartOfTheColumn()
    
    'Add a Temporary Sheet
    Set tempSht = Worksheets.Add
    
    'Copy the required Column to Temporary Sheet
        Sheets("Sheet1").Columns("A:A").Copy Destination:=tempSht.Range("A1")
    
    'Split the Column Data with Comma Delimeter
    tempSht.Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
            :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
        
    'Now Copy the First Column (first Part) into reqired Sheet and Column
    'This will copy to Sheet2 and Paste at F Column
    tempSht.Columns("A:A").Copy Destination:=Sheets("Sheet2").Range("F1")
        
    'Delete the Temporary Sheet
    Application.DisplayAlerts = False
        tempSht.Delete
    Application.DisplayAlerts = True
    End Sub
    

    Thanks!

  38. Bharath May 5, 2017 at 5:44 PM - Reply

    Hi Sir, i have data in sheet 1 and using VBA Codes to copy paste the data to sheet 2 but the problem is Copy paste is not taking place as per the invoice description. For example : Sheet one contains the following information Invoie Number, Date ,Amount and description and when i click on the command button it copy paste the date in sheet2 to respectively. but if the description is more than 2 lines that is where data is getting miss placed. Please suggest and thank you in advance.

  39. Bharath May 5, 2017 at 5:47 PM - Reply

    Below is the codes currently iam using..

    Sheets(“Invoice”).Range(“Bqty”).Copy
    ‘Activate the destination worksheet
    Sheets(“Data”).Activate
    ‘Select the target range
    Worksheets(“Data”).Cells(Rows.Count, 9).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    ‘PasteSpecial in the target destination

  40. Sravanthi May 19, 2017 at 3:42 PM - Reply

    Dear Sir,

    How do I copy all the data from one sheet and create chart/pivot from the copies data and display the results in the second sheet.

    Thank you in advance for helping,

  41. Sravanthi May 19, 2017 at 3:43 PM - Reply

    In continuation to my above query, I want to copy cells without mentioning the column/row numbers, e.g., if I have to work on different set of master data on a weekly basis with a same macro.

  42. ahmad nuri May 27, 2017 at 9:33 AM - Reply

    how to set condition so that macro will work when meet the condition

  43. Hariprashath June 7, 2017 at 10:16 AM - Reply

    Hi sir,

    How do i copy only particular data from a cell having common attributes?

    eg

    Check the below SLA job has been completed.

    Commands:
    motored -j 711_hari
    motored -j 711_ramnath
    motored -j 711_raviprasad

  44. Baldev June 24, 2017 at 3:28 PM - Reply

    Dear Friend
    Am new in VB
    Request for code for copy from Sheet1 (Range A2:I2) Fixed row with change value by every 2 seconds (Its a pull data from one site used for stock exchange. These dynamic data need to paste to Sheet2 of same workbook as Value or in CSV format that to every time paste in next available row. Would you please guide for he same .
    With regards
    Baldev

  45. Vida July 6, 2017 at 6:30 AM - Reply

    Hi!

    I’m having trouble with codes. I badly need your help.

    I wanted to copy paste only the Columns E to H from Sheet1 to Sheet2 Cell A2 IF in Column M of Sheet1 contains “Singapore” text and IF Column M contains “USA” text in Sheet3 Cell A2.

    I’ve already went through countless websites and still can’t find the codes.

    Thank you in advance!

  46. PNRao July 17, 2017 at 2:32 PM - Reply

    You can Copy the Cells based on the conditions. Your question is quite confusing.

    I am assumining that you want to:
    Copy the Data from Sheet 1 to Sheet 2 (Columns E:H)
    Condition 1: Column M data in Sheet 1 should match the Value ‘Singapore’
    Condition 2: When Range A2 value in Sheet3 is ‘USA’

    Sub sbCopyData()
    
    lRow = 200 'Last Row with Data in sheet 1
    
    'Starts form Row 2
    kCntr = 2 'Start row to paste in Sheet1
    
    
    For iCntr = 2 To lRow 'Here 2 is Start Row of Sheet1
    
    If Sheets("Sheet1").Cells(iCntr, 13) = "Singapore" And Sheets("Sheet3").Range("A2") = "USA" Then
    'Here 13=M
    
    Sheets("Sheet1").Range("E" & iCntr & ":" & "H" & iCntr).Copy _
    Destination:=Sheets("Sheet2").Range("A" & kCntr)
    kCntr = kCntr + 1
    End If
    
    
    Next
    
    
    End Sub
    
    

    Hope this helps!

  47. John October 24, 2017 at 11:55 PM - Reply

    This is what I currently have:

    Sub Match()
    Range(“F4:F” & Cells(Rows.Count, “F”).End(xlUp).Row).Copy Destination:=Range(“G4”)
    End Sub

    I want to copy and paste what is in F only if the cell in G is blank. Am currently stuck!

  48. Naveen May 31, 2018 at 11:03 PM - Reply

    Hi

    How to data transfer one excel sheet to another excel sheet with formulas using VBA Code.code

    i tried below code data will transfer but formulas not .

    sub text()
    Range(“A1:A4”).copy
    sheet2.Activate
    range(“A1”).select
    Activesheet.paste
    End sub

  49. elizabeth cendana June 23, 2018 at 4:11 PM - Reply

    Hi,
    Can you give me a code
    For a shape/ button that when clicked would autmtcally enter a letter at the desired cell.

    Example

    Button 1(anytime)

    When clicked will automatically paste at range D5

    Then another code for

    When the button whose range d4-d5 clicked
    Will autmcally be pasted on E5-E6

    Thank you

  50. PNRao June 27, 2018 at 1:16 PM - Reply

    You can create button and assign the below macro to copy and paste:

    Sub sbButton1Clcik()
    'Copy the Data
    Range("C1").Copy ' Source Data to Copy
    
    Range("D5").Select 'Target Cell to Paste
    Activesheet.Paste
    End Sub
    

    You can create another button and assign the below macro to copy and paste:

    Sub sbButton2Clcik()
    'Copy the Data
    Range("C2").Copy ' Source Data to Copy
    
    Range("E5").Select 'Target Cell to Paste
    Activesheet.Paste
    End Sub
    

    Hope this helps!

  51. Vinoth July 26, 2018 at 3:09 PM - Reply

    Hi Rao

    I have source excel sheet with tab named sheet1 . I need to search for the particular job names “JHL1999E” and “JLF1CRTW1”.then i need to start copying its corresponding data which starts from next to its immediate column.To make it clear in the JHL1999E is the position (3,2) ,i need the date to be copied from (3,3) till (3,7) .Like that i need to copy till the end of the row,There will be blank in the next row which denotes the end. Please help me in the coding.

  52. sundar August 14, 2018 at 12:59 PM - Reply

    hi , i need data transfer from combobox and textbox as like journal entry for accounting system

    DATE PARTICULARS V.NO VOUCHER TYPE DEBIT(RS) CREDIT(RS)

    AND IT WILL BE WORK ON MULTIPLE DR.HAEDS AND CREDIT HEADS

    ACCOUNT HEAD…1 HARRY 40000
    2 SELINA 50000
    3 ALEX 60000

    BANK 100000
    CASH 25000
    CATHARIN 25000

    IF EMPTY IT SHOULD BE SIFT DATA WHEN THERE IS 2 TRANSACTION DR AND CR
    PLEASE HELP ME

  53. Manjunath December 16, 2018 at 5:12 PM - Reply

    I want to copy Cell A5 and Cell F8 and paste into J17 and C16 .source and destination workbooks are different.

  54. dev March 16, 2019 at 5:16 PM - Reply

    Sir I want to copy data from one work book to another daily

    1.from one workbook say name book1 (column Range is daily changing B,C,D,E so i need to prompt which column data to be select)
    2.copy data to be paste to another work book say book2(here also need to ask prompt at which range say D,E,F to be paste)

    plase provide script

  55. AS September 12, 2019 at 4:59 PM - Reply

    Dear
    I want my macro to ask the user of the excel to type the date (month and year) because it’s changing evrery time we run the macro.

    BR,,,

  56. prabhat anand January 22, 2020 at 9:07 PM - Reply

    Dear,
    I want a macro which can copy & paste from user defined Source Row/Column and paste in user defined destination like
    Range(“Ai : Xi)” where i is user defined.

    Row().entire function hangs when l run code for large data (rows more than 1000) .
    Please help

  57. Rahul Agrawal April 1, 2020 at 10:11 AM - Reply

    Copy from sheet1 and paste in sheet2 and repeate … also copy from sheet2 to sheet1
    I wish to copy the specific cells values from sheet1 to sheet2 and calculations will happen and copy again data from Sheet2 to sheet1

    this process i wish to repeate for multiple line items in excel.

    PLease help with VBA

Leave A Comment