copy records vba course

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Copy Records from VBA Course

Copy Records from VBA Course

resolvedResolved · Medium Priority · Version 2007

Joe has attended:
Excel VBA Intro Intermediate course
Excel Advanced - For Power Users course

Copy Records from VBA Course

Hi,

Having recently gone through the basic VBA Course we were shown a method of copying records as below. I've managed to adadpt this for my needs however the data I wish to copy is held in a separate file / window, I've tried adjusting the code so it refences the other file/window/sheet, however it doesn't appear to work. For the time being I've adjusted the code, so that I move the other file into the VBA File and then carry out the copy records code before deleting the source. Is there a way of adjusting the below, so I can switch workbooks/files without having to import the data as a whole?

Many thanks

Joe Martin

Sub CopyRecords()

Dim strMan As String
'Can be seen by any procedure by any module in the workbook
Dim intRowCount As Integer
Dim intColumnCount As Integer
Dim intTargetRowCount As Integer


Dim strSourceRangeStart As String
Dim strSourceRangeFinish As String
Dim strTargetRangeStart As String
Dim strTargetRangeFinish As String

Dim strSourceRange As String
Dim strTargetRange As String

intTargetRowCount = 2
strMan = "MAT"


Windows("TOTALS_MAT.XLS").Activate

'to count number of row

For intRowCount = 1 To Sheets("TOTALS_MAT").Range(strMan) _
.CurrentRegion.Rows.Count

'How many rows in the region (CNTL+*) where cell A10 sits in.

If Sheets("Total Sales").Range("A10") _
.Cells(intRowCount, 10).Value = strName Then

'Count if StrName = the one we're looking for? in 10th column

strSourceRangeStart = _
Sheets("Total Sales").Range("A10"). _
Cells(intRowCount, 1).Address

'pick up address property, then same row to column you need
'Get addresses of start of source range

strSourceRangeFinish = _
Sheets("Total Sales").Range("A10"). _
Cells(intRowCount, 9).Address
'Get addresses of end of source range

strTargetRangeStart = _
Sheets(strName).Cells(intTargetRowCount, 1).Address
'Get Target Range Start of source range

strTargetRangeFinish = _
Sheets(strName).Cells(intTargetRowCount, 9).Address
'Get Target Range Finish of source range

strSourceRange = strSourceRangeStart _
& ":" & strSourceRangeFinish

'Creates Ranges where range is that we want to copy

strTargetRange = strTargetRangeStart _
& ":" & strTargetRangeFinish

'Creates Ranges, where to copy

Sheets(strName).Range(strTargetRange).Value = _
Sheets("Total Sales").Range _
(strSourceRange).Value

'copies to strName sheet

Sheets(strName).Cells(intTargetRowCount, 10) = _
Markup(Sheets(strName).Cells(intTargetRowCount, 8), _
Sheets(strName).Cells(intTargetRowCount, 9))

'Adds in Markup MARKUP(DP,SP) - Functions

Sheets(strName).Cells(intTargetRowCount, 11) = _
Commission(Sheets(strName).Cells(intTargetRowCount, 10))


intTargetRowCount = intTargetRowCount + 1
'next blank row in str sheet, next time copy goes onto next row

End If
Next intRowCount

End Sub

RE: Copy Records from VBA Course

Hi Joe

Thanks for your question

can you just confirm that the data is always being copied from an excel file, and not some other data source (e.g. Access Database)

Thanks

Stephen

RE: Copy Records from VBA Course

Hi,

The file I wish to copy from is an excel file, derived from a delimited file that has had "Text to Columns" already performed. As mentioned before as a workaround, I'm currently moving the excel file I've opened into the main file and then stripping the rows required from sheet to sheet (once they're in the same file), but if I can avoid this step it would cut out this process.

Thanks

Joe

RE: Copy Records from VBA Course

Hi Joe

Thanks for your question. This one is a little tricky to deal with without seeing the file. However, I think your problems stems from using the window object. It would be better to explicitly state in your source range the name of the other workbook.

IfWorkbooks("SourceWorkbook"). Sheets("Total Sales").Range("A10") _
.Cells(intRowCount, 10).Value = strName Then


Repeat the above for the start of the source range

strSourceRangeStart = Workbooks("SourceWorkbook")._
Sheets("Total Sales").Range("A10"). _
Cells(intRowCount, 1).Address


This should solve your problem

Any further issues, please do not hesitate to get back to me

Regards

Stephen

Thu 7 Jul 2011: Automatically marked as resolved.

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 


Server loaded in 0.06 secs.