Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel Macro

Posted on 2012-08-20
4
Medium Priority
?
821 Views
Last Modified: 2012-08-20
Hopefully I can explain this well. I have 2 workbooks, I need to copy the data from one workbook to the another. The amount of data changes in the rows day to day, how can I make my macro copy properly. For this test I am just using one workbook, in it is Sheet1 and Sheet2 and here is the macro I recorded.

Sub test()
'
' test Macro
' test macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
    Range("A2:A12").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A2").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range("B2:B12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("B2").Select
    ActiveSheet.Paste
    Columns("B:B").EntireColumn.AutoFit
    Sheets("Sheet1").Select
    Range("C2:C12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("C2").Select
    ActiveSheet.Paste
    Range("D2").Select
    Sheets("Sheet1").Select
    Range("D2:D12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("D2:D12").Select
    ActiveSheet.Paste
    Range("A2:E12").Select
    Application.CutCopyMode = False
    With Selection.Font
        .Name = "Arial"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
        .Name = "Arial"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub
testsheet.xlsm
0
Comment
Question by:Edward Pamias
  • 2
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 38313962
something like this?

sub asdfas
dim wbk1 as workbook, wbk2 as workbook
dim rgFrom as range, rgTo as range

set wbk1=activeworkbook 'update as needed
set wbk2=activeworkbook

set rgFrom=wbk1.sheets("Sheet1").range("A1:E" & wbk1.sheets("Sheet1").cells(rows.count,1).end(xlup).row)

set rgTo=wbk2.sheets("Sheet2").cells(1,1)

rgfrom.copy rgto

with rgto.currentregion.font
        .Name = "Arial"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
with rgto.currentregion
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
with rgto.currentregion.interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

end sub

Open in new window

0
 
LVL 19

Author Comment

by:Edward Pamias
ID: 38314090
This works perfect on this workbook. I am going to see if I can modify it to work on my 2 workbooks. If not I will post another question. Thank you!
0
 
LVL 19

Author Closing Comment

by:Edward Pamias
ID: 38314093
Thank you!
0
 
LVL 39

Expert Comment

by:nutsch
ID: 38314117
Glad to help. Thanks for the grade.

Thomas
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question