excel vba copy from one sheet to 2nd

July 29, 2011 at 20:28:46
Specs: Windows 7
Hi, any help very much appreciated. Excel VBA. 2007, Windows 7.

Enter a numeric value in cell B10 in Sheet 1, say 20
Copy the numeric value of rows from sheet 2 (one column only) and paste in sheet 1 starting at cell B15 say.
So 20 values in column B say in sheet 2 will be copied and pasted to Sheet 1 cells B15 down.

Hope that makes sense!


See More: excel vba copy from one sheet to 2nd

July 29, 2011 at 21:52:55
There appears to be some specifics missing from your request:

For one thing, you don't say what Row in Sheet 2 Column B the data to be copied begins.

What do you want to happen if you enter 20 in one instance and now have data in Sheet1!B15:B34, then you enter 15 the next time? Should all of the old data be cleared or should the data in B15:29 be overwritten and the data on B31:B34 left alone?

If the old data should be cleared, will there be any data below the pasted data that should not be cleared or can the code simply delete all dat from B15 and below?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

July 29, 2011 at 22:32:39
Hi DerbyDad03
Thank you for replying and sorry my question is unclear.

In sheet 2 the list is fixed at 60 numbers in column B, starting at row 3. So cell B3 contains 1, B4 =2 and so on.

You have helped higghlight something I would like to create in this appliaction when you mention the situation where the variable in Sheet 1 changes.

OK lets look at the bigger picture..

There could be dozens of sheets (and I would need to create a new sheet and name each one specifically based on a reference text in another cell which is created via a user form). However there would be only one sheet 2 which would have the name 'ways' and in that sheet starting at cell B3 would exist the numbers from 1 to 60.

I have a user form as I said and within that form I have a button when pressed which should create a new sheet, the user would then input a name for the sheet such as 'DB1''.

The sheet would be a copy of a blank sheet stored in the file as the template.

The template (which in this case I have refereed to as Sheet 1) contains the prepared input cells.

One of these cells, B10 is the input cell for referencing our Sheet 2 or ;Ways'. So when the user reaches this cell on the user form they input a number between 1 and 60. Lets say that number is 20 again.

This reference then asks for the first 20 numbers from Sheet 2 'Ways' to be copied and pasted into the new sheet 1 'DB1' starting at cell B13 in 'Sheet 1' so to speak..

Each new instance oof the created sheet could have different numbers of rows required from sheet 2 and each sheet should be permanent but edoitable so if someone changes thie mind about the number of rows in sheet 1 they could change them, usually by reducing or increasing the number of rows by changing B10 again. However a message should appear if there is data entered in adjacent rows on that sheet.

Please note that there are other lists to copy into Sheet 1 instead of those fron cells B3 down on Sheet 2 which are not strictly numerical as they contain letters as well.

I presented my problem this way to get the basics right first before moving on.

I hope this explanation helps to clarify the initial problem. OMG I may have created a monster here. Would it be easier if I sent you the workbook?



Report •

July 30, 2011 at 07:28:28
The code below should get you started, assuming you know a little bit about VBA programming.

If you want the copy/paste to occur automatically when the user enters data in B10, then you'll need to include a copy of this code in every new sheet that is created. Since a WorksheetChange macro fires when a change is made to the sheet it resides in, the code has to reside in the Sheet Module for that sheet.

Even if you move the main code to a standard module, you would still need a small WorksheetChange macro in each sheet that would call the main module.

BTW... In your OP you said the paste should start in B15, but in your latest post you said B13. I assume that one of those is a typo, but I do need to point out that since VBA code has to very specific in many cases, it helps if you are very careful and proofread your posts for accuracy.

One more point:

I presented my problem this way to get the basics right first before moving on.

This is fine and in some ways makes sense, especially if you are just looking for some basic code that you will be modifying yourself. However, if you are going to be coming back to a Help forum adding little bits of requirements here and there, things will get very frustrating for those that are trying to help you. For a request such as yours, we need to set up a workbook with multiple sheets to test the code but we don't expect these to be long term projects where we need to save the sheets and constantly modify the data and code every time you request a new "feature". Without all of the information upfront, we may head down one path only to find that the code has to be completely re-written as new requirements are added. We're all just volunteers here, you know. ;-)

Anyway, try this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastRow As Integer
Dim Response
'Determine if change was made to B10 and that a number was entered
 If Target.Address = "$B$10" Then
  If IsNumeric(Target) Then
'Disable Events temporarily
   Application.EnableEvents = False
'Warn user if there is "extra" data in Column B
     lastRow = Range("B" & Rows.Count).End(xlUp).Row
      If lastRow > 14 + Target Then
        Response = _
          MsgBox("There is existing data in Column B that" & vbCrLf & _
                 "extends beyond your current request." & vbCrLf & _
                  vbCrLf & "Do you wish to continue?", vbYesNo, "Warning")
            If Response = vbNo Then GoTo Done
      End If
'Copy data if user select Yes
       Sheets("Ways").Range("B3:B" & 2 + Target).Copy _
  End If
 End If
'Re-enable Events
   Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

August 1, 2011 at 11:01:12
ok thanks DerbyDad

That's a great start


Report •

Ask Question