hlep adjusting code transfer data from sheet to another sheet with formating

alhagag · Feb 10th 2020

hello

i found this code from some web it works but when i apply this code another workbook it doesn't transfer all data i changed what i need in my file i no know where is the problem

please anybody help

Code

Sub TransferData()
Dim TargetRow As Long
Dim lastRow As Long
Dim Rw As Long
Dim rSource As Range
lastRow = SHEET2.Cells(Rows.Count, "A").End(xlUp).Row
TargetRow = SHEET3.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row
For Rw = 1 To lastRow
Set rSource = SHEET2.Range(SHEET2.Cells(Rw, 1), SHEET2.Cells(Rw, 8))
If rSource.Cells(1) <> "" Then
rSource.Copy
SHEET3.Cells(TargetRow, "A").PasteSpecial Paste:=xlPasteValues
SHEET3.Cells(TargetRow, "A").PasteSpecial Paste:=xlPasteFormats
TargetRow = TargetRow + 1
End If
Next Rw
End Sub

Display More

**royUK** · Feb 10th 2020

Did you adjust sheet names and ranges?

Just checked your code is runing finne but it is only copying the header row - 14. Surely you should be copying the body of the form, if so that would not require a Loop.

Let me know if that is correct and I'll amend your code.

alhagag · Feb 10th 2020

yes i did

alhagag · Feb 10th 2020

Quote from royUK

Did you adjust sheet names and ranges?

you can check my file

**royUK** · Feb 10th 2020

I've added a comment to my first post above

**royUK** · Feb 10th 2020

I've just looked at amending your code. You have MergedCells which will , as always, make this harder.

Explain fully what you are trying to do and I'll have another look

alhagag · Feb 10th 2020

Quote from royUK

I've just looked at amending your code. You have MergedCells which will , as always, make this harder.

Explain fully what you are trying to do and I'll have another look

i know about mergedcell in the beginning i tough this problem but the original file contains also merged cell with this the code works i attache the orginal file you can run macro and see the result in sheet2 that what i would

alhagag · Feb 12th 2020

i need help please

**Carim** · Feb 12th 2020

Hello,

With your extremely special set-up ... and merged cells which serve no purpose ...

you should use the best possible shortcut .... the sign equal ...

**royUK** · Feb 12th 2020

I asked to know what you are wanting to do. The results in your last example serve no useful purpose. When I have created Invoice systems the relevant line data is saved to one sheet as a Database and the the Total and tax to another sheet. That way the data can be used.

alhagag · Feb 12th 2020

Quote from Carim

Hello,

With your extremely special set-up ... and merged cells which serve no purpose ...

you should use the best possible shortcut .... the sign equal ...

you can see my file and the original file "transfer" i change the name sheet and range the file orginal contains merged cell and the code works but my file doesn't work berifly i would transfer data from a3: i23 to sheet3 like archive in every time fill process in sheet2

i hope this help

**royUK** · Feb 13th 2020

It doesn't seem like you want help to create a better solution. I keep asking why you want to copy data like that and yes I have tried youe example.

alhagag · Feb 14th 2020

Of course, I want a solution and I do not intend to waste the time of the members. What I want is when I do every time an operation that is stored in the archives of worksheet No. 3 because later I will need it when I search for it through the design of User Form it searches for it and invokes the data through the userform

**royUK** · Feb 14th 2020

Quote

I will need it when I search for it through the design of User Form it searches for it and invokes the data through the userform

That's my point. Your current method is not storing the data in the correct format so how will you search for particular data?

alhagag · Feb 14th 2020

Quote from royUK

That's my point. Your current method is not storing the data in the correct format so how will you search for particular data?

by Name of custodian as saving in the sheet every name contains all the data

**royUK** · Feb 15th 2020

Can't help you because you do not seem to understand what I am saying that there is a correct way of saving data and you are not doing that!

MandyRead · Feb 19th 2020

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

Share