PDA

View Full Version : [SOLVED] VBA Copy and Paste data form one workbook to another.. Problem is data change every w



edmundchant
12-20-2017, 03:12 AM
Hi All I am new to VBA excel, currently I am doing a major project. For this project, every week I will receive new data updates from information company about the percentage of emails we have collected.
My purpose is to create codes so that immediately after I receive the excel from the information company I can run the codes to copy the set of data into my master list.

However the problem is that every week the excel column change and I have to only paste 3 specific columns. So how can I make this dynamic? The method I have decided to adopt is to use userform, where the user can specify what column range and row to copy their data from (as shown in the image). However, I do not know how or where to start.

21223

Below is the codes which I have done so far:



Private Sub transferSpecificData()
Dim percentagecollected As Single
Dim noofemail As Single
Dim totalemail As Single
Dim monthlytransferdata As Workbook

Set monthlytransferdata = Workbooks.Open("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Transfer Monthly Data.xlsb")

Worksheets("Email_AR NTB (3)").Select
percentagecollected = Range("H:H")

Workbooks.Open ("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Monthly Tracking Channel Fake.xlsb")

Worksheets("Branch NTB AR").Select
Worksheets("Branch NTB AR").Range("A1").Select

RowCount = Worksheets("Branch NTB AR").Range("A1").CurrentRegion.Rows.Count
With Worksheets("branch NTB AT").Range("A1")
.Offset(RowCount, 0) = percentagecollected
.Offset(RowCount, 1) = noofemail

End With

monthlytransferdata.Save

End Sub



But it gives back error: Run Time error 13: Type mismatch
&
another problem it does not allow me to actually specify which column and row I want to extract my data from.

Any help is very much appreciated. Thank you!!

Paul_Hossler
12-20-2017, 07:10 AM
1. I think it'd help a lot if you attached a sample of the input workbook and your macro workbook

2. Your approach is not very general purpose, and I think the overall structure needs improvement

3. Guessing as to which line is causing an error (since you didn't tell)



Dim percentagecollected As Single

.....
percentagecollected = Range("H:H")



percentagecollected is Dim-ed as Single

If you want it to be a Range, then



Dim percentagecollected As Range

.....
Set percentagecollected = Range("H:H")




If you want it to be the column number, then



Dim percentagecollected As Long

.....
percentagecollected = Range("H:H").Column

edmundchant
12-20-2017, 07:46 PM
Hi Paul,

1. I have given a screenshot of what the input workbook and the master list looks like.

2. "Your approach is not very general purpose, and I think the overall structure needs improvement" Sorry I am new to VBA not sure what you mean by not general purpose. Can you provide insights as to how I can improve the structure?

3. Oh sorry I didn't know how to debug but I have just found out how to. The error is coming from this line: Workbooks.Open ("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Monthly Tracking Channel Fake.xlsb")
I have changed my percentagecollected to long as well.


Excel sheet given by information company
21226

Master List
21227

edmundchant
12-20-2017, 07:49 PM
Excel sheet given by information company
21226




As you can the actual data that I need to extract only starts at row 38 and the column I need varies... sometimes I need extract last 2 column sometimes last 3 column

offthelip
12-21-2017, 02:48 AM
Your pictures of spreadsheets are unreadable, so it is difficult to help you. However you state:

the problem is that every week the excel column change and I have to only paste 3 specific columns.
Do the columns you need to copy always have the same header? because if they do you can get vba to automatically identify which columns to copy.
If this is not possible, a better way of getting the user to choose the right colums is to allow the user to select the columns with the mouse. This code shows you how to do this:



Dim oRangeSelected As Range
On Error Resume Next
Set oRangeSelected = Application.InputBox("Put a suitable message in here!", _
"SelectARAnge Demo", Selection.Address, , , , , 8)
If oRangeSelected Is Nothing Then
Else

firstrow = oRangeSelected.Row
firstcol = oRangeSelected.Column

MsgBox (firstrow & "/" & firstcol)

End If

edmundchant
01-02-2018, 11:41 PM
I tried to copy using the oRangeSelected but it does not work...am I doing it wrong?

Here's my codes



Private Sub PinPointPlace()
Dim firstrow As Long
Dim firstcol As Long

Dim oRangeSelected As Range
On Error Resume Next
Set oRangeSelected = Application.InputBox("Please select a range of cells!", _
"SelectARAnge Demo", Selection.Address, , , , , 8)
If oRangeSelected Is Nothing Then
MsgBox "You have cancelled the function"
Else

Workbooks("GCAD Mock Data").Sheets("NTB").Range("oRangeSelected").Copy Range("A1")


End If

End Sub

Paul_Hossler
01-03-2018, 08:10 AM
Hi Paul,

1. I have given a screenshot of what the input workbook and the master list looks like.

2. "Your approach is not very general purpose, and I think the overall structure needs improvement" Sorry I am new to VBA not sure what you mean by not general purpose. Can you provide insights as to how I can improve the structure?

3. Oh sorry I didn't know how to debug but I have just found out how to. The error is coming from this line: Workbooks.Open ("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Monthly Tracking Channel Fake.xlsb")
I have changed my percentagecollected to long as well.





1. There's no way to test a screen shot

2.

However the problem is that every week the excel column change and I have to only paste 3 specific columns. So how can I make this dynamic? The method I have decided to adopt is to use userform, where the user can specify what column range and row to copy their data from (as shown in the image).

2. By 'general purpose' I meant including in the macro some identifying information for the columns to be copied and logic as to where they are supposed to go so that you don't have to rely on a userform or a user


3. There could be any number of reasons why the line fails. From this piece of your code, it appears that the macro is in a third workbook, and opens two more. Correct?

[CODE]
Set monthlytransferdata = Workbooks.Open("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Transfer Monthly Data.xlsb")

Worksheets("Email_AR NTB (3)").Select
percentagecollected = Range("H:H")

Workbooks.Open ("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Monthly Tracking Channel Fake.xlsb")[
/CODE]

offthelip
01-05-2018, 03:49 PM
Sorry for the delay in replying I have been away.
this works, It copies the selected cells to A1 on "sheet2" Modify the name to suit your workbook


Dim firstrow As Long
Dim firstcol As Long

Dim oRangeSelected As Range
On Error Resume Next
Set oRangeSelected = Application.InputBox("Please select a range of cells!", _
"SelectARAnge Demo", Selection.Address, , , , , 8)
If oRangeSelected Is Nothing Then
MsgBox "You have cancelled the function"
Else

oRangeSelected.Copy Destination:=Worksheets("Sheet2").Range("A1")
' Workbooks("GCAD Mock Data").Sheets("NTB").Range("oRangeSelected").Copy Range("A1")


End If

edmundchant
01-29-2018, 10:03 AM
Hi Paul and offthelip,

Thank you so much for your help! Some of the codes provided were very useful in pointing me to the right path! And sorry for the delay in reply, I got caught up in other projects.

Anyway I have managed to solve the codes and will leave it here in case someone else might find it helpful in the future :)



Sub TransferData3()


Dim i As Long
Dim j As Long

Dim firstrow1 As Long
Dim firstrow2 As Integer
Dim lastrow1 As Long
Dim lastrow2 As Integer

Dim branchname As String


firstrow1 = Application.InputBox("Please enter the first row")
lastrow1 = Application.InputBox("Please enter the last row")
firstrow2 = Application.InputBox("Please enter the row to paste data")
column1 = Application.InputBox("Please enter the column to copy data from, column:")
column2 = Application.InputBox("to column:")
column3 = Application.InputBox("Where to paste data:")
column4 = Application.InputBox("Where to paste data.")

For i = firstrow1 To lastrow1

branchname = Sheets("NTB").Cells(i, "A").Value

Sheets("Paste").Activate

lastrow2 = firstrow2 + 49

For j = 2 To lastrow2

If Sheets("Paste").Cells(j, "A").Value = branchname Then

Sheets("NTB").Activate
Sheets("NTB").Range(Cells(i, column1), Cells(i, column2)).Copy

Sheets("Paste").Activate
Sheets("Paste").Range(Cells(j, column3), Cells(j, column4)).Select

ActiveSheet.PasteSpecial (xlPasteValuesAndNumberFormats)

End If

Next j

Application.CutCopyMode = False

Next i

Sheets("NTB").Activate
Sheets("NTB").Range("A1").Select


End Sub






It isn't perfect and I would suggest using a userform instead to make things neater but I changed my project direction and decided to use a pivot table instead so I didn't bother making one.

Cheers!