Find last column with Data and copy

[jl2509]

Hello

I have a data collection form for Financial Audits which is created on a work sheet, rather than have many tabs for the audits, I have decided to Horizontally stack the audits within 1 worksheet. They are for the same company so this is a reasonable method for me.

However, I am trying to add a new audit sheet (Copy of last Audit) automatically via a button with a macro assigned

Any ideas how I can check for the last column with data in it, copy 8 columns backwards including the last column and paste the data, 1 column after the last data column?

ie Last data column is Column P, copy back from Column P to Column I and paste to Column Q

When I do this manually, I select Columns P to Column I, copy the selected columns, select Column Q, and paste

The column references obviously change as more audits are completed.

Appreciate any ideas

Thank you

 

[Fluff]

How about

Sub CopyPasteCol()

    Cells(1, Columns.Count).End(xlToLeft).Offset(, -7).Resize(, 8).EntireColumn.Copy _
    Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)

End Sub

 

[jl2509]

Thank s for the reply Fluff

Looks like it might work but I need to find the last column first of all !


Any ideas how to do this and implement your code to do the copy / paste?

Thank you

 

[Fluff]

The code already does that.
What I should have said, is that it assumes you have data in Row 1.
If row 1 is blank is there a particular row that can be used to determine the last column?

 

[Rick Rothstein]

If row 1 is blank is there a particular row that can be used to determine the last column?

This code line will find the last column displaying a value (whether that value is a constant or from a formula) no matter what row it appears on...

[table="width: 500"]
[tr]
	[td]LastUsedCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column[/td]
[/tr]
[/table]

This code line will find the last column containing a value or formula even if the cell or cells in that column contain a formula displaying the empty text string ("")...

[table="width: 500"]
[tr]
	[td]LastUsedCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column[/td]
[/tr]
[/table]

These code lines can be modified to find the last row by changing the xlByColumns to xlByRows and the .Column to .Row.

 

[jl2509]

Appreciate the responses here guys, and great job

Fluff I misunderstood the code, and your code works if as you say "There is data in row1!
However, my thought process was not about data in a particular row, but as Rick has picked up on, "Data can be in any row"
I dont have formulas in the last column of my report so the xlValues option from Rick should work for me.

Not being very good with VBA, I need some help with the proposals above into a vba sub routine.

How do I combine:

"Find last Column code"

LastUsedCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

with the "copy specific columns and paste" code (omitting the assumption of data in Row 1)

Cells(1, Columns.Count).End(xlToLeft).Offset(, -7).Resize(, 8).EntireColumn.Copy _
Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)

Thank you

 

[Fluff]

OK, try this

Sub CopyPasteCol()

    Dim UsdCols As Long
    
    UsdCols = Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    Cells(1, UsdCols - 7).Resize(, 8).EntireColumn.Copy Cells(1, UsdCols).Offset(, 1)

End Sub

 

[jl2509]

Perfect, much appreciated

 

[Fluff]

Glad we could help & thanks for the feedback

 

[jl2509]

Hi Fluff

I wonder if you could offer some more help please?
From the code you provided below, which worked perfectly, can you make this copy and insert the columns in front of the columns copied?

for example,
Last data column is Column I, copy back from Column I to Column B and insert the copied columns before Column B
However, the search for the last data column used, is now only ever on the first review sheet and the last data column is always column I. Hopefully this makes it easier.

Thank you

[/COLOR][COLOR=#333333]Sub CopyPasteCol()[/COLOR]

[COLOR=#333333]Dim UsdCols As Long[/COLOR]

[COLOR=#333333]UsdCols = Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column[/COLOR]

[COLOR=#333333]Cells(1, UsdCols - 7).Resize(, 8).EntireColumn.Copy Cells(1, UsdCols).Offset(, 1)[/COLOR]

[COLOR=#333333]End Sub
[/COLOR][COLOR=#333333]
[/COLOR]