Help with creating a macro needed

[kasbac]

Hi there

I need help writing a macro that will complete the following steps:

• Select the tab ”Overview Q1 2011”
• Select the last column in the sheet containing data (Column A will always be blank and should be disregarded.
• Copy the selected column
• Paste the column into the next column
• Find ”$Q$4:$Q$5000” in the newly pasted data and replace it with "$P$4:$P$5000"
• Select the tab “xxx”
• Copy the cells P1:P2
• Return to the tab ”Overview Q1 2011”
• Paste special values only into the cells in the newly created column in line 2 and 3.

I hope that someone will be able to help me out, if any clarification is needed please let me know.

Thanks in advance

 

[JackDanIce]

I think you can use the macro recorder function to carry out all that you've asked for below.

If you want to do that and then paste the code it gives, happy to review that code and suggest any improvements.

 

[Andrew Poulsom]

Did you try recording a macro while doing it manually?

 

[kasbac]

Thanks for you quick replys.

Unfortuanally I dont think the record function will work as every time I run the macro the last column containing data needs to be identified and the formulas needs to be pasted into the subsequent column so this will change every time i run the macro.

 

[Andrew Poulsom]

It'll get you started. If you are stuck on how to change your code to get the next column post it and someone can amend it.

 

[kasbac]

i have tried recording the macro and it look as follows:

Sub Macro1()

' Macro recorded 28-02-2011

    Sheets("Overview Q1 2011").Select
    Columns("I:I").Select
    Selection.Copy
    Columns("J:J").Select
    ActiveSheet.Paste
    Selection.Replace What:="$Q$4:$Q$5000", Replacement:="$P$4:$P$5000", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Sheets("xxx").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Overview Q1 2011").Select
    Range("J2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

I would very much like to get some help with modifying the above macro so that instead of selecting colum I:I the macro should select the last colum containing data. Following the data should be pasted in to the subsequent coulmn.

Secondly in the end the macro should select line two in the last coulmn containing data instead of J2.

If any things needs clarification please let me know

 

[kasbac]

bump

 

[Andrew Poulsom]

What does your data look like? Is the data in row 1 contiguous from column A to the last column?

 

[kasbac]

row 1 is blank and so is coulm A

from B2 however the data is contiguous to the last column if that can help?

 

[Andrew Poulsom]

Try the untested:

Sub Macro1()
    Dim LastCol As Long
    Sheets("Overview Q1 2011").Select
    LastCol = Range("B2").End(xlToRight).Column
    Cells(1, LastCol).EntireColumn.Copy Cells(1, LastCol + 1)
    Cells(1, LastCol + 1).EntireColumn.Replace What:="$Q$4:$Q$5000", Replacement:="$P$4:$P$5000", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Sheets("xxx").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Overview Q1 2011").Select
    Cells(2, LastCol + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub