New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

record a macro to copy data and paste into next empty row?

Macros, VBA, Excel Automation, etc.

record a macro to copy data and paste into next empty row?

Postby nimv1 » Thu Feb 21, 2013 10:23 am

Hi there,

I am new to macros and wanted to know if it is possible to record a macro to do the following:

- copy and paste a selection of cells from one sheet and paste into another sheet into the next empty row?

For example, in the attached spreadsheet, what I am trying to accomplish is:

- Copy cells D113:M113 from sheet 1
- paste into C7:L7 into sheet 2

Going forward, I would like the macro to copy and paste the data in D7:L7 and so on.

Hope you can help?

Regards

N
  • 0

You do not have the required permissions to view the files attached to this post.
nimv1
Regular
 
Posts: 30
Joined: Jan 25, 2013
Reputation: 0
Excel Version: 2010

Re: record a macro to copy data and paste into next empty row?

Postby Sisyphus » Fri Feb 22, 2013 2:33 am

I have moved this topic to the forum for VBA and Macros where it may find better attention.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: record a macro to copy data and paste into next empty row?

Postby Sisyphus » Sat Feb 23, 2013 3:36 am

OK. Basically, this code will do the job:-
Code: Select all
Sub TransferData()

    Worksheets("Sheet 1").Range("D113:M113").Copy
    Worksheets("Sheet 2").Cells(7, 3).PasteSpecial Paste:=xlValues
End Sub
Observe that Cells(7, 3) specifies R and Column = Row 7, column 3 (= column C) = C7

However, this code isn't good. The rule says that you shouldn't hard-code variables. There are lots of variables. Even if they are not really variable every time you use the code they may change whenever you modify your workbook. So you take them out of the code where they are easy to find and therefore easy to modify. After you do this, this would be the result:-
Code: Select all
Option Explicit

    Enum Nws                        ' Worksheet navigation
        NwsStart = 4                ' Sh1 - column 4 = D
        NwsEnd = 13                 ' Sh1 - column 13 = M
        NwsTarget = 3               ' Sh2 - column 3 = C
    End Enum
   
Sub TransferData()

    Const Sh1 As String = "Sheet 1"
    Const Sh2 As String = "Sheet 2"
   
    Dim Ws1 As Worksheet
    Dim Ws2 As Worksheet
    Dim Rng As Range
    Dim Rs As Long                  ' Source row
    Dim Rt As Long                  ' Target row
   
    Set Ws1 = Sheets(Sh1)
    Set Ws2 = Sheets(Sh2)
    Rs = 113
    Rt = LastRow(NwsTarget, Ws2)
   
    With Ws1
        Set Rng = Range(.Cells(Rs, NwsStart), .Cells(Rs, NwsEnd))
    End With
    Rng.Copy
    Ws2.Cells(Rt + 1, NwsTarget).PasteSpecial Paste:=xlValues
End Sub

Private Function LastRow(Optional ByVal Col As Variant, _
                         Optional Ws As Worksheet) As Long
    ' 0059 V 3.2 Apr 2, 2012

    ' Return the number of the last non-blank row in column Col.
    ' Specify the column as string or number
    ' If no column is specified,
      ' return the last row from column A.
    ' If no worksheet is specified
      ' return the result from the currently active sheet.
   
    Dim R As Long
   
    If Ws Is Nothing Then Set Ws = ActiveSheet
    If VarType(Col) = vbError Then Col = 1
    With Ws
        R = .Cells(.Rows.Count, Col).End(xlUp).Row
        With .Cells(R, Col)
            ' in a blank column the last used row is 0 (= none)
            If R = 1 And .Value = vbNullString Then R = 0
            ' include all rows of a merged range
            LastRow = R + .MergeArea.Rows.Count - 1
        End With
    End With
End Function

That my seem like an awful lot of code to do the work of just two lines. You will notice the difference when you start to work with it. I draw your attention to the variable Rt which is supplied by a function. The variable Rs would need to be supplied by another function. The two lines of original code don't have this capability. With the properly written code making this change - or any other change - is child's play.
  • 1

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: record a macro to copy data and paste into next empty row?

Postby nimv1 » Sat Feb 23, 2013 7:00 pm

Hi S,

Many thanks for your reply to my message. I am very keen to try it, but being new to VBA and macros, how do apply the code below in excel?

Regards

N
  • 0

nimv1
Regular
 
Posts: 30
Joined: Jan 25, 2013
Reputation: 0
Excel Version: 2010

Re: record a macro to copy data and paste into next empty row?

Postby Sisyphus » Sat Feb 23, 2013 9:54 pm

From the Developer tab in the ribbon click Visual Basic (first button on the left) to open the VB Editor.
In the Project Explorer window on the left, right-click on the name of the workbook where you want to add the code.
In the menu that drops down select Insert and Module.
This action will insert standard code module into your VBA project. It will be called Module1.
Double-click to open this module. It will open in the main window on the left. There is nothing inside because it is new.
Paste the code I gave you into this window.
If you wish to save your workbook with the code inside remember to save it as a macro enabled workbook, with xlsm extension.

Make sure that your workbook has worksheets by the names of "Sheet 1" and "Sheet 2".
You can now run the procedure TransferData.
There are several ways to do that.
  • From the Developer tab, click on Macros. The procedure will be listed there by name. Click on it to select and press the Run button. - Or just double-click.
  • From the VB Editor, place the cursor anywhere inside the procedure you want to run.
    Don't confuse the procedure with the module. The procedure TransferData starts with the line Sub TransferData and ends with End Sub.
    From the main menu select Run and Sub/Userform
  • You can achieve the same effect by pressing F5 after placing the cursor within the procedure you want to run.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: record a macro to copy data and paste into next empty row?

Postby nimv1 » Sun Feb 24, 2013 3:16 pm

Many thanks S, you have helped me alot.
I will have a great day :)

Regards

N
  • 0

nimv1
Regular
 
Posts: 30
Joined: Jan 25, 2013
Reputation: 0
Excel Version: 2010

Re: record a macro to copy data and paste into next empty row?

Postby nimv1 » Mon Feb 25, 2013 5:12 pm

Dear S,

In regards to the code you provided, If I wanted to extend this by applying the same logic to another 2 worksheets in the same workbook (i.e. worksheet 3 and worksheet 4), is this possible?
Or would I have to rercord another macro?

I wanted to run the macro with one code if possible?

Regards

N
  • 0

nimv1
Regular
 
Posts: 30
Joined: Jan 25, 2013
Reputation: 0
Excel Version: 2010

Re: record a macro to copy data and paste into next empty row?

Postby Sisyphus » Tue Feb 26, 2013 5:24 am

Automatic a process can't be done without knowing the process. So, to your hypothetical question I can answer yes with confidence. Doing it is another matter, seeing that you probably wouldn't understand my suggestion on how to do it unless some one actually did the encoding for you.
So, presuming that you have new data in Sheet1 which you want to copy to Sheet2, and you have new data in Sheet3 which you like to paste to Sheet4, you would definitely need two separate procedures which, however, would both use a modified version of the one I gave you. The main problem right now is that your way of calling the procedure is too complicated and you already announce your feeling that you don't want it to become more so.
Please spend a moment to think of how you would like it to be done easier. Would you like a button on sheets 1 and 3 to call the macro? Or could the updating of Sheets3 and 4 be done completely automatic whenever some data change in sheets 1 and 3? Is Sheet2 always updated from Sheet1, Sheet4 from Sheet3?
There are lots of questions - all related to work flow - which will prevent you from getting what you want if you don't spend time to answer them carefully.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: record a macro to copy data and paste into next empty row?

Postby nimv1 » Tue Feb 26, 2013 3:25 pm

Hi S,

Thanks for your reply and comments.
Having thought more about the process, work flow and your comments, I would like to accomplish the following:

- Data is imported on a weekly basis into the data work tab (see attached fro example of setup)

- Different data is populated into sheets 1 and 3

- As the data is populated simultaneously, I would like a sinlge button to paste the row from sheets 1 and 3 into 2 and 4.
The data will be seperated into gross and net, making the numbers different in sheets 2 and 4

- I have had a go myself in creating a macro based on your initial one (see code below), so at present I have to run a macro to copy and paste the data from sheet 1 into sheet 3 and run another macro to copy and paste data from sheet 3 into sheet 4.

- Can one macro do both copy and paste data from sheet 1 and 3 into 2 and 4?

Regards

N

Apologies if I have not wrapped this correctly. It is the first time I have done so.

Code: Select all

Option Explicit

Enum Tws ' Worksheet navigation
TwsStart = 4 ' Sh3 - column 4 = D
TwsEnd = 13 ' Sh3 - column 13 = M
TwsTarget = 3 ' Sh4 - column 3 = C
End Enum

Sub CopyData()

Const Sh3 As String = "Net Revenue Split"
Const Sh4 As String = "Net Revenue"

Dim Ws3 As Worksheet
Dim Ws4 As Worksheet
Dim Rng As Range
Dim Rs As Long ' Source row
Dim Rt As Long ' Target row

Set Ws3 = Sheets(Sh3)
Set Ws4 = Sheets(Sh4)
Rs = 7
Rt = LastRow(TwsTarget, Ws4)

With Ws3
Set Rng = Range(.Cells(Rs, TwsStart), .Cells(Rs, TwsEnd))
End With
Rng.Copy
Ws4.Cells(Rt + 1, TwsTarget).PasteSpecial Paste:=xlValues
End Sub

Private Function LastRow(Optional ByVal Col As Variant, _
Optional Ws As Worksheet) As Long
' 0059 V 3.2 Apr 2, 2012

' Return the number of the last non-blank row in column Col.
' Specify the column as string or number
' If no column is specified,
' return the last row from column A.
' If no worksheet is specified
' return the result from the currently active sheet.

Dim R As Long

If Ws Is Nothing Then Set Ws = ActiveSheet
If VarType(Col) = vbError Then Col = 1
With Ws
R = .Cells(.Rows.Count, Col).End(xlUp).Row
With .Cells(R, Col)
' in a blank column the last used row is 0 (= none)
If R = 1 And .Value = vbNullString Then R = 0
' include all rows of a merged range
LastRow = R + .MergeArea.Rows.Count - 1
End With
End With
End Function
  • 0

You do not have the required permissions to view the files attached to this post.
nimv1
Regular
 
Posts: 30
Joined: Jan 25, 2013
Reputation: 0
Excel Version: 2010

Re: record a macro to copy data and paste into next empty row?

Postby Sisyphus » Tue Feb 26, 2013 4:11 pm

Please use the following code in place of the one you already had:-
Code: Select all
Option Explicit

    Enum Nws                        ' Worksheet navigation
        NwsStart = 4                ' Sh1 - column 4 = D
        NwsEnd = 13                 ' Sh1 - column 13 = M
        NwsTarget = 3               ' Sh2 - column 3 = C
    End Enum
   
Sub UpdateDataArchive()

    Const AllSheets As String = "Source1,Target1,Source2,Target2"
    Dim Sh() As String
    Dim i As Integer

    Sh = Split(AllSheets, ",")
    For i = 1 To 2
        TransferData Sh((i - 1) * 2), Sh((i - 1) * 2 + 1)
    Next i
End Sub

Private Sub TransferData(ByVal Sh1 As String, _
                         ByVal Sh2 As String)

    Dim Ws1 As Worksheet
    Dim Ws2 As Worksheet
    Dim Rng As Range
    Dim Rs As Long                  ' Source row
    Dim Rt As Long                  ' Target row
   
    Set Ws1 = Sheets(Sh1)
    Set Ws2 = Sheets(Sh2)
    Rs = 113
    Rt = LastRow(NwsTarget, Ws2)
   
    With Ws1
        Set Rng = Range(.Cells(Rs, NwsStart), .Cells(Rs, NwsEnd))
    End With
    Rng.Copy
    Ws2.Cells(Rt + 1, NwsTarget).PasteSpecial Paste:=xlValues
End Sub

Private Function LastRow(Optional ByVal Col As Variant, _
                         Optional Ws As Worksheet) As Long
    ' 0059 V 3.2 Apr 2, 2012

    ' Return the number of the last non-blank row in column Col.
    ' Specify the column as string or number
    ' If no column is specified,
      ' return the last row from column A.
    ' If no worksheet is specified
      ' return the result from the currently active sheet.
   
    Dim R As Long
   
    If Ws Is Nothing Then Set Ws = ActiveSheet
    If VarType(Col) = vbError Then Col = 1
    With Ws
        R = .Cells(.Rows.Count, Col).End(xlUp).Row
        With .Cells(R, Col)
            ' in a blank column the last used row is 0 (= none)
            If R = 1 And .Value = vbNullString Then R = 0
            ' include all rows of a merged range
            LastRow = R + .MergeArea.Rows.Count - 1
        End With
    End With
End Function

You should now call the procedure UpdateDataArchive which calls the previous procedure twice, each time feeding it the names of a different pair of worksheets. These names are specified in the new procedure. I have used different names from before so as to easier distinguish between them. You can use any names. Just be sure to have commas between them and no leading or trailing space characters.

The presumption is that the both source and target sheets are of identical construction and design. Should this not be the case the difference would also be set in the new procedure and passed to TransferData as a parameter.

I have refrained from creating a button because it is hard to tell where to put it. Consider setting up a keyboard shortcut if calling the macro from the Macro button is too cumbersome.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Next

Return to Macros and VBA Questions

Who is online

Users browsing this forum: Google [Bot] and 41 guests