Copy rows based on number of columns which have values

[Kaimer]

Hello,

I am trying to find a macro which can help me huge time at my work.
Everyday I receive multiple excel templates which has different number of items in it. Every item with all attributes is written in one column, so when I receive template with 150 items, the file has 150 columns.
Please is there a macro which would copy all columns and rows which have values into another worksheet within the same file?
I found a lot of macros which are copying all data based on number of rows, but I need to have it the other way so it will copy all rows based on data in columns.

Hope its understandable.
Thank you a lot

 

[aaewalsh]

Hey

Would they be all in the same sheet name in different templates, would there be just one worksheet per template?

 

[Kaimer]

Hello,
There is one worksheet per template. So when I receive the file and has 2 items in it, they are on the same worksheet 1 items starting on C5 and second on D5 and each item has +-50 rows, depends on the template type.

 

[mumps]

Please is there a macro which would copy all columns and rows which have values into another worksheet within the same file?

You say that you receive multiple templates daily. Do you want to copy the data from each template into the same sheet in a different workbook? If so, what is the full name of the destination workbook and what is the name of the destination sheet? Will all the received templates be open at the same time? Please clarify in detail, step by step, what you want to do.

 

[Kaimer]

Sorry that I confused you with multiple templates. Lets forget about that.
What I need is that when I receive one file I need to copy all columns which have values in C5.D5,E5,F5 etc and I need to copy all rows which have values of those columns into another worksheet of that same file.
I have uploaded a file for you guys to have an idea what I mean. Sorry that I cannot share the original file due to policy.

So in the file I have 5 items and I need to copy Columns starting from C5 D5 E5 F5 G5 and all their attributes the same way as they are shown here.
I cannot make it static as every time I receive new template, there is different number of items and different number of attributes.
I do not need to copy the attribute names only the columns with the item name and the values for each attribute
Thank you

 

[mumps]

Change the sheet names (in red) to suit your needs.

Sub CopyColumns()
    Application.ScreenUpdating = False
    Dim lRow As Long, lCol As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    With srcWS
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lCol = .Cells(5, .Columns.Count).End(xlToLeft).Column
        .Range("C5").Resize(lRow - 4, lCol - 2).Copy desWS.Range("A1")
    End With
    Application.ScreenUpdating = True
End Sub

 

[Kaimer]

Oh my god, you guys are legends.
Can I ask for one modification, that the paste would be as values?
There is a lot of formating and colours of cells etc, which I dont move to move. So need to paste values.

 

[mumps]

Try:

Sub CopyColumns()
    Application.ScreenUpdating = False
    Dim lRow As Long, lCol As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    With srcWS
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lCol = .Cells(5, .Columns.Count).End(xlToLeft).Column
        .Range("C5").Resize(lRow - 4, lCol - 2).Copy
        desWS.Range("A1").PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[Kaimer]

This is it!!! Insane how I was trying to figure this out for a week, and you made it so quickly.
Thank you so much

 

[mumps]

You are very welcome.