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

Morrissey

Board Regular
Joined
Mar 8, 2002
Messages
85
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Not sure but try adding
With ActiveCell.Characters(Start:=1, Length:=14).Font
.Name = "Arial"
.Size = 10

End With
into your code
 
Upvote 0
Hi Moz

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

Code:
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.
 
Upvote 0
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!!!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top