Solved

VBA Copy Paste

Posted on 2013-05-28
10
186 Views
Last Modified: 2013-06-03
I modified some code that I had in another workbook, works but I haven't been able to figure out how to modify it to only paste the data from columns B:P (Template) into the new worksheet.  Also would prefer to capture the data from the last row with data.  Currently getting all rows with formatting

Sub FileCopy()
Dim Rowcount As Integer
Dim DataRowCount As Integer


'These 2 lines create variables for your 2 sheets.

    Set MFile = ThisWorkbook.Worksheets("ShipDoc") 'ActiveWorksheet
    Set OFile = ThisWorkbook.Worksheets("Template")
     
    OFile.Activate
    Rowcount = Application.CountA(ActiveSheet.Range("B:P")) 'How many rows contain data
     Rows("2:" & Rowcount).Select
        Selection.Copy
    
    MFile.Activate
    
    DataRowCount = Application.CountA(ActiveSheet.Range("B:B")) 'How many rows contain data in this sheet
    Cells(DataRowCount + 5, 1).Select
    ActiveSheet.Paste
    Range("A1").Select
    


End Sub

Open in new window

0
Comment
Question by:jmac001
  • 5
  • 4
10 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39203626
Hi,

Pls try

Sub FileCopy()
Dim Rowcount As Integer
Dim DataRowCount As Integer


'These 2 lines create variables for your 2 sheets.

    Set MFile = ThisWorkbook.Worksheets("ShipDoc") 'ActiveWorksheet
    Set OFile = ThisWorkbook.Worksheets("Template")
     
    OFile.Activate
    Rowcount = Application.CountA(ActiveSheet.Range("B:B")) 'How many rows contain data ' I've modified this
    Range("B" & Rowcount & ":P" & Rowcount).Select  ' I've modified this
        Selection.Copy
    
    MFile.Activate
    
    DataRowCount = Application.CountA(ActiveSheet.Range("B:B")) 'How many rows contain data in this sheet
    Cells(DataRowCount + 5, 2).Select ' I've modified this
    Selection.PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False ' I have ve modified this
    Range("A1").Select
    


End Sub

Open in new window

Regards
0
 
LVL 24

Expert Comment

by:Steve
ID: 39203774
you could try the following which eliminates the clipboard copy all together:

Sub FileCopy()
Dim Rowcount As Long  'row counts should be Long
Dim DataRowCount As Long  'row counts should be Long
Dim MFile As Worksheet
Dim OFile As Worksheet

'These 2 lines create variables for your 2 sheets.
    Set MFile = ThisWorkbook.Worksheets("ShipDoc")
    Set OFile = ThisWorkbook.Worksheets("Template")

    Rowcount = OFile.Range("B" & Rows.Count).End(xlUp).Row 'How many rows contain data in Template
    DataRowCount = MFile.Range("B" & Rows.Count).End(xlUp).Row 'How many rows contain data in ShipDoc
    
    MFile.Range("B" & DataRowCount + 5 & ":P" & DataRowCount + 3 + Rowcount).Value = _
        OFile.Range("B2:P" & Rowcount).Value

End Sub

Open in new window


The following does just the last row (not all data):
    MFile.Range("B" & DataRowCount + 5 & ":P" & DataRowCount + 5).Value = _
        OFile.Range("B" & Rowcount & ":P" & Rowcount).Value

Open in new window

0
 

Author Comment

by:jmac001
ID: 39204718
The_Barman thanks that is what I was looking for.  Two questions 1. how do I maintain the formating from the Template to ShipDoc worksheet?  2. users will need to re-populate the information I know that you can clear contents but I have header information how do I clear/delete from A6 down.  Thanks
0
 
LVL 24

Expert Comment

by:Steve
ID: 39205018
To clear contents, just use the function after transfering the data:

Sub FileCopy()
Dim Rowcount As Long  'row counts should be Long
Dim DataRowCount As Long  'row counts should be Long
Dim MFile As Worksheet
Dim OFile As Worksheet

'These 2 lines create variables for your 2 sheets.
    Set MFile = ThisWorkbook.Worksheets("ShipDoc")
    Set OFile = ThisWorkbook.Worksheets("Template")

    Rowcount = OFile.Range("B" & Rows.Count).End(xlUp).Row 'How many rows contain data in Template
    DataRowCount = MFile.Range("B" & Rows.Count).End(xlUp).Row 'How many rows contain data in ShipDoc
    
    MFile.Range("B" & DataRowCount + 5 & ":P" & DataRowCount + 3 + Rowcount).Value = _
        OFile.Range("B2:P" & Rowcount).Value

        OFile.Range("B6:P" & Rowcount).ClearContents

End Sub

Open in new window


To maintain the formatting would require either code to format the new data after moving it. Or just to copy the formatting over.
Is it not possible to format the whole columns in the destination sheet so that the format is in place before the data agets there?
0
 

Author Comment

by:jmac001
ID: 39205165
Receive and error 1004 Method 'Range' of object_'Worksheet' failed.  Switch the sheets to be cleared after the first run will need to have the MFile worksheet cleared and repopulated.

Here is the code

Sub FileCopy()
Dim Rowcount As Long  'row counts should be Long
Dim DataRowCount As Long  'row counts should be Long
Dim MFile As Worksheet
Dim OFile As Worksheet

    


'These 2 lines create variables for your 2 sheets.
    Set MFile = ThisWorkbook.Worksheets("ShipDoc")
    Set OFile = ThisWorkbook.Worksheets("Template")
    
    MFile.Range("B6:O" & Rowcount).Clear
    
    Rowcount = OFile.Range("B" & Rows.Count).End(xlUp).Row 'How many rows contain data in Template
    DataRowCount = MFile.Range("A" & Rows.Count).End(xlUp).Row 'How many rows contain data in ShipDoc
    
    MFile.Range("A" & DataRowCount + 1 & ":O" & DataRowCount + (-1) + Rowcount).Value = _
        OFile.Range("B2:P" & Rowcount).Value

End Sub

Open in new window


The formatting would be cleared with the vba.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 24

Expert Comment

by:Steve
ID: 39206792
The line:
MFile.Range("B6:O" & Rowcount).Clear
Is before the move of the data... is this what you wanted?
It is also before the line:
Rowcount = OFile.Range("B" & Rows.Count).End(xlUp).Row 'How many rows contain data in Template
So the reason for the error is that RowCount does not have any value...
It is also RowCount that relates to Ofile rather than Mfile

1) You should clear the sheet after transfering the data,
2) use clearcontents rather than clear and this will maintain formatting.
3) You must give RowCount a value before using it.

Somthing like:
Sub FileCopy()
Dim Rowcount As Long  'row counts should be Long
Dim DataRowCount As Long  'row counts should be Long
Dim MFile As Worksheet
Dim OFile As Worksheet

    


'These 2 lines create variables for your 2 sheets.
    Set MFile = ThisWorkbook.Worksheets("ShipDoc")
    Set OFile = ThisWorkbook.Worksheets("Template")

    Rowcount = OFile.Range("B" & Rows.Count).End(xlUp).Row 'How many rows contain data in Template
    DataRowCount = MFile.Range("A" & Rows.Count).End(xlUp).Row 'How many rows contain data in ShipDoc
    
    MFile.Range("A" & DataRowCount + 1 & ":O" & DataRowCount + (-1) + Rowcount).Value = _
        OFile.Range("B2:P" & Rowcount).Value

    OFile.Range("B6:O" & Rowcount).ClearContents   ' ClearContents of Template, leaving Formatting in place

End Sub 

Open in new window


If you could provide a step by step detailed description of the result of the code I would be more than happy to write the full code to acheive your goal. If you have an example workbook it could help too if you could post it too.
Thanks, Steve.
0
 

Author Comment

by:jmac001
ID: 39211949
Hi Steve,

What I would like to happen is the the first time the "Create Button" is clicked on the ShipDoc worksheet is to copy of the data from the Template Worksheet.   The next time the "Create" button is clicked I would like all of the data from A6 down to be cleared from the ShipDoc worksheet and the all the data from the Template worksheet be populated into the ShipDoc worksheet
Parts-Tracker-Template---Copy.xlsm
0
 
LVL 24

Accepted Solution

by:
Steve earned 500 total points
ID: 39215451
The attached file has the following code which should be a step in the right direction:

Sub FileCopy()
Dim Rowcount As Long  'row counts should be Long
Dim DataRowCount As Long  'row counts should be Long
Dim MFile As Worksheet
Dim OFile As Worksheet

'These 2 lines create variables for your 2 sheets.
    Set MFile = ThisWorkbook.Worksheets("SHIPDOC")
    Set OFile = ThisWorkbook.Worksheets("TEMPLATE")

'Count how many rows contain data in ShipDoc (not needed)...
    'DataRowCount = MFile.Range("A" & Rows.Count).End(xlUp).Row
   
'clear the contents of ShipDoc from row 6 to the end of the data...
    MFile.Range("6:" & Rows.Count).Delete   ' Delete Contents of SHIPDOC

'Count how many rows contain data in Template
    Rowcount = OFile.Range("B" & Rows.Count).End(xlUp).Row

'transfer data from Template to Shipdoc (starting at row 6)
    MFile.Range("A" & 6 & ":O" & Rowcount + 6 + (-2)).Value = _
        OFile.Range("B2:P" & Rowcount).Value

'Transfer fromatting from first line of template to SHIPDOC
    OFile.Range("B2:P2").Copy
    MFile.Range("A" & 6 & ":O" & Rowcount + 6 + (-2)).PasteSpecial Paste:=xlPasteFormats

End Sub

Open in new window


See attached workbook...

Is this how you would like the finished article to look?
Do you need the date filling in or the Store number?
Take a look and see how this suits and feel free to ask for changes :)

ATB
Steve.
Parts-Tracker-Template---Copy.xlsm
0
 

Author Closing Comment

by:jmac001
ID: 39216950
Steve, this is actually how I would like it to work. Thank you
0
 
LVL 24

Expert Comment

by:Steve
ID: 39217386
Excellent, happy you have it sorted :)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now