Help with Vba Code

P

pizzute5

New Member
Joined
Feb 23, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I need help with my code. In step 4 my data is getting pasted. Can anyone help? Thanks!!!


Sub AccelaCompliance()



'

' Step1 Macro

'



'

Sheets.Add After:=ActiveSheet

Sheets("Sheet1").Select

Sheets("Sheet1").Name = "Formatted"

Sheets("BH - Board Up Report Full").Select

Cells.Select

Selection.Copy

Sheets("Formatted").Select

Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Rows("1:1").Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlUp

'

' Step2 Macro

'



'

Sheets("BH - Board Up Report Full").Select

ActiveWindow.SelectedSheets.Delete

'

' Step3 Macro

'



'

Range("A:A,B:B,G:G,H:H,J:J,K:K,Q:Q,R:R,S:S,T:T,U:U,V:V,W:W").Select

Range("W1").Activate

Selection.ClearContents

Range("A1").Select



'

' Step4FinalFinal Macro

'



'

ActiveCell.FormulaR1C1 = "=MID(RC[4],1,SEARCH(CHAR(10),RC[4])-1)"

Range("A2").Select

Selection.AutoFill Destination:=Range("A2:A11244"), Type:=xlFillDefault

Range("A2:A11244").Select

Columns("A:A").Select

Range("A11210").Activate

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Selection.Replace What:="#VALUE!", Replacement:="", LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _

ReplaceFormat:=False

'

' Step5 Macro

'



'

ActiveCell.FormulaR1C1 = "Address"

Range("B1").Select

ActiveCell.FormulaR1C1 = "Zip Code"

Range("B2").Select

'

' Step6 Macro

'



'

Range("B2").Select

ActiveCell.FormulaR1C1 = "=RIGHT(RC[3],5)"

Range("B2").Select

Selection.AutoFill Destination:=Range("B2:B11041"), Type:=xlFillDefault

Range("B2:B11041").Select

ActiveWindow.SmallScroll Down:=-15

ActiveWindow.ScrollRow = 10991

ActiveWindow.ScrollRow = 10974

ActiveWindow.ScrollRow = 10940

ActiveWindow.ScrollRow = 10924

ActiveWindow.ScrollRow = 10907

ActiveWindow.ScrollRow = 10321

ActiveWindow.ScrollRow = 10203

ActiveWindow.ScrollRow = 10002

ActiveWindow.ScrollRow = 8143

ActiveWindow.ScrollRow = 7674

ActiveWindow.ScrollRow = 7389

ActiveWindow.ScrollRow = 5529

ActiveWindow.ScrollRow = 5412

ActiveWindow.ScrollRow = 4725

ActiveWindow.ScrollRow = 4608

ActiveWindow.ScrollRow = 4407

ActiveWindow.ScrollRow = 3368

ActiveWindow.ScrollRow = 3184

ActiveWindow.ScrollRow = 3016

ActiveWindow.ScrollRow = 1609

ActiveWindow.ScrollRow = 1559

ActiveWindow.ScrollRow = 1291

ActiveWindow.ScrollRow = 1240

ActiveWindow.ScrollRow = 1190

ActiveWindow.ScrollRow = 637

ActiveWindow.ScrollRow = 553

ActiveWindow.ScrollRow = 403

ActiveWindow.ScrollRow = 34

ActiveWindow.ScrollRow = 1

ActiveWindow.ScrollRow = 51

ActiveWindow.ScrollRow = 285

ActiveWindow.ScrollRow = 336

ActiveWindow.ScrollRow = 403

ActiveWindow.ScrollRow = 419

ActiveWindow.ScrollRow = 403

ActiveWindow.ScrollRow = 336

ActiveWindow.ScrollRow = 319

ActiveWindow.ScrollRow = 202

ActiveWindow.ScrollRow = 185

ActiveWindow.ScrollRow = 151

ActiveWindow.ScrollRow = 118

ActiveWindow.ScrollRow = 51

ActiveWindow.ScrollRow = 34

ActiveWindow.ScrollRow = 1

Columns("B:B").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("A1").Select

Application.CutCopyMode = False

'

' Step7 Macro

'



'

Columns("E:E").Select

Selection.Delete Shift:=xlToLeft

'

' Step8 Macro

'



'

Range("D2:D10637").Select

ActiveWindow.ScrollRow = 10605

ActiveWindow.ScrollRow = 10589

ActiveWindow.ScrollRow = 10555

ActiveWindow.ScrollRow = 10421

ActiveWindow.ScrollRow = 10337

ActiveWindow.ScrollRow = 10203

ActiveWindow.ScrollRow = 7808

ActiveWindow.ScrollRow = 7271

ActiveWindow.ScrollRow = 6719

ActiveWindow.ScrollRow = 2413

ActiveWindow.ScrollRow = 2229

ActiveWindow.ScrollRow = 369

ActiveWindow.ScrollRow = 235

ActiveWindow.ScrollRow = 185

ActiveWindow.ScrollRow = 1

Columns("D:D").Select

Selection.NumberFormat = "000-00-000"

'

' Step9 Macro

'



'

Columns("C:C").Select

Selection.Delete Shift:=xlToLeft

Columns("D:D").Select

Selection.Delete Shift:=xlToLeft

Columns("I:I").Select

Selection.Cut

Columns("D:D").Select

ActiveSheet.Paste

'

' Step10 Macro

'



'

Range("E1").Select

ActiveCell.FormulaR1C1 = "Purchase Order #"

Range("E2").Select

ActiveCell.FormulaR1C1 = _

"=IFS(RC[1]=""A & D ENTERPRISES LLC."",""PS2022*0201"",RC[1]=""AMERICAN GENERAL BUILDERS LLC."",""PS2022*0198"",RC[1]=""AVILES CONSTRUCTION COMPANY INC."",""PS2023*0173"",RC[1]=""INNER CITY DEMOLITION & DISPOSAL LLC"",""PS2023*0174"")"

Range("E2").Select

Selection.AutoFill Destination:=Range("E2:E11041")

Range("E2:E11041").Select

Columns("E:E").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

'

' Step11 Macro

'



'

Columns("M:M").Select

Selection.Replace What:="#", Replacement:="", LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _

ReplaceFormat:=False

'

' Step12 Macro

'



'

Columns("J:J").Select

Selection.Cut

Columns("F:F").Select

Selection.Insert Shift:=xlToRight

Range("F1").Select

ActiveCell.FormulaR1C1 = "Amount"

Columns("G:I").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range("G1").Select

ActiveCell.FormulaR1C1 = "Ward"

Range("H1").Select

ActiveCell.FormulaR1C1 = "No. of Units"

Range("I1").Select

ActiveCell.FormulaR1C1 = "Qty"

Range("J1").Select

ActiveCell.FormulaR1C1 = "Vendor"

Columns("P:P").Select

Selection.Cut

Columns("K:K").Select

ActiveSheet.Paste

ActiveWindow.SmallScroll Down:=-3

Columns("N:O").Select

Selection.Delete Shift:=xlToLeft

Columns("F:F").Select

Selection.Style = "Comma"

Range("A1").Select

Application.CutCopyMode = False

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$K$11041"), , xlYes).Name _

= "Table1"

Range("Table1[#All]").Select

ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium2"

ActiveWindow.SmallScroll Down:=-9

End Sub
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Hi & Welcome to MrExcel.

Can you elaborate as to what exactly you'd like to paste and what issues you are encountering?

If possible could you also post a sample of your data and the desired result?
 
Upvote 0
I have attached a screenshot, I am trying to just have the data till the line break of the address pasted
 

Attachments

  • 1708711596669.png
    1708711596669.png
    40.2 KB · Views: 8
Upvote 0
I'm still not entirely following. By address, do you mean Column E?

Seems like your code was written using the Macro Recorder, If you can share an example of your Raw data(It can be an example without any sensitive information) AND an example of what the desired output would be, it'll be easier for us to come up with alternatives to your existing code. MrExcel has a tool called XL2BB which allows you to paste a copy of your worksheet, so we can copy it on our end and make changes.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
ActiveSheet. Paste error
Replies
1
Views
354
RoryA
RoryA
VinceF
  • Solved
VBA to Copy paste value based on value in cell
Replies
4
Views
490
VinceF
VinceF
C
  • Solved
Automation error The Object invoked has disconnected from its clients.
Replies
7
Views
2K
cstuder
C
D
  • Solved
Insert formula in last column macro
2
Replies
13
Views
268
MARK858
MARK858
S
  • Solved
VBA autofilling code
Replies
6
Views
607
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,990
Messages
6,182,219
Members
453,095
Latest member
SleepyAtlasHQ

Share this page

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