Copy range from column to column

[austenr]

I have an EXCEL sheet that is generated by another program that reads a DB. There is a problem with it that sometimes will write a chunk of data in the wrong columns. I cannot get the program that produces it to get corrected so I need a work around. Basically here is what I need:

Data is written in column A, B and C row by row. However sometimes, data is written in columns D,E and F. What I want to do is create a macro that will test for spaces in column A line by line. If Column A is blank then select data in Column D, E and F and paste to Column A, B, and C. The problem is that it is not always the same rows everytime. There is probably some code already that I could modify if someone could point me in the right direction. Thanks in advance. One thing I know about this board, everything always gets solved.

[Ken Puls]

Hi Austen,

What about this?

If ActiveSheet.Range("A1").Value = vbNullString Then
 With Columns("D:F")
     .Copy Columns("A:C")
     .Clear
 End With
 End If

This assumes that you want to copy the entire column over for D:F, and that the data in cell A1 is sufficient to determine that the problem occured.

HTH,

[austenr]

I get a compile error on ("A1"). Invalid outside procedure. Looks right..???

[Jacob Hilderbrand]

How about this:

Option Explicit
 
 Sub Macro1()
Range("D:F").Copy
     Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
         True, Transpose:=False
     Range("A1").Select
     Application.CutCopyMode = False
End Sub

[austenr]

Thanks DRJ. Works great as usual. Curious though, what is the Transpose statement for? That is new to me.

[Jacob Hilderbrand]

You're Welcome

You can actually remove that since it is FALSE. If it was TRUE it would rearrange the data. So if you copy A1:A10 and paste it to A1, but transpose it, the data will be placed in A1:J1.

Similarily if you copy a row and transpose it will put the data down a column.

[austenr]

Great..One other thing, I am reading the posts about calling a macro from another workbook. I am developing a macro for a co-worker that is very EXCEL challenged. She runs a Query on a DB that produces a read-only file. She has to save it. How could she access the above macro easily?

[Zack Barresse]

Hello Austen.

If you are on a network, I'd suggest creating an add-in with the routine in the add-in inside of a standard module. Then just call it something like MyAddin.xla!Module1.MyRoutine

There was another thread regarding this issue recently (I can find a link for it if you'd like) I believe by Ken (kpuls).

And if you're over a network (either a Workgroup or a Domain, both work) just have her Browse for the add-in. It can be installed on multiple pc's and runs very well that way; so multiple people can call the same routine(s).

If you don't want to make it an add-in, I'd suggest exporting the module (in the VBE, right click the Module, select Export To..., save somwhere (Module1.bas) and send the file to her. Then have her Import it from her VBE.

Imho, the add-in is the easiest most hassle free way to go. They are a piece of cake to make and once installed a monkey could run them. (Okay, maybe trained monkeys. )

[Ken Puls]

Hi Austen,

For the record, I've never seen that error before, and it works fine on my PC. I didn't give you the Sub and End Sub, just the guts, and the message would seem to indicate that it's missing the wrappers, but I can't even run it at all without that part... Weird.

I agree with Zack on add-ins. By far the easiest way to go.

The post that Zack was referring to was this one... or was it this one? They're both relevant, I think. The first link is more a general discussion with the different alternatives of where to save your code to make it accessible, while the second gets right into the nitty gritty of exactly how to call the procedure from an add-in or other workbook.

Cheers,

[Jacob Hilderbrand]

You may not need to create a macro for your user if it will be too much trouble. This can be done manually really easily.

Select Col D:F then select A1
Edit | Paste Special
Check the box for skip blanks