Excel VBA Help

[wal_verin]

Hi all i am new to Excel VBA and i need help in coming up with a macro to carry out a time consuming task.

This is what i want the Macro to do when the Update data button is clicked: It should copy the information from certain columns (A,B and F for example) of worksheet 1 and "paste special" this in worksheet 2 and in columns A, B and C.

The tricky part is when pasting this information it should paste the information on every other row and it should not change the format of worksheet 2. At the same time all the figures in column C should move one cell down and have a text in the next row in column B which says "According to the Balance Sheet records". This is how the output should look:

Sheet 1
Column A Column B Column C ...... Column F
1000 Revenue .... 2000
2000 Cost .... 3000

Sheet 2 = Output after Update button is pressed
Column A Column B Column C
1000 Revenue
According to the BS record 2000
empty row empty row empty row
2000 Cost
According to the BS record 3000

In addition to this i will need a button that is able to undo what is copied and another button that adds new rows when a particular cell is selected. For example if i select cell A4 and press the button a new row is inserted in cell A4:AZ4

Thanks a million for helping

Sub Button_update_data()
 
Dim x As Worksheet, y As Worksheet, LastRow&
'Change the link to where you saved your files
Set x = Worksheets("Fortnoxbalans")
Set y = Worksheets("Test_Avst")
 
LastRow = x.Cells.SpecialCells([INDENT]<wbr>xlCellTypeLastCell).Row
 
x.Range("A1:A" & LastRow).Copy y.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
x.Range("B1:B" & LastRow).Copy y.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
x.Range("F1:F" & LastRow).Copy y.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
 
 
Application.CutCopyMode = False
 
End Sub 						
[/INDENT]

 

[wal_verin]

Now a got an extra help code that inserts the rows with the text "According to the BS record" The only problem i have now is to have the figures on column F copied to Column C to fall in line with this inserted rows. The code for this that need some twitch is x.Range("F1:F" & LastRow).Copy y.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) Below is the entire code plus the new added line

Option Explicit
Sub Button_sort_data()
Range("A1:F200", Range("A" & Rows.Count).End(xlUp)).Sort [A11], xlAscending
End Sub
Option Explicit


Sub Button_delete_unwanted()
Dim LR As Long, Found As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
LR = Range("B" & Rows.Count).End(xlUp).Row
LR = Range("C" & Rows.Count).End(xlUp).Row
Set Found = Columns("A").Find(what:="Anläggningstillgångar", LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then Rows(Found.Row & ":" & LR).Delete
End Sub


Sub Button_update_data()


Dim x As Worksheet, y As Worksheet, LastRow&
'Change the link to where you saved your files
Set x = Worksheets("Fortnoxbalans")
Set y = Worksheets("Test_Avst")


LastRow = x.Cells.SpecialCells(xlCellTypeLastCell).Row


x.Range("A1:A" & LastRow).Copy y.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
x.Range("B1:B" & LastRow).Copy y.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
x.Range("F1:F" & LastRow).Copy y.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)


'This Code is responsible for inserting the new rows ans start row 10
Dim TackleRows As Range
Dim Uprows As Long, J As Long


Set TackleRows = ThisWorkbook.Worksheets("Test_Avst").UsedRange
Uprows = TackleRows.Rows.Count
Uprows = Uprows - (Uprows Mod 1)
For J = Uprows To 10 Step -1
Rows(J & ":" & J).Insert Shift:=xlDown
Cells(J, "B").Value = "According to the BS record"
Next J


Application.CutCopyMode = False


End Sub


Million thanks for any help on this