Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    copying and pasting multiple times (2000)

    All

    I have been asked to improve a macro written by a colleague. The macro currently has multiple if statements (which print out on over 15 pages) one after the other activation of which is dependent upon the ansques month variable entered by the user. The only difference between each of the if statements is the range of cells to be copied and pasted. Please find a copy of one of the if statements below.

    If ansques = "aug 02" Then

    ThisWorkbook.Worksheets("macro calc").Activate
    Application.ScreenUpdating = False
    Sheets("macro calc").Visible = True
    ActiveSheet.unprotect password:="a"
    Sheets("original data").Visible = True
    Sheets("original data").Select
    range("c7:Bg11").Select
    selection.copy
    Sheets("macro calc").Select
    range("c7:bg7").Select
    selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Sheets("original data").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("macro calc").Select
    ActiveSheet.Protect password:="a"
    ActiveWindow.SelectedSheets.Visible = False
    Application.ScreenUpdating = True

    So far I have tried to declare 2 ranges 'copyRange' and 'pasterange' and then use the if statements to assign the ranges to the relevant areas by:

    If ansques = "aug 02" Then
    set copyrange = worksheets("original data"). range("c7:Bg11")
    set pasterange = worksheetsSheets("macro calc").range("c7:bg7")

    At the end of the procedure I have the original statement shown above but now referring to copyrange and pasterange. However this revision has failed miserably as the macro does not recognise my copyrange and pasterange assignments. If I can get these to work, I intend to change the multiple if statements to a select case statement.

    Can anybody explain why my revision does not work and please and give me some advice on how best to proceed.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copying and pasting multiple times (2000)

    There is a lot of selecting and activating going on in the macro. With a few exceptions, you don't need to select ranges to operate on them.

    It seems to me that the following much shorter code should do what you want:

    Application.ScreenUpdating = False
    Worksheets("macro calc").Unprotect Password:="a"
    copyRange.Copy
    pasteRange.PasteSpecial Paste:=xlValues
    Worksheets("macro calc").Protect Password:="a"
    Application.ScreenUpdating = True

    If copyRanfe ans pasteRange are not recognized, check carefully where you have declared them. It must either be in the same procedure where you assign and use them, or at the top of the module, before all functions and procedures.

  3. #3
    Lounger
    Join Date
    Feb 2003
    Location
    Renfrewshire, Scotland
    Posts
    35
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: copying and pasting multiple times (2000)

    Alex,
    Not sure I understand your question but I notice in your set pasterange = worksheetsSheets("macro calc").range("c7:bg7") - you have 'sheets' twice! that shouldn't affect copyrange though.

  4. #4
    Lounger
    Join Date
    Feb 2003
    Location
    Renfrewshire, Scotland
    Posts
    35
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: copying and pasting multiple times (2000)

    Hi Alex,
    Me again. I don't see your ' end if' s, and I'm not sure about your select and paste ranges i.e. you have range("C7:BG11") which you are copying to a different sized range("C7:BG7????). Shouldn't it be the same sized range on both sheets? or am I missing the point?

    DougieW

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying and pasting multiple times (2000)

    DougieW

    the 'endif' is at the end of the multiple if statements

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying and pasting multiple times (2000)

    Dougie

    The duplicate of sheets is just my typo in prepring this message and is not in the program.

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying and pasting multiple times (2000)

    Hans

    All declarations are within the procedure.

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying and pasting multiple times (2000)

    Hans

    I have tried the revision you suggested within a fresh workbook using test data and it runs superbly.

    code shown below for your info:

    Public Sub copyAndPaste()
    Dim copyRange As Range
    Dim pasteRange As Range
    Dim ansques As String

    ansques = "Aug 02"

    If ansques = "Aug 02" Then
    Set copyRange = Worksheets("original data").Range("c7:Bg11")
    Set pasteRange = Worksheets("macro calc").Range("c7:bg11")
    End If

    Application.ScreenUpdating = False
    Worksheets("macro calc").Unprotect Password:="a"
    copyRange.Copy
    pasteRange.PasteSpecial Paste:=xlValues
    Worksheets("macro calc").Protect Password:="a"
    Application.ScreenUpdating = True

    End Sub

    thanks very much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •