Issues With VBA Macro For Transposing Using Named Range

[Legacy 363566]

Hello!

I have been working on this issue all day, so I am hoping someone here is able to help. I am trying to create a VBA script that will allow me to easily complete a copy > paste special (transpose) operation.

1. I need to select a custom range of data
2. Loop through the data by row
2.1 If the cell of the first column is not null I need to check if there is data in the following columns
2.2 If there is data in the following columns, I need all the data to be copied
3. I need to do a special paste of the data, so it can be transposed, below the cell in the first column of the row

Here is an illustration of what I am saying:
https://drive.google.com/open?id=0B7RoI5-NVRVfMEFTRmp2b3lyeEk

Here is what the spreadsheet needs to look like once the script has completed:
https://drive.google.com/open?id=0B7RoI5-NVRVfUDJtMmZQd0h1bjg

Here is my code (I get runtime error):

<code>Sub PhotoColumnsToRows()

'We need to know where the photo URL's are located
Dim rng As Range
Set rng = Application.InputBox(Prompt:= _
            "Please select a range.", _
                Title:="SPECIFY RANGE", Type:=8)


'Now we need to scan the range of data for URL's, skipping blank rows/cells
For Each row In rng
    If Not IsNull(rng.Column) Then
        copyRow = rng.row
        Rows(copyRow & ":" & copyRow).Select
        Selection.Copy

        Sheets("Sheet2").Select
        ActiveSheet.Rows(copyRow).Select
        ActiveSheet.PasteSpecial Transpose:=True
        Sheets("Sheet1").Select
    End If
Next row
End Sub</code>

Thank you in advance!

PS: I am using Excel 2011 on a Mac

 

[humdingaling]

you are getting tangled up in the select and end up selecting a row to pastespecial transpose which doesnt work

i am not sure where you are wanting to paste or how you want it to format

below is an example on how you can avoid using select

            copyRow = rng.Row
            Rows(copyRow & ":" & copyRow).Select
            Selection.Copy
            Sheets("Sheet2").Cells(copyRow, 1).PasteSpecial Transpose:=True

however i dont think your looping the range correctly
as when i ran test data it just loops as row 1 over and over again

 

[StephenCrump]

Welcome to the Forum!

Can you perhaps describe what you are trying to do? The first picture isn't helpful, and the second appears to be locked? A screenshot of before and after would also be helpful.

Just looking at these three lines of code ...

For Each Row In rng
    If Not IsNull(rng.Column) Then
        copyRow = rng.Row

- The loop will actually step cell by cell, but presumably you do want to step by row?
- rng.Column will always return the column number of the first column in rng
- rng.Row will always return the row number of the first row in rng

so it's not clear what the code is meant to be doing.

 

[Legacy 363566]

The code I originally pasted is totally bunk and was part of a test. I attempted to edit the thread to remove it, but there is no option to edit the thread.

I need to paste the selection transposed below the cell of the first column, per my image here: https://drive.google.com/open?id=0B7RoI5-NVRVfUDJtMmZQd0h1bjg
I guess I need to insert new rows first too before pasting.

 

[Legacy 363566]

The long and the short of it is that the data was originally formatted whereas the product images were separated per column. However, the ecommerce platform requires product images to be per row. So there is one column for parsing product images and additional images go on additional rows in the column.

 

[humdingaling]

couldnt open your file but here is what i changed your code to based on a mock up i did based on your Image

Sub PhotoColumnsToRows()

    'We need to know where the photo URL's are located
    Dim rng As Range 'selected range
    Dim Cell As Range 'for use in loop
    
    Set rng = Application.InputBox(Prompt:="Please select a range.", _
                    Title:="SPECIFY RANGE", Type:=8)
    
    'Now we need to scan the range of data for URL's, skipping blank rows/cells
    For Each Cell In rng
        If Not IsEmpty(Cells(Cell.Row, 11)) Then 'checks cell.row and Column J to see if it is empty..IsNull didnt work with my mock up data if it works for you use IsNull
            Rows(Cell.Row).Select
            Selection.Copy
            Sheets("Sheet2").Cells(1, Cell.Row).PasteSpecial Transpose:=True
        End If
    Next
End Sub

 

[humdingaling]

looking at your 2nd image i dont think the above code is what your after

so this is what you start with
https://drive.google.com/open?id=0B7...EFTRmp2b3lyeEk

and this is what you want to end up with?
https://drive.google.com/open?id=0B7...DJtMmZQd0h1bjg


may i ask what the reason for selecting range is?

 

[Legacy 363566]

looking at your 2nd image i dont think the above code is what your after

so this is what you start with
https://drive.google.com/open?id=0B7...EFTRmp2b3lyeEk

and this is what you want to end up with?
https://drive.google.com/open?id=0B7...DJtMmZQd0h1bjg


may i ask what the reason for selecting range is?

Yes, those are essentially before and after pictures.

The reason for selecting a range is so that my headers don't get messed up.

 

[humdingaling]

if i my paraphase what you are after then

Look at Column J for any URL's
if URL is found look to the right to see if there are any other URLS
if some do exist then put underneath the the URL in column J

but only do this for the section you highlight or for the whole thing but just leave your headers alone?

 

[Legacy 363566]

if i my paraphase what you are after then

Look at Column J for any URL's
if URL is found look to the right to see if there are any other URLS
if some do exist then put underneath the the URL in column J

but only do this for the section you highlight or for the whole thing but just leave your headers alone?

When the URLs are pasted, they need to insert a new row.

Here is a sample of what the I am trying to achieve: https://drive.google.com/file/d/0B7RoI5-NVRVfekhKb3dtNko1T2s/view?usp=sharing