VBA Copy and paste

[DanielMachin]

Hi Guys,

Could someone please help me. I am trying to copy B4, B6, E7, H4, L4, M37 from worksheet1 and paste it into worksheet2 in A, B , C ,D,E,F thus creating a new table.

I need the paste to be values(as there are formulas and drop down boxes etc in sheet1) and look for the next empty line in worksheet2.


Thank You.... I am new to VBA and have a slight headache from trying to work it out myself...lol

 

[dmt32]

Hi,
on way would be to place values from your ranges in to an array & then output the array to sheet 2

See if this does what you want. Place code in standard module:

Sub CopyRange()
    Dim Item As Range, DataEntryRange As Range
    Dim data() As Variant
    Dim LastRow As Long
    Dim i As Integer


    'cells to copy from Data Entry sheet
    Set DataEntryRange = Sheet1.Range("B4,B6,E7,H4,L4,M37")


    For Each Item In DataEntryRange
        'build array
        i = i + 1
        ReDim Preserve data(1 To i)
        'data values to array
        data(i) = Item.Value
    Next
    'output array to sheet2
    With Sheet2
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Cells(LastRow, 1).Resize(1, i).Value = data
    End With
End Sub

I have assumed that your worksheets have their default code names (Sheet1, Sheet2) but adjust as required.

Others here may have alternative suggestions.

Hope Helpful

Dave

 

[DanielMachin]

Thank you... however i am having problems getting it to work as a command. Is there something silly im doing

 

[dmt32]

sorry do not follow - have you placed code in standard module & assigned to a forms button?
Or are you using a commandButton?

Dave

 

[DanielMachin]

I want to use a command button to run the program once the data in sheet1 is ready. I should have stated this from the start. However i did try to place it in a standard module and run it with no luck. Im sorry its all new to me. Ive just started doing tutorials on the whole vba thing... thanks for your paitence

 

[dmt32]

If using a commandbutton set design mode on & double click button & you will be taken to your sheets code page where a code tag that looks like this should appear:

Private Sub CommandButton1_Click()
    
End Sub

You would then place code within CommandButton1_Click procedure which should all end up looking like this:

Private Sub CommandButton1_Click()
    Dim Item As Range, DataEntryRange As Range
    Dim data() As Variant
    Dim LastRow As Long
    Dim i As Integer


    'cells to copy from Data Entry sheet
    Set DataEntryRange = Sheet1.Range("B4,B6,E7,H4,L4,M37")


    For Each Item In DataEntryRange
        'build array
        i = i + 1
        ReDim Preserve data(1 To i)
        'data values to array
        data(i) = Item.Value
    Next
    'output array to sheet2
    With Sheet2
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Cells(LastRow, 1).Resize(1, i).Value = data
    End With
End Sub

Return to your worksheet & ensure that you have taken commandbutton out of design mode & hopefully all should now work for you.

Hope Helpful

Dave

 

[DanielMachin]

Thank You.. However the following error occurred

runtime error 424

object required

and it highlighted the following line in yellow
Set DataEntryRange = ServiceA.Range("B4,B6,E7,H4,L4,M37")


(please note my sheet1 is ServiceA and my sheet2 is ServiceAData.

 

[dmt32]

I was using the sheets code name - If ServiceA & ServiceData are your sheets TAB names then code will need to look like this:

Private Sub CommandButton1_Click()
    Dim Item As Range, DataEntryRange As Range
    Dim data() As Variant
    Dim LastRow As Long
    Dim i As Integer


    'cells to copy from Data Entry sheet
    Set DataEntryRange = Worksheets("ServiceA").Range("B4,B6,E7,H4,L4,M37")


    For Each Item In DataEntryRange
        'build array
        i = i + 1
        ReDim Preserve data(1 To i)
        'data values to array
        data(i) = Item.Value
    Next
    'output array to sheet2
    With Worksheets("ServiceAData")
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Cells(LastRow, 1).Resize(1, i).Value = data
    End With
End Sub

Dave

 

[DanielMachin]

Dave you are a champion.... Thanks for your time. Now i just have to learn what it all means and re apply it on other sheets. Once again thanks