Need help trying to copy a sheet paste to another and make it work the way i need. I recorded what im trying to do and i can explain more.

zone709 · Oct 20, 2016

Yes I need a lot of cleaning up on this. I took all the scrolling out.

Code:

Sub Macro1()
'
' Macro1 Macro
'
'
    Cells.Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("G23").Select
    ActiveWindow.SmallScroll Down:=-21
    Rows("1:88").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    ActiveWindow.SmallScroll Down:=-12
    Rows("26:37").Select
    Selection.Delete Shift:=xlUp
    ActiveWindow.SmallScroll Down:=30
    Rows("51:62").Select
    Selection.Delete Shift:=xlUp
    ActiveWindow.SmallScroll Down:=27
    Rows("76:76").Select
    Selection.Delete Shift:=xlUp
    ActiveWindow.SmallScroll Down:=-6
    Columns("A:A").EntireColumn.AutoFit
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.SmallScroll Down:=-81
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.RowHeight = 27.75
    ActiveWindow.ScrollColumn = 2
    Range("AE1").Select
    ActiveCell.FormulaR1C1 = "Wrk."
    Range("AF1").Select
    ActiveCell.FormulaR1C1 = "Hrs "
    Range("AG1").Select
    ActiveCell.FormulaR1C1 = "Total"
    Range("AH2").Select
    Range("AI2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-5]:RC[-2])"
    Range("AI2").Select
    Selection.AutoFill Destination:=Range("AI2:AI94"), Type:=xlFillDefault
    Range("AI2:AI95").Select
    Selection.Style = "Currency"
    Cells.Select
    Range("D1").Activate
    Selection.Font.Bold = True
    With Selection.Font
        .Name = "Times New Roman"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .Color = -16777216
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Columns("D:D").Select
    ActiveWindow.SmallScroll Down:=72
    Range("A1:AI95").Select
    Range("AI95").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("A1").Select
    ActiveWindow.SmallScroll Down:=-30
End Sub

What I do in this first image is nothing. So I copy it to another sheet and fix it like I need. So second sheet what I do is I scroll down to the word DROP on line 88 delete all that then in between NN and Drop I delete that also and then towards the last data towards end its only NN as reference to delete that line. Then I do everything else in the code. The yellow columns I highlighted cause I would like to delete the empty rows. Basically any column with no data delete. Then as you see in code with the formula I put it in AI then drag down to last data row with data. Basically I need to do all this. "If data in col A then this code I recorded. Any help would be great. Yes formula always starts from AD to AH.
Excel 2010 32 bit

	A	B	E	F	G	K	L	S	V	Z	AA	AB	AD	AE	AF	AG	AH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77	NN
78	Employee
79	Job
80	Contract
81	Class
82	Regular Hrs
83	OT Hrs
84	Total Hrs
85	Wrk. Dues
86	Add'l Dues
87	PAC
88	Drop
89	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		J	38.50	0.00		38.50		$0.00	$0.00	$0.00
90	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		J	40.00	0.00		40.00		$34.01	$22.00	$0.00
91	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		J	40.00	11.00		51.00		$46.30	$28.05	$0.00
92	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		J	32.00	0.00		32.00		$0.00	$0.00	$0.00
93	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		F	40.00	0.00		40.00		$37.51	$22.00	$0.00
94	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		PV	40.00	0.00		40.00		$0.00	$0.00	$0.00
95	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		J	38.50	0.00		38.50		$34.95	$21.18	$1.93
96	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		PV	32.00	0.00		32.00		$17.56	$17.60	$1.60
97	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		F	40.00	2.00		42.00		$43.54	$23.10	$2.10
98	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		J	40.00	0.00		40.00		$0.00	$0.00	$0.00
99	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		J	34.00	0.00		34.00		$30.87	$18.70	$1.70
100	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		F	40.00	21.50		61.50		$57.67	$33.83	$0.00
101	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		UT	40.00	0.00		40.00		$14.69	$22.00	$0.00
102	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		PV	13.00	0.00		13.00		$7.13	$7.15	$0.65
103	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		J	40.00	0.00		40.00		$36.31	$22.00	$0.00
104	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		J	40.00	0.00		40.00		$36.31	$22.00	$2.00
105	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		J	40.00	8.00		48.00		$43.57	$26.40	$2.40
106	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		J	24.00	0.00		24.00		$21.79	$13.20	$1.20
107	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		J	40.00	0.00		40.00		$36.31	$22.00	$0.00
108	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		J	40.00	11.00		51.00		$46.30	$28.05	$0.00
109	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		J	24.00	0.00		24.00		$24.16	$13.20	$1.20
110	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		J	40.00	1.00		41.00		$37.22	$22.55	$2.05
111	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		F	26.00	0.00		26.00		$26.95	$14.30	$0.00
112	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		J	40.00	1.00		41.00		$0.00	$0.00	$0.00
113	54646	LARRY		FC-0019293 - 300 West Street		062123-Cement		F	40.00	1.00		41.00		$0.00	$0.00	$0.00
114	NN
115	Employee
116	Job
117	Contract
118	Class
119	Regular Hrs
120	OT Hrs
121	Total Hrs
122	Wrk. Dues
123	Add'l Dues
124	PAC
125	Drop
126	265466	THOMAS		GF-011215215 650 West street		232655-Cement		F	40.00	3.00		43.00		$44.57	$23.65	$0.00
127	265466	THOMAS			GF-011215215 650 West street		232655-Cement	F	0.00	8.00		8.00		$8.29	$4.40	$0.00
128	265466	THOMAS			GF-011215215 650 West street		232655-Cement	UT	17.00	0.00		17.00		$6.24	$9.35	$0.00
129	265466	THOMAS			GF-011215215 650 West street		232655-Cement	PV	32.00	0.00		32.00		$17.56	$17.60	$1.60
130	265466	THOMAS			GF-011215215 650 West street		232655-Cement	PV	32.00	0.00		32.00		$17.56	$17.60	$1.60
131	265466	THOMAS			GF-011215215 650 West street		232655-Cement	J	37.00	0.00		37.00		$33.59	$20.35	$1.85
132	265466	THOMAS			GF-011215215 650 West street		232655-Cement	J	3.00	5.00		8.00		$7.26	$4.40	$0.40
133	265466	THOMAS			GF-011215215 650 West street		232655-Cement	J	8.00	0.00		8.00		$8.05	$4.40	$0.40
134	265466	THOMAS			GF-011215215 650 West street		232655-Cement	J	32.00	0.00		32.00		$32.21	$17.60	$1.60
135	265466	THOMAS			GF-011215215 650 West street		232655-Cement	J	40.00	0.00		40.00		$36.31	$22.00	$0.00
136	265466	THOMAS			GF-011215215 650 West street		232655-Cement	PV	40.00	0.00		40.00		$21.95	$22.00	$2.00
137	265466	THOMAS			GF-011215215 650 West street		232655-Cement	J	32.00	0.00		32.00		$0.00	$0.00	$0.00
138	265466	THOMAS			GF-011215215 650 West street		232655-Cement	J	40.00	12.00		52.00		$0.00	$0.00	$0.00
139	265466	THOMAS			GF-011215215 650 West street		232655-Cement	GF	40.00	7.00		47.00		$0.00	$0.00	$0.00
140	265466	THOMAS			GF-011215215 650 West street		232655-Cement	J	32.00	0.00		32.00		$29.05	$17.60	$0.00
141	265466	THOMAS			GF-011215215 650 West street		232655-Cement	J	40.00	0.00		40.00		$36.31	$22.00	$2.00
142	265466	THOMAS			GF-011215215 650 West street		232655-Cement	J	32.00	0.00		32.00		$29.05	$17.60	$1.60
143	265466	THOMAS			GF-011215215 650 West street		232655-Cement	PV	32.00	0.00		32.00		$0.00	$0.00	$0.00
144	265466	THOMAS			GF-011215215 650 West street		232655-Cement	J	24.00	0.00		24.00		$21.79	$13.20	$1.20
145	265466	THOMAS			GF-011215215 650 West street		232655-Cement	UT	40.00	0.00		40.00		$14.69	$22.00	$2.00
146	265466	THOMAS			GF-011215215 650 West street		232655-Cement	F	40.00	1.00		41.00		$38.45	$22.55	$0.00
147	265466	THOMAS			GF-011215215 650 West street		232655-Cement	PV	40.00	0.00		40.00		$21.95	$22.00	$2.00
148	265466	THOMAS			GF-011215215 650 West street		232655-Cement	PV	40.00	0.00		40.00		$0.00	$0.00	$0.00
149	265466	THOMAS			GF-011215215 650 West street		232655-Cement	PV	40.00	0.00		40.00		$21.95	$22.00	$2.00
150	265466	THOMAS			GF-011215215 650 West street		232655-Cement	A1	39.00	0.00		39.00		$12.74	$9.75	$1.95
151	NN
152	Employee
153	Job
154	Contract
155	Class
156	Regular Hrs
157	OT Hrs
158	Total Hrs
159	Wrk. Dues
160	Add'l Dues
161	PAC
162	Drop
163	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	PV	40.00	0.00		40.00		$0.00	$0.00	$0.00
164	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	F	40.00	16.00		56.00		$0.00	$0.00	$0.00
165	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	40.00	1.00		41.00		$37.22	$22.55	$2.05
166	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	24.00	0.00		24.00		$21.79	$13.20	$0.00
167	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	16.00	0.00		16.00		$16.11	$8.80	$0.80
168	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	24.00	0.00		24.00		$24.16	$13.20	$1.20
169	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	PV	40.00	0.00		40.00		$21.95	$22.00	$2.00
170	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	40.00	1.00		41.00		$37.22	$22.55	$2.05
171	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	UT	9.00	0.00		9.00		$3.30	$4.95	$0.45
172	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	PV	38.00	0.00		38.00		$20.85	$20.90	$1.90
173	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	F	40.00	1.00		41.00		$38.45	$22.55	$0.00
174	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	F	0.00	8.00		8.00		$7.50	$4.40	$0.00
175	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	GF	40.00	6.00		46.00		$44.52	$25.30	$2.30
176	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	40.00	0.00		40.00		$36.31	$22.00	$0.00
177	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	PV	32.00	0.00		32.00		$17.56	$17.60	$1.60
178	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	F	40.00	14.50		54.50		$56.49	$29.98	$0.00
179	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	40.00	8.00		48.00		$48.32	$26.40	$2.40
180	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	PV	24.00	0.00		24.00		$13.17	$13.20	$1.20
181	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	PV	32.00	0.00		32.00		$17.56	$17.60	$1.60
182	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	UT	40.00	0.00		40.00		$14.69	$22.00	$2.00
183	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	40.00	0.00		40.00		$36.31	$22.00	$0.00
184	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	PV	40.00	0.00		40.00		$21.95	$22.00	$2.00
185	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	40.00	0.00		40.00		$36.31	$22.00	$0.00
186	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	40.00	1.00		41.00		$41.27	$22.55	$2.05
187	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	F	40.00	1.00		41.00		$38.45	$22.55	$0.00
188	NN	Employee	Job			Contract		Class	Regular Hrs	OT Hrs	Total Hrs		Wrk. Dues		Add'l Dues	PAC	Drop
189	3265646	ROBERT	DEPT-65612516			65498-LOAD		PV	24.00	0.00	24.00		$13.17		$13.20	$1.20
190	3265646	ROBERT	DEPT-65612517			65498-LOAD		J	40.00	0.00	40.00		$0.00		$0.00	$0.00
191	3265646	ROBERT	DEPT-65612518			65498-LOAD		F	40.00	0.00	40.00		$41.46		$22.00	$2.00
192	3265646	ROBERT	DEPT-65612519			65498-LOAD		A3	37.50	0.00	37.50		$24.92		$9.38	$1.88
193	3265646	ROBERT	DEPT-65612520			65498-LOAD		J	40.00	0.00	40.00		$0.00		$0.00	$0.00
194	3265646	ROBERT	DEPT-65612521			65498-LOAD		J	24.00	0.00	24.00		$21.79		$13.20	$0.00
195	3265646	ROBERT	DEPT-65612522			65498-LOAD		J	8.00	0.00	8.00		$7.26		$4.40	$0.00
196	3265646	ROBERT	DEPT-65612523			65498-LOAD		J	33.00	0.00	33.00		$29.96		$18.15	$1.65
197	3265646	ROBERT	DEPT-65612524			65498-LOAD		PV	40.00	0.00	40.00		$21.95		$22.00	$2.00
198	3265646	ROBERT	DEPT-65612525			65498-LOAD		GF	40.00	0.00	40.00		$38.71		$22.00	$0.00
199	3265646	ROBERT	DEPT-65612526			65498-LOAD		J	32.00	0.00	32.00		$29.05		$17.60	$1.60
200	3265646	ROBERT	DEPT-65612527			65498-LOAD		PV	24.00	0.00	24.00		$13.17		$13.20	$1.20
201	3265646	ROBERT	DEPT-65612528			65498-LOAD		J	40.00	2.00	42.00		$42.28		$23.10	$2.10
202	3265646	ROBERT	DEPT-65612529			65498-LOAD		UT	24.00	0.00	24.00		$8.81		$13.20	$1.20
203	3265646	ROBERT	DEPT-65612530			65498-LOAD		J	40.00	10.00	50.00		$45.39		$27.50	$2.50
204	3265646	ROBERT	DEPT-65612531			65498-LOAD		J	40.00	11.00	51.00		$46.30		$28.05	$0.00
205	3265646	ROBERT	DEPT-65612532			65498-LOAD		J	24.00	0.00	24.00		$0.00		$0.00	$0.00
206	3265646	ROBERT	DEPT-65612533			65498-LOAD		J	8.00	0.00	8.00		$7.26		$4.40	$0.40
207									3,091.50	173.00	3,264.50		$2,135.66		$1,423.02	$82.36

Sheet: Table 1

Results:

Excel 2010 32 bit

	A	B	E	F	G	K	L	S	V	Z	AA	AB	AD	AE	AF	AG	AI
1														Wrk.	Hrs	Total
2	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		J	38.5	0		38.5		0	0	0	$ -
3	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		J	40	0		40		34.01	22	0	$ 56.01
4	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		J	40	11		51		46.3	28.05	0	$ 74.35
5	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		J	32	0		32		0	0	0	$ -
6	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		F	40	0		40		37.51	22	0	$ 59.51
7	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		PV	40	0		40		0	0	0	$ -
8	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		J	38.5	0		38.5		34.95	21.18	1.93	$ 58.06
9	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		PV	32	0		32		17.56	17.6	1.6	$ 36.76
10	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		F	40	2		42		43.54	23.1	2.1	$ 68.74
11	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		J	40	0		40		0	0	0	$ -
12	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		J	34	0		34		30.87	18.7	1.7	$ 51.27
13	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		F	40	21.5		61.5		57.67	33.83	0	$ 91.50
14	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		UT	40	0		40		14.69	22	0	$ 36.69
15	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		PV	13	0		13		7.13	7.15	0.65	$ 14.93
16	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		J	40	0		40		36.31	22	0	$ 58.31
17	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		J	40	0		40		36.31	22	2	$ 60.31
18	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		J	40	8		48		43.57	26.4	2.4	$ 72.37
19	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		J	24	0		24		21.79	13.2	1.2	$ 36.19
20	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		J	40	0		40		36.31	22	0	$ 58.31
21	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		J	40	11		51		46.3	28.05	0	$ 74.35
22	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		J	24	0		24		24.16	13.2	1.2	$ 38.56
23	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		J	40	1		41		37.22	22.55	2.05	$ 61.82
24	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		F	26	0		26		26.95	14.3	0	$ 41.25
25	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		J	40	1		41		0	0	0	$ -
26	54646	Larry		FC-0019293 - 300 West Street		062123-Cement		F	40	1		41		0	0	0	$ -
27	265466	Thomas		GF-011215215 650 West street		232655-Cement		F	40	3		43		44.57	23.65	0	$ 68.22
28	265466	Thomas			GF-011215215 650 West street		232655-Cement	F	0	8		8		8.29	4.4	0	$ 12.69
29	265466	Thomas			GF-011215215 650 West street		232655-Cement	UT	17	0		17		6.24	9.35	0	$ 15.59
30	265466	Thomas			GF-011215215 650 West street		232655-Cement	PV	32	0		32		17.56	17.6	1.6	$ 36.76
31	265466	Thomas			GF-011215215 650 West street		232655-Cement	PV	32	0		32		17.56	17.6	1.6	$ 36.76
32	265466	Thomas			GF-011215215 650 West street		232655-Cement	J	37	0		37		33.59	20.35	1.85	$ 55.79
33	265466	Thomas			GF-011215215 650 West street		232655-Cement	J	3	5		8		7.26	4.4	0.4	$ 12.06
34	265466	Thomas			GF-011215215 650 West street		232655-Cement	J	8	0		8		8.05	4.4	0.4	$ 12.85
35	265466	Thomas			GF-011215215 650 West street		232655-Cement	J	32	0		32		32.21	17.6	1.6	$ 51.41
36	265466	Thomas			GF-011215215 650 West street		232655-Cement	J	40	0		40		36.31	22	0	$ 58.31
37	265466	Thomas			GF-011215215 650 West street		232655-Cement	PV	40	0		40		21.95	22	2	$ 45.95
38	265466	Thomas			GF-011215215 650 West street		232655-Cement	J	32	0		32		0	0	0	$ -
39	265466	Thomas			GF-011215215 650 West street		232655-Cement	J	40	12		52		0	0	0	$ -
40	265466	Thomas			GF-011215215 650 West street		232655-Cement	GF	40	7		47		0	0	0	$ -
41	265466	Thomas			GF-011215215 650 West street		232655-Cement	J	32	0		32		29.05	17.6	0	$ 46.65
42	265466	Thomas			GF-011215215 650 West street		232655-Cement	J	40	0		40		36.31	22	2	$ 60.31
43	265466	Thomas			GF-011215215 650 West street		232655-Cement	J	32	0		32		29.05	17.6	1.6	$ 48.25
44	265466	Thomas			GF-011215215 650 West street		232655-Cement	PV	32	0		32		0	0	0	$ -
45	265466	Thomas			GF-011215215 650 West street		232655-Cement	J	24	0		24		21.79	13.2	1.2	$ 36.19
46	265466	Thomas			GF-011215215 650 West street		232655-Cement	UT	40	0		40		14.69	22	2	$ 38.69
47	265466	Thomas			GF-011215215 650 West street		232655-Cement	F	40	1		41		38.45	22.55	0	$ 61.00
48	265466	Thomas			GF-011215215 650 West street		232655-Cement	PV	40	0		40		21.95	22	2	$ 45.95
49	265466	Thomas			GF-011215215 650 West street		232655-Cement	PV	40	0		40		0	0	0	$ -
50	265466	Thomas			GF-011215215 650 West street		232655-Cement	PV	40	0		40		21.95	22	2	$ 45.95
51	265466	Thomas			GF-011215215 650 West street		232655-Cement	A1	39	0		39		12.74	9.75	1.95	$ 24.44
52	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	PV	40	0		40		0	0	0	$ -
53	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	F	40	16		56		0	0	0	$ -
54	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	40	1		41		37.22	22.55	2.05	$ 61.82
55	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	24	0		24		21.79	13.2	0	$ 34.99
56	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	16	0		16		16.11	8.8	0.8	$ 25.71
57	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	24	0		24		24.16	13.2	1.2	$ 38.56
58	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	PV	40	0		40		21.95	22	2	$ 45.95
59	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	40	1		41		37.22	22.55	2.05	$ 61.82
60	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	UT	9	0		9		3.3	4.95	0.45	$ 8.70
61	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	PV	38	0		38		20.85	20.9	1.9	$ 43.65
62	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	F	40	1		41		38.45	22.55	0	$ 61.00
63	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	F	0	8		8		7.5	4.4	0	$ 11.90
64	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	GF	40	6		46		44.52	25.3	2.3	$ 72.12
65	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	40	0		40		36.31	22	0	$ 58.31
66	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	PV	32	0		32		17.56	17.6	1.6	$ 36.76
67	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	F	40	14.5		54.5		56.49	29.98	0	$ 86.47
68	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	40	8		48		48.32	26.4	2.4	$ 77.12
69	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	PV	24	0		24		13.17	13.2	1.2	$ 27.57
70	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	PV	32	0		32		17.56	17.6	1.6	$ 36.76
71	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	UT	40	0		40		14.69	22	2	$ 38.69
72	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	40	0		40		36.31	22	0	$ 58.31
73	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	PV	40	0		40		21.95	22	2	$ 45.95
74	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	40	0		40		36.31	22	0	$ 58.31
75	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	J	40	1		41		41.27	22.55	2.05	$ 65.87
76	456645642	MATT			HG-2151512105 500 PACIFIIC STREET		1213215-LARRY	F	40	1		41		38.45	22.55	0	$ 61.00
77	3265646	ROBERT	DEPT-65612516			65498-LOAD		PV	24	0	24		13.17		13.2	1.2	$ 27.57
78	3265646	ROBERT	DEPT-65612517			65498-LOAD		J	40	0	40		0		0	0	$ -
79	3265646	ROBERT	DEPT-65612518			65498-LOAD		F	40	0	40		41.46		22	2	$ 65.46
80	3265646	ROBERT	DEPT-65612519			65498-LOAD		A3	37.5	0	37.5		24.92		9.38	1.88	$ 36.18
81	3265646	ROBERT	DEPT-65612520			65498-LOAD		J	40	0	40		0		0	0	$ -
82	3265646	ROBERT	DEPT-65612521			65498-LOAD		J	24	0	24		21.79		13.2	0	$ 34.99
83	3265646	ROBERT	DEPT-65612522			65498-LOAD		J	8	0	8		7.26		4.4	0	$ 11.66
84	3265646	ROBERT	DEPT-65612523			65498-LOAD		J	33	0	33		29.96		18.15	1.65	$ 49.76
85	3265646	ROBERT	DEPT-65612524			65498-LOAD		PV	40	0	40		21.95		22	2	$ 45.95
86	3265646	ROBERT	DEPT-65612525			65498-LOAD		GF	40	0	40		38.71		22	0	$ 60.71
87	3265646	ROBERT	DEPT-65612526			65498-LOAD		J	32	0	32		29.05		17.6	1.6	$ 48.25
88	3265646	ROBERT	DEPT-65612527			65498-LOAD		PV	24	0	24		13.17		13.2	1.2	$ 27.57
89	3265646	ROBERT	DEPT-65612528			65498-LOAD		J	40	2	42		42.28		23.1	2.1	$ 67.48
90	3265646	ROBERT	DEPT-65612529			65498-LOAD		UT	24	0	24		8.81		13.2	1.2	$ 23.21
91	3265646	ROBERT	DEPT-65612530			65498-LOAD		J	40	10	50		45.39		27.5	2.5	$ 75.39
92	3265646	ROBERT	DEPT-65612531			65498-LOAD		J	40	11	51		46.3		28.05	0	$ 74.35
93	3265646	ROBERT	DEPT-65612532			65498-LOAD		J	24	0	24		0		0	0	$ -
94	3265646	ROBERT	DEPT-65612533			65498-LOAD		J	8	0	8		7.26		4.4	0.4	$ 12.06

Sheet: Sheet1

My Aswer Is This · Oct 20, 2016

Maybe you could just explain in simple terms what your wanting to do.

Do not tell us how you want it done. Just tell us what you have now and what you want the end result to be.
Then maybe I could help you and please do not say "read my code and you will see what I want".

zone709 · Oct 20, 2016

Yeah sorry about that trying to see where I said that read my codeand see what I want. Doesn't sound right sorry. Its what im trying to see if I can get done.

The first image is the results from a PDF.
So then I copy it to another sheet and paste special it 123. Why because on first sheet it has merged cells and etc. So copying it on another sheet paste special it goes into normal cells. Then I delete from line 1 to where the word said DROp line 88. Sometimes it can be line 89. So im thinking reference the word DROP. Then I delete more what I don't need From NN to DRop line 114 to 125. Then again,then in line 188 NN delete. Now I have a full sheet on names and data from line 1 to 94 except line 1 is for headers I added then I ad formula in AI drag it down to last row of data. Border everything, Bold it, font 10 and basically done. That's what I need to try to do. Again sorry about read my code yeah your right sounds off. Thanks

zone709 · Oct 20, 2016

zone709 said:
Yeah sorry about that trying to see where I said that read my codeand see what I want. Doesn't sound right sorry. Its what im trying to see if I can get done.

The first image is the results from a PDF.
So then I copy it to another sheet and paste special it 123. Why because on first sheet it has merged cells and etc. So copying it on another sheet paste special it goes into normal cells. Then I delete from line 1 to where the word said DROp line 88. Sometimes it can be line 89. So im thinking reference the word DROP. Then I delete more what I don't need From NN to DRop line 114 to 125. Then again,then in line 188 NN delete. Now I have a full sheet on names and data from line 1 to 94 except line 1 is for headers I added then I ad formula in AI drag it down to last row of data. Border everything, Bold it, font 10 and basically done. That's what I need to try to do. Again sorry about read my code yeah your right sounds off. Thanks

Hi I can't or don't know how to explain the end result without explaining what I'm doing on the last response. Hope I explain it right

My Aswer Is This · Oct 21, 2016

I did not mean to say you did that. It's just that sometimes when I ask Posters questions they come back and say "Just read my code and you should understand what I want". I just had a poster yesterday tell me. "I explained very clearly in my original post what I want" When posters say that I just unfollow that posting and move on to help someone else.

Michael M · Oct 21, 2016

Are you simply trying to delete ALL rows that don't start with a number in Column "A" ??
Then color All columns that are completely blank ??

zone709 · Oct 21, 2016

Michael M said:
Are you simply trying to delete ALL rows that don't start with a number in Column "A" ??
Then color All columns that are completely blank ??

Actually yeah trying to delete all rows that don't start with a number. Your right but the color I highlighted idid that cause I want to delete those columns that are blank in them. As you can see there is no data in those cells. Then the rest of the code I recorded show where I add my headers and formula and drag that down.

zone709 · Oct 21, 2016

Hi i am gonna start a new post and delete this one soon. i think i made this more of what it is. I know how to write half the code there is just some little parts i cant get. Thanks

Michael M · Oct 21, 2016

I would suggest NOT starting a new thread just yet !!
You've made it clear what the requirements are AND you have created some of the code.
Post what code you have and we'll take a look at it !!
This will delete the Text / Blank rows...and blank columns....but the code is UNTESTED

Code:

Sub MM1()
Dim lr As Long, r As Long, lc As Integer, c As Integer
lc = Cells(1, Columns.Count).End(xlToLeft).Column
lr = Cells(Rows.Count, "A").End(xlUp).Row
For c = lc To 1 Step -1
    If Application.CountA(Columns(c).EntireColumn) = 0 Then
       Columns(c).Delete
    End If
Next c
For r = lr To 2 Step -1
    If Not IsNumeric(Cells(r, 1).Value) = True Or Cells(r, 1).Value = "" Then
        Rows(r).Delete
    End If
Next r
End Sub

Need help trying to copy a sheet paste to another and make it work the way i need. I recorded what im trying to do and i can explain more.

zone709

Well-known Member

Excel Facts

My Aswer Is This

Well-known Member

zone709

Well-known Member

zone709

Well-known Member

My Aswer Is This

Well-known Member

Michael M

Well-known Member

zone709

Well-known Member

zone709

Well-known Member

Michael M

Well-known Member

Forum statistics

Share this page

Need help trying to copy a sheet paste to another and make it work the way i need. I recorded what im trying to do and i can explain more.

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock