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.

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

Postby nimv1 » Tue Feb 26, 2013 5:44 pm

Dear S,

Thanks for the quick reply.
You mention replace my code with the below.
Do I also have to stop using the initial code you emailed me? i.e. delete bot codes and replace with the below?

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 7:02 pm

Yes. It is a complete replacement.
  • 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 » Thu Feb 28, 2013 11:49 am

Dear S,

Many thanks for all your help. I have tried based on the code you have given to do the exact same thing, but copy and paste a column of data rather than a row of data.
However, my code will not work. Please can you help?

For example, in the attached, I would like a macro to copy data from sheet1 (cells D5:D9) into the nxt empty row of sheet 2 (i.e for this case, cell D4).

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 » Thu Feb 28, 2013 2:59 pm

Yes, you can modify the first code I gave you to do this job. This line of code needs adjustment:-
Set Rng = Range(.Cells(Rs, NwsStart), .Cells(Rs, NwsEnd))
This is the syntax of the above code:-
Code: Select all
= Range(.Cells(Row, Column), .Cells(Row, Column))
What it burns down to is something like Range("A1:B10") where you specify the first and last cells of the range, each using Row and Column for definition.
In the original (above) code the row is the same for beginning and end of the range while the columns are defined by NwsStart and NwsEnd. In your new concept you will need to change that around. You want the column to be the same and Start and End to be different.
You should replace Rs (a constant row) with Cs (a constant column). If you don't need to determine the source column programmatically you might add an enumeration, like NwsSource = 4, and use it in place of Cs.
You might use the same NwsStart and NwsEnd enumerations to specify the first row of the Source range and the last.
NwsStart = 5
NwsEnd = 9
But change the comments to show what the numbers mean.
Finally, enter the correct specs of the range. It should look like this:
Code: Select all
Set Rng = Range(.Cells(NwsStart, Cs), .Cells(NwsEnd, Cs))
' or
Set Rng = Range(.Cells(NwsStart, NwsSource), .Cells(NwsEnd, NwsSource))


The other problem is that you need to transpose the data. The original code takes a row and pastes a row. Now you want to take a column and paste a row. This is the answer:-
Code: Select all
Ws2.Cells(Rt + 1, NwsTarget).PasteSpecial Paste:=xlValues, Transpose:=True
  • 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 » Thu Feb 28, 2013 3:35 pm

Hi S,

I am having difficulty in applying everything you have said correctly into the code.
I am getting syntax error.
Please see attached spreadsheet with trying to apply what you have said.
Would appreciate it if you are able to help and correct?

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 nimv1 » Fri Mar 01, 2013 4:23 am

Hi S,

Had a really good go at applying what you said, but still not getting it right!
I have managed to copy the right data (i.e. column data), but it still pastes it into a row.
Please see attached.
I hope you (or anyone) can help me to paste it correctly into cells D4:D8 and going forward, paste into the next blank cell in row4?

Just to be clear, the next copy and paste will return the data into cells E4:E8 and so on.
Therefore I do not need to transpose. I.e. I am copying a column and pasting as a column.
The next set of data will identify that there is data in cell D4 and therefore paste the new data into the next column.

Hope this is clear?

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 » Sat Mar 02, 2013 1:06 am

nimv1,
I thought you were almost there and didn't want to spoil your fun by just slapping the solution on the table. How about you give it one more try?
Best, you go back to the original code I posted. Forget about modifying the Paste command because, as you say, you paste column to column (I hadn't realized that).
In my last post I told you how to set the range to copy from, like Range(.Cells(Row, Column), .Cells(Row, Column)). That part is still to be done. But now you need to define the target cell which is Ws2.Cells(Rt, Ct)
Rt is probably always the same row. You can set it in the enumeration Nws, say NwsTargetRow = ??.
(Of course, if you have NwsTargetRow you don't need Rt any more; remove Dim Rt as Long)
Ct is a variable column, and this is how you get the value:
Throw out the function LastRow() unless it is used by another procedure in the same workbook.
Copy the function LastColumn() from the forum Excel Macros on this site. It is somewhere on the 2nd page, near the end. Paste it into project and call it like Ct = LastColumn(NwsTargetRow, Ws2)
Remember to declare Dim Ct As Long.
I'm sure you can get it done now. Good luck!
  • 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 » Sat Mar 02, 2013 3:16 pm

Hi S,

I also wish I could do it all on my own!
From reading your reply, this has got me more confused to be honest. My code seems to be all over the place!
I think I have made it worse! Constantly been getting 'run time' or 'syntax' error when I apply changes.
I have spent all day trying to figure it out, but am getting nowhere.
I would appreciate it if you could provide your solution, and therefore realise where I am going wrong?
Be much appreciated S,

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 Mar 02, 2013 7:52 pm

You set the source range 100% correct, but there was some confusion is setting the target cell. The function LastRow returns a row number, but you need a column number (Ct = Column/Target). So, I discarded the function LastRow and inserted the function LastColumn in its place. Here is the complete code:-
Code: Select all
Option Explicit

    Enum Nws                        ' Worksheet Source (Sh1)
        NwsStart = 5                ' Sh1 - row
        NwsEnd = 9                  ' Sh1 - row
        NwsSource = 4               ' Sh1 - 4 = Column D
    End Enum

    Enum Nwt                        ' Worksheet Target (Sh2)
        NwtFirstRow = 4             ' Sh2 - target row
        NwtFirstCol = 4             ' Sh1 - 4 = Column D
    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 Ct As Long                  ' Target column
   
    Set Ws1 = Sheets(Sh1)
    Set Ws2 = Sheets(Sh2)
   
    Ct = LastColumn(NwtFirstRow, Ws2) + 1
    If Ct < NwtFirstCol Then Ct = NwtFirstCol

    With Ws1
        Set Rng = Range(.Cells(NwsStart, NwsSource), .Cells(NwsEnd, NwsSource))
    End With
    Rng.Copy
    Ws2.Cells(NwtFirstRow, Ct).PasteSpecial Paste:=xlValues
End Sub

Private Function LastColumn(Optional ByVal Rw As Long, _
                            Optional Ws As Worksheet) As Long
   
    ' 0056 V 2.1 Apr 2, 2012
   
    ' Return the number of the last non-blank column in row Rw.
    ' If no row is specified,
      ' return the last column from row 1.
    ' If no worksheet is specified,
      ' return the result from the currently active sheet.
   
    Dim C As Long
   
    If Ws Is Nothing Then Set Ws = ActiveSheet
    Rw = IIf(Rw, Rw, 1)
    With Ws
        C = .Cells(Rw, .Columns.Count).End(xlToLeft).Column
        With .Cells(Rw, C)
            ' in a blank row the last used column is 0 (= none)
            If C = 1 And .Value = vbNullString Then C = 0
            ' include all columns of a merged range
            LastColumn = C + .MergeArea.Columns.Count - 1
        End With
    End With
End Function
  • 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 » Mon Mar 04, 2013 4:27 am

Thanks S, sorry for the late reply.
You have helped alot. Would not have got there on my own!

Just a quick further question on this please S, If I wanted to copy and paste 2 columns at a time, would the code change much?
For example copy B30:D36 into C30:D36 and the following time paste into the next 2 empty columns of sheet 2(E30:F36)?

Please note, this would be another macro in regards to the previous one, therefore I would like to keep both.

Regards

N
  • 0

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

PreviousNext

Return to Macros and VBA Questions

Who is online

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