VBA Formatting Help Needed Urgently!!!!!!!

[Morrissey]

Ok I Have Made This Code That Sends Data From My Invoice System For My Freight forwarding Company To A Sort Of Database Here Is The Code:

Sub Send_To_Database()
Application.ScreenUpdating = False
Sheets("Automated Invoice").[G11].Copy
Sheets("Database").Select
[A65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
Sheets("Automated Invoice").Select
Application.ScreenUpdating = True

Application.ScreenUpdating = False
Sheets("Automated Invoice").[B11].Copy
Sheets("Database").Select
[B65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
Sheets("Automated Invoice").Select
Application.ScreenUpdating = True

Application.ScreenUpdating = False
Sheets("Automated Invoice").[E13].Copy
Sheets("Database").Select
[C65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
Sheets("Automated Invoice").Select
Application.ScreenUpdating = True

Application.ScreenUpdating = False
Sheets("Automated Invoice").[B13].Copy
Sheets("Database").Select
[D65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
Sheets("Automated Invoice").Select
Application.ScreenUpdating = True

Application.ScreenUpdating = False
Sheets("Automated Invoice").[B15].Copy
Sheets("Database").Select
[E65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
Sheets("Automated Invoice").Select
Application.ScreenUpdating = True

Application.ScreenUpdating = False
Sheets("Automated Invoice").[B16].Copy
Sheets("Database").Select
[F65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
Sheets("Automated Invoice").Select
Application.ScreenUpdating = True

Application.ScreenUpdating = False
Sheets("Automated Invoice").[G18].Copy
Sheets("Database").Select
[G65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
Sheets("Automated Invoice").Select
Application.ScreenUpdating = True

Application.ScreenUpdating = False
Sheets("Automated Invoice").[G19].Copy
Sheets("Database").Select
[H65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
Sheets("Automated Invoice").Select
Application.ScreenUpdating = True

Application.ScreenUpdating = False
Sheets("Automated Invoice").[G20].Copy
Sheets("Database").Select
[I65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
Sheets("Automated Invoice").Select
Application.ScreenUpdating = True

Application.ScreenUpdating = False
Sheets("Automated Invoice").[G21].Copy
Sheets("Database").Select
[J65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
Sheets("Automated Invoice").Select
Application.ScreenUpdating = True

Application.ScreenUpdating = False
Sheets("Automated Invoice").[G22].Copy
Sheets("Database").Select
[K65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
Sheets("Automated Invoice").Select
Application.ScreenUpdating = True

Application.ScreenUpdating = False
Sheets("Automated Invoice").[G49].Copy
Sheets("Database").Select
[L65536].End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
Sheets("Automated Invoice").Select
Application.ScreenUpdating = True
End Sub


Ok The Code Works Perfectly However Because The Data Is On My Invoice Tempalte Is Formatted It Transfers Thr Formattted Text On To The Database I Don't Want This To Happen I Would Like It Formatted As 'Arial 10' Does Anyone Know How To Do This If So Help Is Needed Thanks,


(PS that template thing doesn't work for me with the MS add ons as there is various complcations on my invoice system that prevents that so I need to do it this way so please don't suggesst top do that I know this is the way I have to do it!)

Your Pal Mozz

[brettvba]

Not sure but try adding
With ActiveCell.Characters(Start:=1, Length:=14).Font
.Name = "Arial"
.Size = 10

End With
into your code

[Dave Hawley]

Hi Moz

I hope you don't mind me saying, but you are going about this the long way. Try some code like this:

Sub Send_To_Database()
Dim i As Integer
Dim strRange As Range

For i = 1 To 12
    strRange = Choose(i, "G11", "B11", "E13", "B13", "B15", "B16", _
                         "G18", "G19", "G20", "G21", "G22", "G49")
                         
    Sheets("Automated Invoice").Range(strRange).Copy Destination:= _
    Sheets("Database").Range("A65536").End(xlUp).Offset(1, 0)
    With Sheets("Database").Range("A65536").End(xlUp).Font
        .Name = "Arial"
        .Size = 10
    End With
    
Next i

End Sub

Much easier to read and De-bug.

[Morrissey]

Thank You Very Much For Your Help

[Morrissey]

Hi David One Little Problem With Your Code Don't Worry I Thought I Was Going The Long Way Anyway But I Have Replaced Your Code With Mine And When I try To Execute The Code A Debug Error Message Comes Up Saying


"Object Variable With Block Variable Not Set"

and when I go to debug this is what is highlighted


strRange = Choose(i, "G11", "B11", "E13", "B13", "B15", "B16", _
"G18", "G19", "G20", "G21", "G22", "G49")

just a little more help please! we are nearly there!!!

[Morrissey]

someone please

[Morrissey]

come on someone

[Jay Petrulis]

On 2002-04-08 10:46, Morrissey wrote:
come on someone

Hi,

I don't see the problem off hand. Make sure you have the "end with" in the statement at the end.

Please copy and paste your code exactly as you have it. We need to compare if there are any typos or anything missing from Dave's code and what you have.

Bye,
Jay

[Jay Petrulis]

Hi,

There is a type mismatch in the code because strRange is dimensioned as a Range, but then it is used as a String.

Change

Dim strRange As Range
to
Dim strRange As String

and it should work for you.

Bye,
Jay