Code to Copy Paste Special Between to Files 1

[sxschech]

I have an excel file that is updated each week with data from another excel file. The data is copied and pasted manually with edit paste special. At this point, because the position of the incoming data as well as the position as to where the data will be placed in the main spreadsheet (different groups of data will be going to different tabs within the destination spreadsheet) can be different each week, I don't think the process can be fully automated. What I was hoping could happen is that if I place the cursor on the first cell in the source sheet and click the cursor in the first cell in the destination the code could then Copy Paste Special Values for 5 cells (G2:G6 for example) into the other file to the position of the cursor.

Example of Source data
[tt]
2006 F 2006/06/03 SC MSF APP 5
2006 F 2006/06/03 sc MSF ACC 18
2006 F 2006/06/03 sc MSF CAN 9
2006 F 2006/06/03 sc MSF DEN 22
2006 F 2006/06/03 sc MSF ITE 44
2006 F 2006/06/03 BN MBA APP 67
2006 F 2006/06/03 BN MBA ACC 5
2006 F 2006/06/03 BN MBA CAN 2
2006 F 2006/06/03 BN MBA DEN 0
2006 F 2006/06/03 BN MBA ITE 25
[/tt]

SC data will go to the SC tab in the destination while BN data will go to the BN tab in the destination file. Each week is set up as its own col. in the destination so for example the week may be in col. Z and next week the data will go in col. AA. Only the col with data will actually be copied from the source to the destination. Needs to be copied as values due to formatting and where the data originally came from.


-----------------------
On a related note, I have another spreadsheet where the information is more "fixed" and set up code that successfully transfers data between source file to destination and correct tab within destination. If I start appending data to this file, to create one master source, rather than having a separate copy for each week, how can I adjust the logic so that it will be able to pick up the correct data, by date?

This is the code I have for the data that has a "fixed" position each week. This code is "hands off" meaning I don't have to do any manual copy or paste, whereas the code I create for above probably would need to have more manual intervention due to the variability in the source data groups.

Sub CP_UPaste()
'
' CP_UPaste Macro
' Macro recorded 5/19/2006 by ITS Image
'
'Copy Data from CP-SUM Report and paste in corresponding tab
'uses weekno as reference to the col to paste the data in.  Assumes
'paste into row 6 for Current Year Totals
'
Dim stSheet As String
Dim stRange As String
Dim i As Integer
Dim intWeekno As Integer
Dim stFileName As String
stFileName = "C:\Documents and Settings\ACCOUNTS\My Documents\Stats\Data\"
    Workbooks.Open Filename:=stFileName & "CP_U.xls"
    Windows("06F_A.xls").Activate
    For i = 1 To 4
        Select Case i
         Case Is = 1
            stSheet = "CP"
            stRange = "2"
        Case Is = 2
            stSheet = "CP-Summer"
            stRange = "14"
        Case Is = 3
            stSheet = "CP-Off"
            stRange = "8"
        Case Is = 4
            stSheet = "CP-Off-Summer"
            stRange = "20"
        End Select
        Sheets(stSheet).Select
        Windows("CP_U.xls").Activate
        Range("F" & stRange & ":F" & stRange + 5).Select
        Selection.Copy
        Windows("06F_A.xls").Activate
        Cells(6, Sheets("Temp Data").[Q111]).Select 'Dynamically Refer to Col Letters
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Next i
    Sheets("PSTotal").Activate
    Selection.Copy
    Cells(6, Sheets("Temp Data").[Q111]).Select 'Dynamically Refer to Col Letters
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

 

[SkipVought]

Hi,

I strongly advise AGAINST excessive use of copy 'n' paste if any other means are available.

Is your source sheet configured in TABLUAR format: one row of headings in row 1, all data contiguous.

Your acquisition criteria seems to key on the DATE criteria. THis could be a pretty simple query using Data>Get External Data>New Database Query>EXCEL FILES -- YOUR OTHER WORKBOOK...

Macro record getting the data.

The we can modify this macro and the Data Range Properties to bring it into the sheet in the proper place.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[sxschech]

Skip,

The prolbem is it is not simply a list of data to copy and paste (unless I'm misunderstanding your response), the main spreadsheet contains 14 data entry tabs to be updated and I discovered that some rows are hidden in the main file, also a new category/group can get added at any time and involves a bit of work to set up the additional rows and formulas that get summarized onto several other tabs and also a chart. That is why I'm not sure at present it could be fully automated. I had thought about converting to Access, which I am more familiar with, but due to the multi-week/year nature of the file and the various calculations, I have not really tackled doing so yet. I didn't create the spreadsheets, rather came into it by way of accepting a new position. At this point, I was looking for a way to cut down on the amount of keystrokes and mousing to get the data from point a to point b.

Thanks,

Steve

 

[SkipVought]

14 tabs to be UPDATED (written to) or to be accessed (copied from)? Either way, what's the issue?

Hidden rows/columns? Not a problem with query.

New rows get added or rows deleted. Not a problem with query.

What this multi-week/year thing?

Let's talk about specifically what need to get copied from A to B.

All it should eventyally take is a button click.





Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[sxschech]

The raw data is like the sample from the original post. The destination has a col for each week from Sep 05 thru Sep 06 starting at col D10 runing through col BB? depending on how many new groups are added between now and then.

Each group has 6 rows, 5 of which are obtained from raw data, 6th is a calculation based on prev week and cur week data (this can be overridden depending on data from yet another report that is distributed via email.

Does this help describe the situation or is more information needed.

Thanks again for your interest and assistance.

 

[SkipVought]

You have a snippet of source data.

You did not post any spec regarding the result.

What gets mapped to what?

Does your source data have column headings?

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[sxschech]

Source Data
[tt]
Yr T RunDate Loc Grp Stat StatCount
2006 F 2006/06/03 SC MSF APP [red] 5[/red]
2006 F 2006/06/03 sc MSF ACC [red] 18
[/red]2006 F 2006/06/03 sc MSF CAN [red] 9
[/red]2006 F 2006/06/03 sc MSF DEN [red] 22
[/red]2006 F 2006/06/03 sc MSF ITE [red] 44[/red]
2006 F 2006/06/03 BN MBA APP 67
2006 F 2006/06/03 BN MBA ACC 5
2006 F 2006/06/03 BN MBA CAN 2
2006 F 2006/06/03 BN MBA DEN 0
2006 F 2006/06/03 BN MBA ITE 25


Dest Data
[tt]
SC-Tab 30-Sep-05 7-Oct-05 14-Oct-05 21-Oct-05
Year Descrip Week # 2 3 4
2006 MSF App 0 1 1 5 [red]5[/red]
Acc 0 0 0 2
Can 0 0 0 0
Den 0 0 0 0
ItE 0 0 0 0
Net ItE 0 0 0 0
2006 MLA App 1 2 3 3
Acc 1 2 2 2
Can 0 0 0 0
Den 0 0 0 0
ItE 1 1 1 1
Net ItE 0 0 0 0
[/tt]

In example above, source data from SC StatCount col will be pasted into the first 5 cells in the dest data of the SC tab under the col corresponding to the run date. The rundate may not exactly match the date in the destination due to timing of the run. So essentially it is the next sequential col in the destination. When the next group is ready, in this case, BN, I would switch to the BN tab and paste in the appropriate section there. This is just a simplified example, the actual data has further groups and subgroups which also determine whether they are even included in this file, or are saved in another file.

The reports generated from this information are from detail, and different summary levels and a chart. The reports display a look at the past 6 weeks for the current year and the past 3 years and then do various comparison calculations on the result.

 

[SkipVought]

It's very hard to tell what you are doing, but here's a pivot table report that I did in about 5 seconds on the source data you just posted...
[tt]
Sum of StatCount RunDate
Loc Grp Stat 6/3/2006
BN MBA ACC 5
APP 67
CAN 2
DEN 0
ITE 25
MBA Total 99
BN Total 99
SC MSF ACC 18
APP 5
CAN 9
DEN 22
ITE 44
MSF Total 98
SC Total 98
[/tt]
Run Date can be GROUPED by weeks (7 Days)
Add a column in your source to calculate Week#

Should be pretty simple with the Pivot table Wizard.

5 Seconds!

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[sxschech]

Skip,

Appreciate your thoughts, the data have already been "pivoted" all that is needed is a way to copy the 5 data elements of each group from Spreadsheet File 1 to the appropriate Tab Col and Row in SpreadSheet File 2. The pivot results that you provided are doing extra things (totaling that aren't needed for what we are trying to do).

Let me try explaining this way...

1. Open Source File
2. Open Destination File
3. Go to First Data Entry Tab "SC"
4. Look at Descrip of Col B "BLL"
5. Go row 16 (may differ on different tabs)
6. Go to first empty col (curweek) Col Z for Example Z16
7. Switch to Source File
8. Is BLL here? No --insert 0's for the 5 cells downward
9. Switch back to Destination
9a.Insert 0's for the 5 cells downward
10.Go 7 rows down (Move to Z22)
11.Look at Descrip of Col B "MSF"
12.Is MSF here? Yes--Switch to Source File
13.Copy 5 cells of data in Col G
14.Position in cell Z22
15.Edit Paste Special Values
16.Move down 2 cells to Z28
17.Repeat Steps above until arrive at end data input section of destions file's tab that is being worked on.
18.If data in source doesn't exist in destination, add new rows and formulae in all tabs requiring new data for summaries and chart
19.If data doesn't exist in source but there are rows in desination, enter 0's
20.When arrive at end of tab, move to the next data entry tab (won't be the next tab as there are summary tabs in between the entry tabs) and repeat steps.

does this help clarify what is going on? This is why I was considering an approach to try to automate the copy paste process, but still have control over the positioning manually since things are so variable.

 

[sxschech]

Finally figured out how to write the code for copying between two sheets and paste special.

Sub CPSV()
'
' Copy Paste Special Values Macro
' Copies the Totals from the GradTransport file for each major based on placing the cursor at
' a user specified starting cell.
' Active this macro with Control Key + u -- lowercase u not uppercase!!
' Macro recorded 7/5/2006 by ITS Image
'Keyboard Shortcut: Ctrl u
'

'
    Windows("Transport.xls").Activate
    'Select the Active Cell and 5 cells down
    Range(ActiveCell, ActiveCell.Offset(5, 0)).Select 'Example: Range("G554:G559").Select
    Selection.Copy
    'Move cursor to next group to make it the active cell for the next copy
    ActiveCell.Offset(6, 0).Select
    Windows("Grad.xls").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    'Move cursor to next group to make it the active cell for the next paste
    ActiveCell.Offset(7, 0).Select
End Sub

As for MS query, I wrote some code with that as well, I tried to parameterize it, but it didn't work as expected, while in MS Query, it worked ok, but when copied as code to vba, it creates a generic "Parameter" prompt, rather than [Enter Major:]. Also, noticed that with the prompt it allows one to either type the value or to select an excel cell. How can I modify the code to refer to a cell for the prompt (I would end up with 3 prompts, two of which would be a fixed cell location on a given sheet, the third would be in Col B, but the row would be the same row as the active cell. Thus if the active cell were on K26 the third prompt would refer to B26. Note: The code below is only showing one prompt at present. Desired prompts are in [red]red[/red]

Sub QueryPaste()
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=C:\Documents and Settings\ACCOUNTS\Data\Transport.xls;D" _
        ), Array( _
        "efaultDir=C:\Documents and Settings\ACCOUNTS\Data;DriverId=790;MaxBufferSize=20" _
        ), Array("48;PageTimeout=5;")), Destination:=ActiveCell)  'Range("AQ10"))
        .CommandText = Array( _
        "SELECT App_Count" & Chr(13) & "" & Chr(10) & _
        "FROM Transport Transport" & Chr(13) & "" & Chr(10) & _
        "WHERE Term=[red]'F'[/red] AND School=[red]'ARTS'[/red] AND Major=[red]?[/red]" _
        )
        .Name = "GRAD"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = True
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[SkipVought]

This is one way...

Sub QueryPaste([b][red]sMajor[/red][/b] as string)

....

        .CommandText =  _
        "SELECT App_Count " &  _
        "FROM Transport Transport " &  _
        "WHERE Term   ='F' " & _
        "  AND School ='ARTS' " & _
        "  AND Major  =[b][red]'" & sMajor & "'[/red][/b]"

Skip,

_[red][/red]