Copying Data to Another Worksheet - Macro
I am making a large macro for a daily report. After creating the macro, I have to copy over the information to condense it with the previous report. I have done so but its recording the amount of rows I am copying and the row that I will be copying it to. Does anyone know how I can program the macro to copy an undefined amount of information to an undefined row?
I appreciate any advice or help!
Re: Copying Data to Another Worksheet - Macro
This is that section of the code:
Sub CopyOver() ' ' CopyOver Macro ' Macro recorded 1/25/2013 by cac1057 ' ' Rows("1:110").Select Selection.Copy Windows("Previous Hold Transfer Report.XLS").Activate ActiveWindow.SmallScroll Down:=42 ActiveWindow.ScrollRow = 44 ActiveWindow.ScrollRow = 46 ActiveWindow.ScrollRow = 49 ActiveWindow.ScrollRow = 54 ActiveWindow.ScrollRow = 59 ActiveWindow.ScrollRow = 68 ActiveWindow.ScrollRow = 77 ActiveWindow.ScrollRow = 88 ActiveWindow.ScrollRow = 101 ActiveWindow.ScrollRow = 114 ActiveWindow.ScrollRow = 130 ActiveWindow.ScrollRow = 148 ActiveWindow.ScrollRow = 171 ActiveWindow.ScrollRow = 194 ActiveWindow.ScrollRow = 223 ActiveWindow.ScrollRow = 249 ActiveWindow.ScrollRow = 281 ActiveWindow.ScrollRow = 309 ActiveWindow.ScrollRow = 341 ActiveWindow.ScrollRow = 375 ActiveWindow.ScrollRow = 414 ActiveWindow.ScrollRow = 449 ActiveWindow.ScrollRow = 484 ActiveWindow.ScrollRow = 519 ActiveWindow.ScrollRow = 553 ActiveWindow.ScrollRow = 588 ActiveWindow.ScrollRow = 616 ActiveWindow.ScrollRow = 642 ActiveWindow.ScrollRow = 666 ActiveWindow.ScrollRow = 687 ActiveWindow.ScrollRow = 709 ActiveWindow.ScrollRow = 727 ActiveWindow.ScrollRow = 745 ActiveWindow.ScrollRow = 761 ActiveWindow.ScrollRow = 773 ActiveWindow.ScrollRow = 785 ActiveWindow.ScrollRow = 794 ActiveWindow.SmallScroll Down:=3 Range("A833").Select ActiveSheet.Paste ActiveWindow.ScrollRow = 796 ActiveWindow.ScrollRow = 794 ActiveWindow.ScrollRow = 792 ActiveWindow.ScrollRow = 791 ActiveWindow.ScrollRow = 787 ActiveWindow.ScrollRow = 786 ActiveWindow.ScrollRow = 785 ActiveWindow.ScrollRow = 784 ActiveWindow.ScrollRow = 783 ActiveWindow.ScrollRow = 781 ActiveWindow.ScrollRow = 780 ActiveWindow.ScrollRow = 779 ActiveWindow.ScrollRow = 778 ActiveWindow.ScrollRow = 777 ActiveWindow.ScrollRow = 776 ActiveWindow.ScrollRow = 774 ActiveWindow.ScrollRow = 773 ActiveWindow.ScrollRow = 772 ActiveWindow.ScrollRow = 768 ActiveWindow.ScrollRow = 766 ActiveWindow.ScrollRow = 763 ActiveWindow.ScrollRow = 758 ActiveWindow.ScrollRow = 754 ActiveWindow.ScrollRow = 750 ActiveWindow.ScrollRow = 746 ActiveWindow.ScrollRow = 743 ActiveWindow.ScrollRow = 739 ActiveWindow.ScrollRow = 736 ActiveWindow.ScrollRow = 733 ActiveWindow.ScrollRow = 731 ActiveWindow.ScrollRow = 730 ActiveWindow.ScrollRow = 727 ActiveWindow.ScrollRow = 726 ActiveWindow.ScrollRow = 725 ActiveWindow.ScrollRow = 724 ActiveWindow.ScrollRow = 721 ActiveWindow.ScrollRow = 720 ActiveWindow.ScrollRow = 719 ActiveWindow.ScrollRow = 716 ActiveWindow.ScrollRow = 712 ActiveWindow.ScrollRow = 709 ActiveWindow.ScrollRow = 704 ActiveWindow.ScrollRow = 699 ActiveWindow.ScrollRow = 693 ActiveWindow.ScrollRow = 689 ActiveWindow.ScrollRow = 683 ActiveWindow.ScrollRow = 677 ActiveWindow.ScrollRow = 669 ActiveWindow.ScrollRow = 663 ActiveWindow.ScrollRow = 653 ActiveWindow.ScrollRow = 643 ActiveWindow.ScrollRow = 632 ActiveWindow.ScrollRow = 622 ActiveWindow.ScrollRow = 609 ActiveWindow.ScrollRow = 595 ActiveWindow.ScrollRow = 582 ActiveWindow.ScrollRow = 570 ActiveWindow.ScrollRow = 557 ActiveWindow.ScrollRow = 543 ActiveWindow.ScrollRow = 529 ActiveWindow.ScrollRow = 515 ActiveWindow.ScrollRow = 501 ActiveWindow.ScrollRow = 486 ActiveWindow.ScrollRow = 469 ActiveWindow.ScrollRow = 455 ActiveWindow.ScrollRow = 441 ActiveWindow.ScrollRow = 426 ActiveWindow.ScrollRow = 415 ActiveWindow.ScrollRow = 402 ActiveWindow.ScrollRow = 391 ActiveWindow.ScrollRow = 378 ActiveWindow.ScrollRow = 369 ActiveWindow.ScrollRow = 358 ActiveWindow.ScrollRow = 348 ActiveWindow.ScrollRow = 342 ActiveWindow.ScrollRow = 335 ActiveWindow.ScrollRow = 329 ActiveWindow.ScrollRow = 321 ActiveWindow.ScrollRow = 315 ActiveWindow.ScrollRow = 308 ActiveWindow.ScrollRow = 302 ActiveWindow.ScrollRow = 295 ActiveWindow.ScrollRow = 289 ActiveWindow.ScrollRow = 282 ActiveWindow.ScrollRow = 277 ActiveWindow.ScrollRow = 270 ActiveWindow.ScrollRow = 265 ActiveWindow.ScrollRow = 261 ActiveWindow.ScrollRow = 254 ActiveWindow.ScrollRow = 248 ActiveWindow.ScrollRow = 240 ActiveWindow.ScrollRow = 231 ActiveWindow.ScrollRow = 223 ActiveWindow.ScrollRow = 214 ActiveWindow.ScrollRow = 207 ActiveWindow.ScrollRow = 197 ActiveWindow.ScrollRow = 186 ActiveWindow.ScrollRow = 176 ActiveWindow.ScrollRow = 167 ActiveWindow.ScrollRow = 158 ActiveWindow.ScrollRow = 151 ActiveWindow.ScrollRow = 143 ActiveWindow.ScrollRow = 134 ActiveWindow.ScrollRow = 126 ActiveWindow.ScrollRow = 117 ActiveWindow.ScrollRow = 110 ActiveWindow.ScrollRow = 102 ActiveWindow.ScrollRow = 94 ActiveWindow.ScrollRow = 84 ActiveWindow.ScrollRow = 76 ActiveWindow.ScrollRow = 67 ActiveWindow.ScrollRow = 57 ActiveWindow.ScrollRow = 46 ActiveWindow.ScrollRow = 39 ActiveWindow.ScrollRow = 30 ActiveWindow.ScrollRow = 23 ActiveWindow.ScrollRow = 17 ActiveWindow.ScrollRow = 10 ActiveWindow.ScrollRow = 7 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 1 Columns("A:A").Select Application.CutCopyMode = False Range("A1:J942").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("1:1").Select Selection.Insert Shift:=xlDown
Can you specify which rows from which file should be copied to which file in which cell?
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There is no defined number. This macro will be used for various reports.
Both reports have the same format. I guess you can say Columns A-I have to be copied over but the number of rows in both reports will always change.
And the worksheet that I will be copying the information will also have an undefined amount of rows... I am trying to condense the information into one spreadsheet.
Ok, so should data from row 2 to the last row of the source data be copied to the other file?
Now, its row 1. Yes.
From which file do you want the macro to run? The source file or destination file?
Also, will the other file (the one that doesnt contain the macro) be open at the time of running the macro or should it be opened by the macro?
Source file.
Yes. In the macro, it is programmed to open up the destination file.
Can you post that code please?
Workbooks.Open Filename:= _ "S:\Small Business\Previous Hold Transfer Report.XLS" Range("A1:I1").Select Selection.Interior.ColorIndex = 3 Windows("Daily Hold Transfer Report.xls").Activate Windows("Previous Hold Transfer Report.XLS").Activate Columns("C:C").Select Dim LastRow As Long LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row Selection.Insert Shift:=xlToRight Range("C2:C" & LastRow).FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[Daily Hold Transfer Report.xls]Sheet1'!C2,1,FALSE)" Dim r As Long Dim iCol As Long With Application .Calculation = xlCalculationManual .ScreenUpdating = False .DisplayAlerts = False For iCol = 1 To Cells(1, Columns.Count).End(xlToLeft).Column For r = Cells(Rows.Count, iCol).End(xlUp).Row To 1 Step -1 If Application.WorksheetFunction.IsNA(Cells(r, iCol)) Then Rows(r).Delete Next r Next iCol .Calculation = xlCalculationAutomatic .ScreenUpdating = True .DisplayAlerts = True End With Windows("Daily Hold Transfer Report.xls").Activate End Sub
Logically, this looks like it should work:
Option Explicit Sub test() Dim fNAME As String, wbDEST As Workbook Dim LastRow As Long, LastCol, r As Long, iCol As Long Application.ScreenUpdating = False Application.DisplayAlerts = False With ActiveSheet .Range("C:C").Insert Shift:=xlToRight LastRow = .Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row LastCol = .Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Column .Range("C2:C" & LastRow).FormulaR1C1 = _ "=VLOOKUP(RC[-1],'[Daily Hold Transfer Report.xls]Sheet1'!C2,1,FALSE)" On Error Resume Next .Range("A2", .Cells(LastRow, LastCol)).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete On Error GoTo 0 LastRow = .Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row .Range("A1:A" & LastRow).EntireRow.Copy End With fNAME = "S:\Small Business\Previous Hold Transfer Report.XLS" Set wbDEST = Workbooks.Open(fNAME) Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteAll 'or xlPasteValues Range("A:J").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal Rows("1:1").Insert Shift:=xlDown wbDEST.Close True Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Re: Copying Data to Another Worksheet - Macro
That gave me an error on the vlook up code. My vlookup macro is working perfectly. I just need the section to copy and paste.
Thanks!
I actually found this in another post. I re-worked the information and it worked!
Thanks for the help JBeaucaire!Sub CopyOver() ' ' CopyOver Macro ' Macro recorded 1/25/2013 by cac1057 ' ' Windows("Daily Hold Transfer Report.xls").Activate Worksheets("HoldTransfer Over & Under 25").UsedRange.Select Selection.Copy Windows("Previous Hold Transfer Report.XLS").Activate Worksheets("HoldTransfer Over & Under 25").Select ActiveSheet.Range("A" & ActiveSheet.UsedRange.Rows.Count + 1).Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False End Sub
That code can be re-written into just 2 lines -
Workbooks("Daily Hold Transfer Report.xls").Worksheets("HoldTransfer Over & Under 25").UsedRange.Copy Workbooks("Previous Hold Transfer Report.xls").Worksheets("HoldTransfer Over & Under 25").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks