Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 115
  • Last Modified:

Find first blank column and paste copied data

Can an expert provide me with VBA that will find the first blank column and then paste the data I have copied

i.e. My Macro will copy data from an existing file [3columns] it will then open another file on a Sheet called Archive Daily

in this sheet I need to find the first blank column and paste the data I have copied.

Many thanks
0
Jagwarman
Asked:
Jagwarman
  • 2
  • 2
1 Solution
 
Hakan YılmazTechnical Office MEP EngineerCommented:
please try this.
it can look for a fixed number of adjacent blank columns, and paste your data to first cell of found range.
Sub StartCode()
    PasteToBlankColumns ThisWorkbook.Sheets("Sheet1"), 1
End Sub

Sub PasteToBlankColumns(ByRef refsheet As Worksheet, ByVal colcnt As Long)
    Dim ic As Long
    Dim checkrange As Range
    refsheet.Activate
    ic = 0
    Do
        ic = ic + 1
        Set checkrange = refsheet.Range(refsheet.Columns(ic), refsheet.Columns(ic + colcnt - 1))
        If Application.WorksheetFunction.CountA(checkrange) = 0 Then
            refsheet.Cells(1, ic).Select
            refsheet.paste
            Exit Do
        End If
    Loop
End Sub

Open in new window

0
 
JagwarmanAuthor Commented:
Hi Hakan Yılmaz I am getting an error at PasteToBlankColumns ThisWorkbook.Sheets("Sheet1"), 1

Subscript out of range (Error 9)

Regards
0
 
Hakan YılmazTechnical Office MEP EngineerCommented:
Do you have a sheet named "Sheet1"?
You can change this line with code below.
This will search blank columns in active sheet of workbook which has this code.
PasteToBlankColumns ThisWorkbook.ActiveSheet, 1

Open in new window

Or this code.
This will search blank columns in application level active sheet.
PasteToBlankColumns Application.ActiveSheet, 1

Open in new window

0
 
JagwarmanAuthor Commented:
great code thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now