Solved

VBA Copy Paste

Posted on 2013-05-28
10
188 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 49

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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