RE: Macro - How do i....? by Malissa
Malissa
Fri May 09 15:46:00 CDT 2008
I put together what you gave me and something isn't working. all it does is
create a new column A (which is what I want it to do) but then thats it.
Sub
Columns("A").Insert
'get LastRow in Column A
LastRow_A = Range("A" & Rows.Count).End(xlUp).Row
'Copy D1 to column A, rows 1 to Last Row
Range("D1").Copy_
Destination = Range("A1:A" & LastRow)
Rows(1).Delete
LastRow_C = Range("C" & Rows.Count).End(xlUp).Row
Row_C_Count = LastRow_C + 1
LastRow_F = Range("F" & Rows.Count).End(xlUp).Row
Range("F1:F" & LastRow_F).Copy
Range("C" & Row_C_Count).PasteSpecial _
SkipBlanks:=True
Columns("F").Delete
End Sub
--
Malissa
"Joel" wrote:
> Sorry, was thinking columns from previous posting.
>
> 'insert column Not sure which column Because
> 'you code started with a column already selected
> Columns("?").Insert
> 'get LastRow in Column A
> LastRow_A = Range("A" & Rows.Count).End(xlUp).Row
> 'Copy D1 to column A, rows 1 to Last Row
> Range("D1").Copy _
> Destination:=Range("A1:A" & LastRow)
> Rows(1).Delete
>
>
> "Malissa" wrote:
>
> > The last line that I put was to delete Row 1 not column A.
> > --
> > Malissa
> >
> >
> > "Joel" wrote:
> >
> > > 3 things
> > >
> > > 1) Once column F is deleted you can't rerun the code a 2nd time. When using
> > > macros, copy the original sheet to a temporary sheet to save your original
> > > data. Then copy back to temporary sheet to the original shee as you develope
> > > your macro. I usually just hightlight the gray cell between Row 1 and Column
> > > A to hight the entrie worksheet. Then go to a tempory worksheet and press
> > > the same gray key and paste. then copy the temp sheet back to the original
> > > as I'm developiong the macro
> > >
> > > 2) Not sure why column F is blank. Is it because it was deleted did the
> > > column change when you inserted a new column.
> > >
> > > 3) I modified you recorded code. I don't think it is exactly what you want.
> > > I didn't know which column to Insert so there is a question mark below.
> > > Change to the correct column. I also think you wanted the Last Row in Column
> > > A, not A14 so I made a simple change. The code was deleteing column A after
> > > you put data into column A. Doesn't make sense.
> > >
> > > 'insert column Not sure which column Because
> > > 'you code started with a column already selected
> > > Columns("?").Insert
> > > 'get LastRow in Column A
> > > LastRow_A = Range("A" & Rows.Count).End(xlUp).Row
> > > 'Copy D1 to column A, rows 1 to Last Row
> > > Range("D1").Copy _
> > > Destination:=Range("A1:A" & LastRow)
> > > 'Do you really want to delete column A
> > > Columns("A").Delete
> > >
> > > If you have more problems post all the code so I can see how you are
> > > progressing.
> > > "Malissa" wrote:
> > >
> > > > I just discovered that the first part doesnt always work.. depending on the
> > > > size of the worksheet. I am seeing that my keystrokes did reference specific
> > > > cells. The part you did adding the delete F is now allowing a blank field
> > > > when pasting into column C.
> > > > --
> > > > Malissa
> > > >
> > > >
> > > > "Joel" wrote:
> > > >
> > > > > Here is a simply methods which I added the delete for column f
> > > > >
> > > > > LastRow_C = Range("C" & Rows.Count).End(xlUp).Row
> > > > > Row_C_Count = LastRow_C + 1
> > > > > LastRow_F = Range("F" & Rows.Count).End(xlUp).Row
> > > > > Range("F1:F" & LastRow_F).Copy
> > > > > Range("C" & Row_C_Count).PasteSpecial _
> > > > > SkipBlanks:=True
> > > > > columns("F").delete
> > > > >
> > > > > "Malissa" wrote:
> > > > >
> > > > > > Joel,
> > > > > >
> > > > > > I tried it and it worked on that worksheet... one more question, what do I
> > > > > > need to do to delete the info from column F after it is moved to column C?
> > > > > >
> > > > > > Thanks,
> > > > > > --
> > > > > > Malissa
> > > > > >
> > > > > >
> > > > > > "Joel" wrote:
> > > > > >
> > > > > > > I start check column F at Row 1. this may need to be changed.
> > > > > > >
> > > > > > > LastRow_C = Range("C" & Rows.Count).End(xlUp).Row
> > > > > > > Row_C_Count = LastRow_C + 1
> > > > > > > LastRow_F = Range("F" & Rows.Count).End(xlUp).Row
> > > > > > > For F_RowCount = 1 To LastRow_F
> > > > > > > If Range("F" & F_RowCount) <> "" Then
> > > > > > > Range("C" & Row_C_Count) = Range("F" & F_RowCount)
> > > > > > > Row_C_Count = Row_C_Count + 1
> > > > > > > End If
> > > > > > > Next F_RowCount
> > > > > > >
> > > > > > > "Malissa" wrote:
> > > > > > >
> > > > > > > > I have a worksheet that starts out like this with 5 columns:
> > > > > > > >
> > > > > > > > A B C D
> > > > > > > > E
> > > > > > > > 1000 0 14630.17 Bank of Amer Disbursement Account
> > > > > > > > 2200 0 250.64 401K Pension Due
> > > > > > > > 6220 5192.31 0 Staff Veterinary Salaries 0
> > > > > > > > 6223 1179.74 0 Veterinary Assistant Wages 157.81
> > > > > > > > 6224 908.5 0 Receptionist Wages 79
> > > > > > > > 6225 4843.19 0 Veterinary Technician Wages 410.98
> > > > > > > > 6227 1346.15 0 Practice Manager Wages 0
> > > > > > > > 6245 262 0 Grooming Payroll Expenses 0
> > > > > > > > 6290 1380 0 Payroll Taxes
> > > > > > > > 6290 0 3525.19 Payroll Taxes-ee
> > > > > > > > 6290 3525.19 0 Payroll Taxes-ee
> > > > > > > > 6315 0 14.4 Disability Insurance
> > > > > > > > 6330 0 342 Medical/Dental Insurance
> > > > > > > > 6355 125.32 0 Retirement Plan Contributions
> > > > > > > >
> > > > > > > > The first part of the marco I have created creates a new column before A and
> > > > > > > > takes the amount in row 1column C and pastes it to all rows that hold
> > > > > > > > information then deletes first row.
> > > > > > > > Looks like this:
> > > > > > > >
> > > > > > > > A B C D E
> > > > > > > > F
> > > > > > > > 14630.17 2200 0 250.64 401K Pension Due
> > > > > > > > 14630.17 6220 5192.31 0 Staff Veterinary Salaries 0
> > > > > > > > 14630.17 6223 1179.74 0 Veterinary Asst Wage 157.81
> > > > > > > > 14630.17 6224 908.5 0 Receptionist Wages 79
> > > > > > > > 14630.17 6225 4843.19 0 Veterinary TechWages 410.98
> > > > > > > > 14630.17 6227 1346.15 0 Practice Manager Wages 0
> > > > > > > > 14630.17 6245 262 0 Grooming Payroll Expenses 0
> > > > > > > > 14630.17 6290 1380 0 Payroll Taxes
> > > > > > > > 14630.17 6290 0 3525.19 Payroll Taxes-ee
> > > > > > > > 14630.17 6290 3525.19 0 Payroll Taxes-ee
> > > > > > > > 14630.17 6315 0 14.4 Disability Insurance
> > > > > > > > 14630.17 6330 0 342 Medical/Dental Insurance
> > > > > > > > 14630.17 6355 125.32 0 Retirement Plan Contributions
> > > > > > > >
> > > > > > > > The next part I am unsure of. What I need to do is take the amounts in
> > > > > > > > column F and move them to the bottom of column C. I also need to eliminate
> > > > > > > > spaces. The catch is that this document may never be exactly the same so it
> > > > > > > > cannot be cell specific.
> > > > > > > >
> > > > > > > > Any suggestions are greatly appreciated. This is my first time working with
> > > > > > > > macros. If you need more info please let me know.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > Thanks,
> > > > > > > > --
> > > > > > > > Malissa