INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBA -- Executing One Macro for Multiple Referenced Rows

VBA -- Executing One Macro for Multiple Referenced Rows

(OP)
Hello,

I am still relatively new to coding and creating macros/code in Excel VBA, so please excuse the lack of knowledge and/or coding language.

I have 750 rows of information with columns staying static. I need to send information in certain cells to another worksheet within the same workbook; however, one worksheet is not in the same format as the other, so a bulk copy/paste will not work here.

I thought perhaps it may be possible for the user to "type" what row number would need to be copied/sent to another worksheet, but I'm having trouble with how to formulate how this may work. I also tried to do this in another tab, "manually" having each part of the macro edit and then having a macro create a macro with the new code, but this is also difficult, if not impossible to execute.

Here is part of my code that works for an individual row (row 9), but each row would need to change, depending on user input:

Sub Line_SendtoST()
' Line_SendtoST Macro

' Copy Tag Number
Sheets("Multi Test").Select
Range("C9").Select
Selection.Copy
Sheets("Single Test").Select
Range("H4:L4").Select
ActiveSheet.Paste

' Copy Pressure & Units
Sheets("Multi Test").Select
Range("O9").Select
Selection.Copy
Sheets("Single Test").Select
Range("L12:O12").Select
ActiveSheet.Paste

Sheets("Multi Test").Select
Range("O8").Select
Selection.Copy
Sheets("Single Test").Select
Range("P12").Select
ActiveSheet.Paste

' Copy Temperature & Units
Sheets("Multi Test").Select
Range("P9").Select
Selection.Copy
Sheets("Single Test").Select
Range("L13:O13").Select
ActiveSheet.Paste

Sheets("Multi Test").Select
Range("P8").Select
Selection.Copy
Sheets("Single Test").Select
Range("P13").Select
ActiveSheet.Paste

End Sub

Any thoughts on how to use relative references, dynamic referencing, or other ways to get a single row to copy over based on user input/selection?

Thank you!

RE: VBA -- Executing One Macro for Multiple Referenced Rows

Hi,

1) Might be beneficial to post a representative sample of your table and the target sheet. Heck, why not upload your workbook?

2) What is significant about row 9 (and row 8) in Multi Test in the context of where its going to Single Test?

3) What is significant about other rows?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA -- Executing One Macro for Multiple Referenced Rows

Hello,

This code replicates your code for Row 9. I have then made some assumptions:

Column C on multi test has data every other row so the next macro copies:

C11 to H15:L15
O11 to L23:O23
O10 to P23
P11 to L24:O24
P10 to P24

CODE

Sub COPY_OVER()
    Application.ScreenUpdating = False
    With Sheets("Multi Test")
        For MY_ROWS = 9 To .Range("C" & .Rows.Count).End(xlUp).Row Step 2
'COPY TAG NUMBER (C9 to H4:L4)
            .Range("C" & MY_ROWS).Copy
            With Sheets("Single Test")
                MY_NEXT_ROW = .Range("L" & .Rows.Count).End(xlUp).Row + 2
                If MY_NEXT_ROW < 4 Then MY_NEXT_ROW = 4
                .Range("H" & MY_NEXT_ROW & ":L" & MY_NEXT_ROW).PasteSpecial (xlPasteValues)
            End With
'COPY PRESSURE & UNITS (O9 to L12:O12)
            .Range("O" & MY_ROWS).Copy
            With Sheets("Single Test")
                MY_NEXT_ROW = .Range("L" & .Rows.Count).End(xlUp).Row + 2
                .Range("L" & MY_NEXT_ROW + 6 & ":O" & MY_NEXT_ROW + 6).PasteSpecial (xlPasteValues)
            End With
'(O8 to P12)
            .Range("O" & MY_ROWS - 1).Copy
            Sheets("Single Test").Range("P" & MY_NEXT_ROW + 6).PasteSpecial (xlPasteValues)
'COPY TEMP & UNITS (P9 to L13:O13)
            .Range("P" & MY_ROWS).Copy
            With Sheets("Single Test")
                MY_NEXT_ROW = .Range("L" & .Rows.Count).End(xlUp).Row
                .Range("L" & MY_NEXT_ROW + 1 & ":O" & MY_NEXT_ROW + 1).PasteSpecial (xlPasteValues)
            End With
'P8 to P13
            .Range("P" & MY_ROWS - 1).Copy
            Sheets("Single Test").Range("P" & MY_NEXT_ROW + 1).PasteSpecial (xlPasteValues)
        Next MY_ROWS
    End With
    Application.ScreenUpdating = True
End Sub 

Post back with your requirements or how this macro is wrong.

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

RE: VBA -- Executing One Macro for Multiple Referenced Rows

(OP)
Hi all,

Thank you for the responses. I figured it would be easiest to upload a snippet of what I am trying to do. I'd like to enter a row on the MultiTest Tab cell D2, and have that row's information be pasted into the Single Test Tab. My current macro (shown above, with slightly altered reference rows) is in this workbook, for Line 1, Row 9. Let me know if this makes it more clear--I really appreciate the help.

Thank you!

RE: VBA -- Executing One Macro for Multiple Referenced Rows

2
yabby24, I changed some stuff in your workbook.

1) Added several Named Ranges to a) make whats happening more understandable and 2) make coding simpler:

Multi Test
SelectedLineNbr: The place in Multi Test where you enter a line number
TagNo: The range of Tag No rows 9-18
Press: The pressure range rows 9-18
Temp: The temperature range rows 9-18

Single Test
Tag_No: The selected tag no
Pressure: The selected pressure
Temperature: The selected temperature

2) removed unused Modules

3) added two pieces of code
first in the Multi Test sheet object added an event that calls your macro whenver you change the SelectedLineNbr...

CODE

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, [SelectedLineNbr]) Is Nothing Then
        Line_SendtoST
        Sheets("Single Test").Activate
    End If
End Sub 

The made your macro simpler

CODE

Option Explicit

Sub Line_SendtoST()
'SkipVought Nov 15, 2016
'Used Named Ranges to define single cells and ranges of data
'the procedure is called by the Worksheet_Change event on Multi Test
'    when the SelectedLineNbr cell is changed

    Dim xl As Application
    
    Set xl = Application
    
    ' Line_SendtoST Macro
    
    [Tag_No.] = xl.Index([TagNo], [SelectedLineNbr], 1)
    
    [Pressure] = xl.Index([Press], [SelectedLineNbr], 1)
    
    [Temperature] = xl.Index([Temp], [SelectedLineNbr], 1)
    
    Set xl = Nothing
 End Sub 

Hope this helps.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA -- Executing One Macro for Multiple Referenced Rows

(OP)
This is exactly what I needed! I didn't know you could use the named ranges in VBA--that made it so much simpler! THANK you so very much for all of your help, I understand so much better now and the larger sheet is fully functional.

Thanks again!

RE: VBA -- Executing One Macro for Multiple Referenced Rows

...and you really didn't need VBA, as you could have used the corresponding formulas directly in each of the cells...

Tag_No.: =INDEX(TagNo,SelectedLineNbr,1)
Pressure: =INDEX(Press,SelectedLineNbr,1)
Temperature: =INDEX(Temp,SelectedLineNbr,1)


However, if you do, you must delete the event code in the Multi Test Sheet Object.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA -- Executing One Macro for Multiple Referenced Rows

So, yabby24, why even have a selection on Multi Test? Why not an in-cell drop down right on Single Test, for instance, in the Tag No. Cell. Just list all the Tag Nos from the Multi Test sheet. When the user selects a Tag No from the drop down, use that value to fill in the blanks for Pressure and Temperature with an INDEX & MATCH. Pretty simple and it keeps the user on the sheet of interest, it would seem to me.

I just can't put some thing down. My mind yearns for better solutions, at least within the scope of my capabilities.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close