• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
You are here: Home
Lost password?
sp_Search
Advanced Search|Last Search Results
Advanced Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search

Please confirm you want to mark all posts read

Mark all topics read

sp_MobileMenu Actions
Actions
sp_LogInOut
Log In
sp_Search

Search Forums

sp_RankInfo
Ranks Information
Avatar

New/Updated Topics

General Excel Questions & Answers

  Sick leave periods in one cell

  removing pesky hidden xml sheets in a workbook

  Excel Is Running Slow

  Quartile reporting

  Data Validation with unique values

  Amortization

VBA & Macros

  error form doesnt send value to the main code

  VBA Update the body from a template email

  Send Email Code Error

Power Query

  How to add a new query for each cell of a column

  Source doesn't exist for Power Query folder

  Create list of working days

  Power Query - How to add multiple columns from parent to Nes…

Xtreme Pivot Tables

  Pivot Tables - Grand Totals AND Summary Percentages in the s…

Excel Expert

  new and deleted entries

Select Forum

  Rules and Guides

Forum Rules and Guides

  Public Forums - For Registered Users

General Excel Questions & Answers

Dashboards & Charts

VBA & Macros

Power Query

Power Pivot

  Course Members Only

Excel Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Power BI

Excel

Word

Outlook

Excel Expert

Excel for Customer Service Professionals

Excel Analysis Toolpak

Excel Tables

Excel for Operations Management

Financial Modelling

Advanced Excel Formulas

Pivot Tables Quick Start

ForumsVBA & Macros
sp_TopicIcon
code correction
Avatar
Sholtan Singh
Posts: 28
Level 0
May 7, 2020 - 6:26 am

1

Sub Step14() Dim w1 As Workbook, w2 As Workbook, w3 As Workbook Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls") Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\2.csv") Set w3 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\3.xlsx") Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet Set Ws1 = w1.Worksheets.Item(1) Set Ws2 = w2.Worksheets.Item(1) Set Ws3 = w3.Worksheets.Item(1) Dim Lc3 As Long, Lenf1 As Long, Lr1 As Long Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row Let Lc3 = Ws3.Cells.Item(1, Ws3.Columns.Count).End(xlToLeft).Column Dim Lc3Ltr As String Let Lc3Ltr = "K" Let Lenf1 = Lr1 - 1 Dim rngOut As Range: Set rngOut = Ws2.Range("A1:" & Lc3Ltr & Lenf1 & "") Ws2.Cells.NumberFormat = "General" Let rngOut.Value = "='[3.xlsx]" & Ws3.Name & "'!A$1" Let rngOut.Value = rngOut.Value Let rngOut.Value = Evaluate("If({1},SUBSTITUTE(" & rngOut.Address & ", ""0"", """"))") Dim rngIn As Range Set rngIn = Ws3.Range("A1:" & Lc3Ltr & "1") rngIn.Copy rngOut.PasteSpecial Paste:=xlPasteValues w1.Close w2.Save Let Application.DisplayAlerts = False w2.Close Let Application.DisplayAlerts = True w3.Close End Sub

 

 

this code gives incorrect output so i need help for the same (plz ssee the correct output pic and make this macro  to give correct output)

Plz have a look Sir and help me out in solving this problem Sircorrect-output.PNGImage Enlarger

incorrect-output.PNGImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage correct-output.PNG (6 KB)
  • sp_PlupImage incorrect-output.PNG (8 KB)
sp_AnswersTopicSeeAnswer
Avatar
Purfleet
England
Posts: 412

Level 4
May 7, 2020 - 2:18 pm

2

Hi Sholtan

Sorry but i have no idea what you want, can you exaplian what the problem is in detail and what is your expect outcome? Can you also attach an example work book rather than just paste a load of code into the message?

That way we do have to recrated anything.

Purfleet

Avatar
Sholtan Singh
Posts: 28
Level 0
May 7, 2020 - 4:15 pm

3

i have attached sample file 

plz see the file and run the macro, everything is perfect with this code but it paste the data incorrectly to 2.csv 

i have uploaded both the pics correct output and incorrect output

when i run the macro i should get the correct output  but i am not getting the same in 2.csvcorrect-output-2.PNGImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage incorrect-output-2.PNG (8 KB)
  • sp_PlupImage correct-output-2.PNG (6 KB)
Avatar
Purfleet
England
Posts: 412

Level 4
May 7, 2020 - 5:38 pm

4

it’s the macro that I need uploading as the code that was pasted in is just text and in that format it is very difficult to read.

If the issue is that the data is copied over to the wrong sheet

I am guessing that there is an issue with one of the variables that has been defined (and there are a lot of variables in this code)

I would suggest stepping throuigh the code with f8 so you can see what is happening at each step and what workbooks or worksheets are being selected when the data is written back.

Purfleet

Avatar
Sholtan Singh
Posts: 28
Level 0
May 7, 2020 - 6:36 pm

5

Sub Step14()
Dim w1 As Workbook, w2 As Workbook, w3 As Workbook
Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\2.csv")
Set w3 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\3.xlsx")
Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet
Set Ws1 = w1.Worksheets.Item(1)
Set Ws2 = w2.Worksheets.Item(1)
Set Ws3 = w3.Worksheets.Item(1)
Dim Lc3 As Long, Lenf1 As Long, Lr1 As Long
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lc3 = Ws3.Cells.Item(1, Ws3.Columns.Count).End(xlToLeft).Column
Dim Lc3Ltr As String
Let Lc3Ltr = "K"
Let Lenf1 = Lr1 - 1
Dim rngOut As Range: Set rngOut = Ws2.Range("A1:" & Lc3Ltr & Lenf1 & "")
Ws2.Cells.NumberFormat = "General"
Let rngOut.Value = "='[3.xlsx]" & Ws3.Name & "'!A$1"
Let rngOut.Value = rngOut.Value
Let rngOut.Value = Evaluate("If({1},SUBSTITUTE(" & rngOut.Address & ", ""0"", """"))")
Dim rngIn As Range
Set rngIn = Ws3.Range("A1:" & Lc3Ltr & "1")
rngIn.Copy
rngOut.PasteSpecial Paste:=xlPasteValues
w1.Close
w2.Save
Let Application.DisplayAlerts = False
w2.Close
Let Application.DisplayAlerts = True
w3.Close

End Sub

 

see now its readable Purfleet Sir 

Avatar
Sholtan Singh
Posts: 28
Level 0
May 7, 2020 - 6:37 pm

6

This code has pasting issue as i mentioned plz help me Purfleet Sir  in solving this problem

Avatar
Philip Treacy
Posts: 1514
Level 10
May 8, 2020 - 1:30 pm

7

Sholtan,

Please attach the workbook with the VBA in it.  Even if you paste code we have to copy it, create a workbook, paste it in, save it etc etc.  If there's then a problem is it because we did something wrong, or your code was pasted into the forum with errors? 

If we have your code in a workbook we know that it's the same code you are using.

If you want us to help please make it easy for us.

Regards

Phil

Avatar
Philip Treacy
Posts: 1514
Level 10
May 8, 2020 - 1:49 pm

8

Sholtan,

I can't figure out what files are for input,which ones are for output.

You've shown an image of 'correct output' but which file is this?  Where do you get the data for the input?

I've looked at the files you supplied but can't see how you've come up with the output as you've specified.

Phil

Avatar
Sholtan Singh
Posts: 28
Level 0
May 8, 2020 - 2:23 pm

9

vba will be placed in a seperate file macro.xlsm
i have three files 1.xls & 2.csv & 3.xlsx
1.xls first row has headers so dont count that
In 1.xls count the total number of rows that has data and copy the 3.xlsx sheet3 first row(first complete row copy) and paste that much time of 3.xlsx first row of sheet3 to 2.csv
suppose 1.xls has data in 5 rows then copy 3.xlsx first row of sheet3 and paste it to 2.csv 5 times
all files are located in a different path
sheet name can be anything
plz see the sample file

Avatar
Purfleet
England
Posts: 412

Level 4
May 11, 2020 - 6:03 am

10

If I understand this correctly you want to paste row 1 of sheet 3, in sheet 2 (the csv file) the number of rows equal to the rows in sheet 1?

Sorry, but I found your code really confusing - if the above summery is correct this should do the trick and is much easier to follow. You might have to change the file locations, I have them set as one on the Macro work sheet. And I didn’t know what you wanted doing with 2-1.csv once the data was copied over.

Sub Step1Ver2()

Dim LastRow As Integer
Dim Path As String

Path = ThisWorkbook.Sheets(1).Range("b1")

Workbooks.Open (Path & "\1-1.xls")

LastRow = Workbooks("1-1.xls").Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row

Workbooks("1-1.xls").Close False

Workbooks.Open (Path & "\2-1.csv")
Workbooks.Open (Path & "\3-1.xlsx")

Workbooks("3-1.xlsx").Sheets(1).Range("1:1").Copy Workbooks("2-1.csv").Sheets(1).Range("a1:a" & LastRow) - 1

'Workbooks("2-1.csc").Close False
Workbooks("3-1.xlsx").Close False

End Sub

sp_AnswersTopicAnswer
Avatar
Sholtan Singh
Posts: 28
Level 0
May 13, 2020 - 5:15 pm

11

Thnx Alot PurFleet Sir for the help 

Forum Timezone:
Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Lynnette Altomari, Karolis Kmitas, Roy Lutke, Jeff Krueger, Darrell Hodge, Natasha Smith
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)

Devices in use: Desktop (12), Phone (5)

Forum Stats:
Groups: 3
Forums: 24
Topics: 6214
Posts: 27243
Member Stats:
Guest Posters: 49
Members: 31894
Moderators: 3
Admins: 4
© Simple:Press

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.