Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA Copy Paste

Posted on 2013-05-28
10
Medium Priority
?
193 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 52

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

715 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