Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Macros in Excel

  1. #1
    Join Date
    Feb 2013
    Posts
    14

    Red face Unanswered: Macros in Excel

    I have a spreadsheet that has several tabs in the spreadsheet. I want to develop a macro that pulls context from cell A1 A2 and A3 and puts them into a table tab in the spreadsheet. The problem is after i create the macro and go to a different tab to try and use it again it copies a1 from this tab and then goes back to the previous tab to get a2 and a3.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Please post the code. It's almost impossible to spot problems in code that you can't see! :P
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Feb 2013
    Posts
    14
    Sub Test()
    '
    ' Test Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+B
    '
    Range("C11").Select
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Jan 3, 2012").Select
    Range("C12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Jan 3, 2012").Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Jan 3, 2012").Select
    Range("H1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    Range("H2").Select
    End Sub

  4. #4
    Join Date
    Feb 2013
    Posts
    14
    Quote Originally Posted by ItGuy1991 View Post
    Sub Test()
    '
    ' Test Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+B
    '
    Range("C11").Select
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Jan 3, 2012").Select
    Range("C12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Jan 3, 2012").Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Jan 3, 2012").Select
    Range("H1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    Range("H2").Select
    End Sub
    the problem is when i switch to a new tab that isn't called jan 3,2012. it will make the first move from jan4 (for example) then go to jan3 to get the 2nd and third

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Yes, I can see that. What do you want it to do?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Feb 2013
    Posts
    14
    i want to use it on jan3 and have it move those files and than use it on jan4 5.6.7.8.... all the way those a few months worth of data

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Do you want to trigger it once per sheet, or once only and have it process all the sheets? If the latter, what are the starting and ending conditions for sheet selection? What are the conditions for selecting the destination cells on sheet Table?

    Also, your initial post mentions cells A1 - A3, but the posted code only copies and pastes from C11 and C12. Which set of cells is required?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  8. #8
    Join Date
    Feb 2013
    Posts
    14
    The final macro will move a total of 157 individual cells from the spreadsheet to the table. the code above is just something i threw together. i plan to use this once per sheet. so i will run the macro 130 or so times.

  9. #9
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Okay, got that. You missed the other part of the question, though. To simplify:
    • How do you decide which cells to copy? (Are they always in the same place? Always the last three in the first row? The last three in the last row? Some other pattern?)
    • How do you decide where to paste them?

    Once I have these answers, I should be able to put something together for you.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  10. #10
    Join Date
    Feb 2013
    Posts
    14
    the copied cells will always be in the same spot. the pasted cells will be relative. the first cell will be pasted wherever the cursor is at and the other 156 will simply be posted to the right

  11. #11
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Okay. Air code, but it will hopefully point you in the right direction:
    Code:
    Sub MoveData()
    
    Dim wshThis as Worksheet
    Dim wshTable as Worksheet
    Dim astrRange(0 To 2) as String
    
    Set wshThis = ActiveSheet
    Set wshTable = Worksheets("Table")
    
    'Replace the values here with the actual cell locations
    astrRange(0) = "A1"
    astrRange(1) = "A2"
    astrRange(2) = "A3"
    
    Application.ScreenUpdating = False
    wshTable.Select
    If ActiveCell <> "" then
    
    Do Until ActiveCell = ""
    
    ActiveCell.Offset(0, 1).Select
    
    Loop
    
    End If
    
    For Each x In astrRange
    
    wshThis.Range(astrRange(x)).Copy
    wshTable.ActiveCell.Paste
    Application.CutCopyMode = False
    ActiveCell.Offset(0, 1).Select
    
    Next x
    
    WshThis.Select
    
    Application.ScreenUpdating = True
    
    Exit Sub
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  12. #12
    Join Date
    Feb 2013
    Posts
    14
    so you're just setting a variable = to active sheet and copying from active sheet?

  13. #13
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    In essence, yes.

    It's also holding an array of the cells to be copied, working out the next available cell on sheet "Table" for pasting purposes, and then looping through the source cells. However, by the time the sub comes on to select sheet "Table", this becomes the active sheet. This is why it gets a handle to the starting sheet - so that it can copy from it, and so that it can revert back to it after processing.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  14. #14
    Join Date
    Feb 2013
    Posts
    14
    so logic behind this. run on sheet jan 3. the macro reads jan3 and store it as the active to variable x. once the table tab is selected does x still equal jan3 or does it equal table

  15. #15
    Join Date
    Feb 2013
    Posts
    14
    Sub Test()
    '
    ' Test Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+B
    '
    Dim active As Worksheet
    Set active = ActiveSheet
    Range("C11").Select
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets(active).Select
    Range("C12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets(active).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Table").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets(active).Select
    Range("H1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    Range("H2").Select
    End Sub

Posting Permissions

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