excel macro to copy data ind paste into selected column depending on cell number

[goeman]

Hi there

Would really appreciate someones help please.

I have the following in a spreadsheet

Sheet 1
Col A: contains various numbers in the row e.g 23, 97, 254 etc (then Rows B to H contain various data)

A B C D E F G
1
2 23
3 97
4 254
5 588


I the need to look at the numbers in col A (e.g 23) and then copy the data cells B2:G2 and paste into row 23 in Sheet2 and then the same for the next number in col A in Sheet1 (e.g 97) copy data in cells B3:G3 and paste into row 97 in Sheet2

Thanks for your help

 

[My Aswer Is This]

Try this:

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        Range("B" & i & ":" & "G" & i).Copy Sheets(2).Cells(Sheets(1).Cells(i, 1).Value, 1)
    Next
Application.ScreenUpdating = True
End Sub

 

[goeman]

Thanks for the fast reply

but seems macro falls down on :

Range("B" & i & ":" & "G" & i).Copy Sheets(2).Cells(Sheets(1).Cells(i, 1).Value, 1)

 

[My Aswer Is This]

Do you have row numbers in all rows in column "A" starting in Row(1)

so columns A looks like this for example:

12
14
45
34
23


Do you have a Sheet 2 ?

Or do you have text in range ("A1")

 

[goeman]

Sorry my fault

the issue was that I had a text header in first row of Sheet1

removed that and it works perfectly

Thanks again for your help...

 

[My Aswer Is This]

Glad I was able to help you.
If you want to keep the header change this line of code:

For i = 1 To Lastrow

To:

For i = 2 To Lastrow

The 1 means start on row (1)
The 2 means start on row (2)

 

[goeman]

Glad I was able to help you.
If you want to keep the header change this line of code:

For i = 1 To Lastrow

To:

For i = 2 To Lastrow

The 1 means start on row (1)
The 2 means start on row (2)

excellent thank you

final last question please.... if I wanted to rename Sheet1 to 'Input' and Sheet2 to 'Data'?

 

[My Aswer Is This]

If your going to run the script from the Sheet named "Input" no change is needed for that part.

Just change this line of code:

Range("B" & i & ":" & "G" & i).Copy Sheets("Data").Cells(Sheets(1).Cells(i, 1).Value, 1)


If your not going to be running the script from sheet("Input") then there would have to be several more changes.

I do not have access to Excel at this time and will have to get back to you.

This is one reason it's always best to point out all these details in your original post.

 

[My Aswer Is This]

Try this:

Note sheet names and starts on row(2)

Sub Test()
'Modified 1-18-18 10:32 AM EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Sheets("Input").Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To Lastrow
        Range("B" & i & ":" & "G" & i).Copy Sheets("Data").Cells(Sheets("Input").Cells(i, 1).Value, 1)
    Next
Application.ScreenUpdating = True
End Sub

 

[goeman]

thanks for your help... and works great on large amounts of data as well